Google Sheets Date Math: Formulas, Examples, and Tips

$Google Sheets Date Math: Formulas, Examples, and Tips$

Google Sheets date math works by treating dates as serial numbers, so you can subtract one date from another or add a number of days. For example, if a start date is in A2 and an end date is in B2, enter =B2-A2 to return the number of days between them; use =A2+7 to find the date one week after A2.

How Google Sheets represents dates

A date that looks like March 10, 2025, in a cell is stored as a numeric date value, not as a month name and day assembled for display. The whole-number portion represents the day, while a decimal portion can represent a time. That is why ordinary addition and subtraction can work on dates, and why formatting changes how a result looks without changing the underlying calculation.

For example, subtracting March 1 from March 10 returns 9, the number of elapsed days. It does not return 10 just because both calendar dates are included when you count them inclusively. If a task needs to count both the starting and ending dates, add 1 to the difference. The correct approach depends on whether the question is “How many days have elapsed?” or “How many calendar dates are in this span?”

Sheets can also store a date and time together. A difference between two timestamps may therefore be a decimal number of days: 0.5 represents 12 hours. If the result is meant to show whole days, use an appropriate number format or a function such as INT when discarding the fractional part is appropriate.

Adding and subtracting days

For straightforward calendar-day calculations, use addition or subtraction. Suppose A2 contains March 10, 2025. With the date in A2, =A2+14 returns March 24, 2025. With the same date in A2, =A2-30 returns February 8, 2025. These operations count weekends and holidays because they are ordinary calendar days.

For two dates, subtraction is often the clearest way to calculate elapsed days. If A2 contains March 1, 2025, and B2 contains March 10, 2025, then =B2-A2 returns 9. The equivalent function form is =DAYS(B2,A2), which takes the end date first and the start date second. The order matters: reversing the dates produces a negative result.

Use DATE when you need to build a date from year, month, and day values rather than type a date string. With year in A2, month in B2, and day in C2, =DATE(A2,B2,C2) returns the corresponding date. DATE also normalizes out-of-range month or day values, which can be useful in calculations but may surprise someone expecting an invalid date to produce an error.

Working with months and month ends

Adding a fixed number of days is not the same as adding a calendar month. Months have different lengths, so adding 30 days does not reliably produce the same day in the following month. For month-based schedules, use EDATE. With a start date in A2, =EDATE(A2,1) returns the date one month later. If A2 is January 31, the result is the last valid day in February, rather than a date in March.

Use EOMONTH when the target is a month’s final date. With a date in A2, =EOMONTH(A2,0) returns the last day of the month containing A2. With A2 as March 10, 2025, it returns March 31, 2025; =EOMONTH(A2,1) returns April 30, 2025. A negative second argument moves backward by months.

These functions are useful for recurring monthly reviews, billing-cycle dates, and month-end reports. Decide how your schedule should handle dates such as the 29th, 30th, or 31st in shorter months. EDATE and EOMONTH provide defined calendar results, but they cannot determine the business rule you intend—such as whether a deadline should move to the last day of the month or remain on a particular weekday.

Counting working days and excluding holidays

Calendar-day calculations include Saturdays, Sundays, and holidays. To count standard Monday-to-Friday working days, use NETWORKDAYS. If A2 is March 3, 2025, and B2 is March 7, 2025, =NETWORKDAYS(A2,B2) returns 5. The start and end dates are included when they are working days.

You can provide a holiday range as an optional third argument. If A2 is March 3, B2 is March 7, and D2:D2 contains the holiday March 5, =NETWORKDAYS(A2,B2,$D$2:$D$2) returns 4. In a real sheet, list holidays in a range such as D2:D20 and use that range in the formula. Keep the dollar signs if you plan to fill the formula down and want the holiday list to stay fixed.

To find a date a given number of working days from a start date, use WORKDAY. With the start date in A2 and the number of working days in B2, =WORKDAY(A2,B2) returns the resulting weekday date, skipping Saturday and Sunday. Add a holiday range as a third argument to skip those dates too. If your organization uses a different weekend pattern, Sheets also provides WORKDAY.INTL and NETWORKDAYS.INTL for specifying weekend days.

Measuring years, months, and age

For completed years or months between dates, Sheets provides DATEDIF. With a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"Y") returns the number of complete years between them. Change the unit to "M" for complete months or "D" for days. The start date must not be later than the end date, or the function returns an error.

DATEDIF is useful for questions such as someone’s age in completed years, but it does not return a decimal or a rounded estimate. For example, a person who has not yet reached their birthday in the current year has not completed another year. Be careful with the unit "MD", which reports a day difference after ignoring months and years; it can be confusing for some date ranges. If you need a transparent calculation, subtract dates for days or combine clearly defined year and month calculations rather than assuming every month has a fixed number of days.

For a live date, =TODAY() returns the current date and updates when the spreadsheet recalculates. =NOW() returns the current date and time. These are volatile functions, so their displayed values can change as the sheet recalculates; they are not a way to permanently stamp the date a record was entered. For a fixed record date, enter a date value directly instead of relying on TODAY or NOW.

Formatting dates and setting the locale

If a formula returns a number such as 9 when you expected a date, the arithmetic may be correct but the result cell may be formatted as a number. Select the cells and choose Format > Number > Date to display date values as dates. Choose Format > Number > Custom date and time to select or create a display format. Formatting does not change the value used in calculations.

If a typed date is interpreted incorrectly or remains text, check the spreadsheet’s locale. In Google Sheets in a web browser, use File > Settings and review the Locale setting. Locale affects how dates are interpreted and displayed, as well as number conventions. For example, a numeric date written with slashes can be ambiguous across regions. A date such as 2025-03-10 is less ambiguous to a reader, but the spreadsheet still needs to recognize the entry as a date value.

When a date is stored as text, date subtraction may fail or behave unexpectedly. You can convert recognizable date text with DATEVALUE. With a date-like text string in A2, =DATEVALUE(A2) attempts to return its date value. Whether an ambiguous string can be interpreted depends on the spreadsheet’s locale and the text format, so check the converted result before using it in a larger calculation.

Common date-math problems

Inclusive versus elapsed counts: Subtracting dates gives elapsed days. Add 1 only when both endpoints should count as dates in the total.
Unexpected decimals: One or both cells may include times. A time is a fraction of a day, so the subtraction can include a fractional result.
Wrong-looking result: The result cell may have a date format when you want a number, or a number format when you want a date. Change the format rather than changing sound arithmetic.
Dates treated as text: Check the locale, the original entry, and whether conversion is needed. Text that resembles a date is not necessarily a usable date value.
Negative day counts: The end date is earlier than the start date, or the arguments to DAYS are reversed. Confirm which date should come first.
Month-end surprises: A month is not a fixed number of days. Use EDATE for a month offset and EOMONTH for a month’s final day, then confirm the result matches the schedule’s rules.

Google Sheets uses its own date handling and displays dates according to spreadsheet settings. Excel also supports familiar date arithmetic and many similarly named functions, but menu locations, locale behavior, and some function details can differ. If a workbook moves between Sheets and Excel, verify important formulas and date displays in the destination application rather than assuming every setting will transfer identically.

Frequently Asked Questions

How do I calculate the number of days between two dates in Google Sheets?
Put the earlier date in A2 and the later date in B2, then enter =B2-A2. The result is elapsed days. Add 1 if your counting rule includes both the first and last calendar dates.

How do I add 30 days to a date?
If the date is in A2, enter =A2+30. This adds 30 calendar days, including weekends and holidays. It does not mean “one month”; use EDATE for a calendar-month offset.

How do I add one month to a date?
With the starting date in A2, use =EDATE(A2,1). For a result at the end of a month, use =EOMONTH(A2,1) to return the final day of the following month.

Why does subtracting dates show a decimal?
At least one cell likely includes a time. Sheets represents the time as part of a day, so the difference can include a fraction. If you only need the date portion, remove or account for the times in the source data rather than rounding without checking the intended meaning.

Can Google Sheets count weekdays but skip holidays?
Yes. Use =NETWORKDAYS(start_date,end_date,holiday_range), replacing the placeholders with cell references or ranges. The result counts standard weekdays in the inclusive date span and excludes dates listed in the holiday range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.