Google Sheets•8 min read
Google Sheets Date Functions: DATEDIF, EDATE & EOMONTH
Use Google Sheets date functions for age, deadlines, month ends, and business-day calculations across locales.
SheetHub••8 min
Google Sheets stores dates as numbers, and
Google Sheets represents a date with a serial number and applies a date format to display it. In Sheets, serial 0 corresponds to December 30, 1899. Excel's commonly used Windows date system starts at January 1, 1900, so imported serial values can differ by a day even when the displayed dates look similar.
Google Sheets makes these functions available to all account types. A personal account, a Workspace account, and a browser session can use the same date formulas. Menu labels and locale settings may differ, but the functions themselves are broadly available.
A real date can be added, compared, and passed to another function. For example, if A2 contains a date, this formula returns the date 30 days later:
Formatting changes the appearance, not the underlying value. A date can display as
Locale is especially important for an ambiguous value such as
The unit controls the result:
To calculate a person's age from a birth date in B2, use:
For a years, months, and days display, calculate the remainder units separately:
Use
The first example returns September 10, 2026; the second moves three months into the past. These formulas suit renewals, review windows, and rolling reports.
For a contract in A2 that ends at the end of its starting month, use
For a weekend other than Saturday and Sunday, use the
The holiday argument is optional, but omitting it means a national holiday counts as a working day. Keep holidays in a range rather than hardcoding them into every formula.
The last formula returns the number of days until the date in C2. To calculate the last working day of the current month, combine
For a column of dates, use a consistent formula pattern and keep the source dates in one range. For market-related date ranges, Google Sheets date functions work with
The function names are largely portable. The biggest migration risk is copying raw serial values or relying on an ambiguous text date. If a workbook is moving from Excel to Sheets, the Excel side of this date-functions pair explains the matching functions and their version details.
Before asking an AI tool for a date formula, validate one known start date and expected month end in the sheet. Include the date column, holiday range, and desired output so locale and argument-order errors are easier to catch.
Use
Are Google Sheets date functions the same as Excel date functions?
Most core functions use the same names and arguments. Differences appear in serial-date conventions, locale parsing, and how each application documents or formats dates.
How can a text value become a date?
Use
Google Sheets date functions cover the full workflow:
DATEDIF works almost exactly like Excel's, quirks included. Google Sheets date functions make age calculations, month-end deadlines, and business-day schedules portable. Sheets users also need to account for locale parsing and a serial-date base that differs from Excel's.
Same functions, slightly different numbers
How Google Sheets stores dates
=A2+3001/15/2026, 15/01/2026, or 2026-01-15 while still representing the same point in time.
Text is different. A value such as 01/15/2026 pasted from another system may remain text, depending on the spreadsheet locale. Convert recognizable text with DATEVALUE:
=DATEVALUE("2026-01-15")01/02/2026. One locale can read it as January 2, while another reads it as February 1. For files shared across regions, use an unambiguous ISO-style input such as 2026-01-15, then apply a display format such as yyyy-mm-dd.
DATEDIF: Calculate age and date differences
DATEDIF returns the complete difference between two dates. Its syntax is:
=DATEDIF(start_date, end_date, unit)| Unit | Result |
|---|---|
"Y" | Complete years |
"M" | Complete months |
"D" | Total days |
"YM" | Remaining months after complete years |
"YD" | Remaining days after complete years |
"MD" | Remaining days after complete months |
=DATEDIF(B2, TODAY(), "Y")=DATEDIF(B2, TODAY(), "Y")
=DATEDIF(B2, TODAY(), "YM")
=DATEDIF(B2, TODAY(), "MD")DATEDIF may not appear in autocomplete, but it still works when typed manually. The start date must not be later than the end date. If it is later, Sheets returns #NUM!. Add a guard to show a clearer message:
=IF(B2>C2, "Check dates", DATEDIF(B2, C2, "Y"))EDATE and EOMONTH: month-based calculations
EDATE when a deadline or renewal should move by whole months. The syntax is =EDATE(start, months). A positive number moves forward; a negative number moves backward.
=EDATE(DATE(2026,3,10), 6)
=EDATE(TODAY(), -3)EOMONTH returns the final day of a month. Its second argument is a month offset, where 0 means the month containing the starting date:
=EOMONTH(TODAY(), 0)
=EOMONTH(TODAY(), 1)
=EOMONTH(TODAY(), -1)=EOMONTH(A2, 0). The function handles February and leap years without a special case.
NETWORKDAYS, WORKDAY, and INTL Variants
NETWORKDAYS counts working days between two dates, excluding Saturday and Sunday by default. Add a holiday range so public holidays are excluded too:
=NETWORKDAYS(TODAY(), C2, Holidays)WORKDAY moves forward or backward by a number of working days and returns a date:
=WORKDAY(TODAY(), 10, Holidays)INTL variants. The weekend argument can be a weekend code or a seven-character string of zeroes and ones. For example, this treats Friday and Saturday as the weekend:
=NETWORKDAYS.INTL(A2, B2, "0000110", Holidays)
=WORKDAY.INTL(A2, 10, "0000110", Holidays)TODAY, NOW, and dynamic dates
TODAY() returns the current date. NOW() returns the current date and time. Both are dynamic and recalculate as the spreadsheet updates, so they suit live age, deadline, and status calculations.
=TODAY()
=NOW()
=C2-TODAY()EOMONTH and WORKDAY:
=WORKDAY(EOMONTH(TODAY(), 0)+1, -1, Holidays)GOOGLEFINANCE date arguments when you build period-based analysis.
Sheets vs. Excel: What Actually Differs?
| Aspect | Excel | Google Sheets |
|---|---|---|
| Common serial base | January 1, 1900 | December 30, 1899 is serial 0 |
DATEDIF | Works, but is not prominently listed | Works and is documented in Sheets help |
NETWORKDAYS.INTL | Available | Available |
| Locale source | Operating-system and workbook settings | Spreadsheet and account locale settings |
| Text-date parsing | Can vary by regional settings | Can vary by spreadsheet locale |
Practical date formulas
| Task | Formula |
|---|---|
| Age in complete years | =DATEDIF(B2,TODAY(),"Y") |
| End of the current month | =EOMONTH(TODAY(),0) |
| Business days remaining | =NETWORKDAYS(TODAY(),C2,Holidays) |
| Deadline after 10 workdays | =WORKDAY(TODAY(),10,Holidays) |
| Six months after a start date | =EDATE(A2,6) |
Errors and limitations
- A text date can return an error or the wrong date. Convert it with
DATEVALUE, or use an unambiguous ISO-style input. Check the spreadsheet locale when parsing imported data. - If
DATEDIFreturns#NUM!, the start date is later than the end date. Reverse the dates or use anIFguard. - A business-day deadline can be too late or too early when the formula skips weekends but not public holidays. Supply a holiday range to
NETWORKDAYSorWORKDAY. - Imported serial values can be off by one day because Excel and Sheets use different serial-date conventions. Convert through displayed dates instead of copying raw serial numbers.
- A dynamic result can change unexpectedly because
TODAY()andNOW()recalculate. Use a fixed date cell when the calculation must remain stable.
Pro tips and common mistakes
yyyy-mm-dd for shared logs and exports. It is unambiguous across locales. Keep holidays in a named range such as Holidays, and use ARRAYFORMULA for whole-column calculations.
Do not add numbers to values that only look like dates. Test the cell first, use the correct DATEDIF unit, and remember that NETWORKDAYS excludes weekends and supplied holidays rather than counting calendar days.
FAQ
DATEVALUE when the text follows the spreadsheet's locale rules. For shared files, prefer ISO-style text such as 2026-01-15 and verify the resulting date.
Does Google Sheets have WORKDAY.INTL?
Yes. WORKDAY.INTL and NETWORKDAYS.INTL let you define a nonstandard weekend and still pass a holiday range.
Why does my date display differently after import?
The stored date and its display format are separate. Apply a consistent format such as yyyy-mm-dd, then check the spreadsheet locale if the underlying date was parsed from text.
Summary
DATEDIF measures elapsed time, EDATE moves by months, EOMONTH finds month boundaries, and NETWORKDAYS or WORKDAY handles business schedules. Use DATEVALUE carefully, keep locale differences in mind, and supply holidays whenever a deadline must match a real work calendar.Article Topics
Recommended Next Reading
Google Sheets
=GOOGLE(...)Google Sheets UNIQUE Function: Extract Distinct Values
Explore ↗
Google Sheets
=GOOGLE(...)Google Sheets COUNTUNIQUEIFS: Count Unique by Criteria
Explore ↗
Google Sheets
=GOOGLE(...)Google Sheets INDIRECT: Build Dynamic Sheet References
Explore ↗
Share this tutorial
Discussion & Community
Share questions, tips, or edge-cases about this spreadsheet formula.