Google Sheets Date Formulas
Use TODAY, DATE, EDATE, EOMONTH, DATEDIF, and NETWORKDAYS in Google Sheets for deadlines, aging, month ends, and workdays.
Google Sheets date formulas help turn static dates into working timelines, schedules, payment plans, and review cycles. Use them when due dates, month ends, workday counts, or rolling deadlines should update without manual edits.
The key is to store dates as real date values, not text. Once the sheet has real dates, formulas can calculate deadlines and intervals reliably.
TODAY for rolling dates
TODAY returns the current date:
=TODAY()Use it in dashboards, overdue checks, and timeline formulas. For example, to mark whether a due date in E2 is overdue:
=E2<TODAY()That returns TRUE when the date has passed.
To build a date from separate numeric year, month, and day fields, use DATE:
=DATE(A2, B2, C2)Google notes that out-of-range months and days roll into neighboring periods, so validate user-entered components when silent rollover would hide a bad input.
EDATE for monthly offsets
EDATE returns a date a set number of months before or after another date:
=EDATE(start_date, months)If A2 is a contract start date, the date three months later is:
=EDATE(A2, 3)This is useful for subscription reviews, grant milestones, quarterly check-ins, and recurring planning cycles.
EOMONTH for month-end dates
EOMONTH returns the last day of a month:
=EOMONTH(start_date, months)To get the end of the current month:
=EOMONTH(TODAY(), 0)To get the end of next month:
=EOMONTH(TODAY(), 1)This is useful in small-business budgets and invoice workflows where monthly close dates matter.
WORKDAY for business-day deadlines
WORKDAY returns a date a number of working days from a start date:
=WORKDAY(start_date, num_days, [holidays])If A2 is the kickoff date and the task takes 10 working days, use:
=WORKDAY(A2, 10)If holidays are listed in H2:H10, use:
=WORKDAY(A2, 10, H2:H10)This is better than adding calendar days when weekends or holidays should not count.
NETWORKDAYS for working-day counts
NETWORKDAYS counts working days between two dates:
=NETWORKDAYS(start_date, end_date, [holidays])To count workdays between a start date in A2 and end date in B2, use:
=NETWORKDAYS(A2, B2)In a project tracker or Gantt chart, this can show realistic working duration instead of raw calendar span.
Calculate elapsed days, months, or years
For total calendar days, direct subtraction is simplest:
=B2-A2For completed units, Google Sheets supports DATEDIF:
=DATEDIF(A2, B2, "Y")Common units include "Y" for whole years, "M" for whole months, "D" for days, "YM" for remaining months after whole years, and "YD" for days while ignoring years.
WATCH OUT
DATEDIF(...,"MD") for critical calculations. Google Sheets accepts the unit, but Microsoft documents that this legacy mode can return a negative, zero, or inaccurate result for some date combinations. For elapsed days, subtract the dates or use "D".Common date formula issues
| Issue | Cause | Fix |
|---|---|---|
| Formula returns a number | Cell is formatted as Number | Format the result as Date |
| Comparison fails | Date is stored as text | Convert imported text to real dates |
| Deadline ignores holidays | Holidays range omitted | Add the holiday list as the third argument |
| Due dates shift unexpectedly | TODAY recalculates | Use a fixed date when the review date should not move |
#NUM! from DATEDIF | Start date is later than end date | Correct the order or handle the negative case intentionally |
| Workday count is off | Holidays are missing or stored as text | Use a clean range of real date values |
Do this with AI
Date logic gets repetitive when a sheet needs kickoff dates, due dates, month-end close, overdue flags, and business-day durations at the same time.
"Add working-day deadlines, month-end review dates, and overdue flags to this Google Sheets project plan"
Griddy can add the formulas against the actual date columns and keep the resulting timeline readable.
Related guide: FILTER in Google Sheets for a live overdue-items view.
Sources
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.
Use this on real templates
Turn dates into working timelines
Date formulas make project plans, invoices, budgets, and schedules easier to review because deadlines and workday counts update automatically.
Project Tracker
Track tasks, owners, priorities, due dates, and blockers in one delivery board. Group work by stream, review progress, and keep next steps visible.
Open templateProject ManagementGantt Chart
Use this free Gantt chart template to plan project phases, owners, milestones, dependencies, and weekly timelines in Excel, Google Sheets, or Griddy.
Open templateFinanceSmall Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Open templateFinanceInvoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.
Open template