Google Sheets Date Difference: Formulas and Examples

To calculate the number of days between two dates in Google Sheets, subtract the earlier date from the later one: with a start date in A2 and an end date in B2, enter =B2-A2. For full months or years, use DATEDIF; for working days, use NETWORKDAYS. The right choice depends on whether you mean elapsed days, complete calendar units, or business days.

How dates work in Google Sheets

Google Sheets stores dates as numeric values, so subtracting one valid date from another produces the number of days between them. The displayed date is a readable representation of that underlying value. This is why a basic subtraction works without a special date-difference operator.

For example, suppose A2 contains January 15, 2024, and B2 contains March 10, 2024. With those dates in A2 and B2, enter =B2-A2; the result is 55 days. The count measures the elapsed time from the start of January 15 to the start of March 10. It does not count both endpoint dates as separate full days, a distinction that matters for attendance, bookings, and service periods.

The cells must contain recognized date values, not text that merely looks like a date. Dates typed into a sheet are usually recognized automatically, but imported data may be stored as text or interpreted according to a different locale. If a subtraction returns an error or an unexpected value, check that both cells are actual dates and that the sheet’s locale matches the way dates are written.

Choose a formula for the kind of difference you need

Start by clarifying what “date difference” means in your task. A calendar-day gap, a count of complete months, and a count of weekdays are different measurements. These are the most useful options:

Elapsed calendar days: subtract the start date from the end date. This is generally the simplest option.
Days with explicit end and start arguments: use DAYS, which makes the argument order clear in a longer formula.
Completed years or months: use DATEDIF with an appropriate unit.
Working days: use NETWORKDAYS, optionally supplying a holiday range.
Elapsed time including hours: subtract date-time values and format the result as a duration, or convert the day fraction to hours or minutes.

In each case, make sure the order of the dates matches your intended result. Putting the later date first in a subtraction produces a negative number, which can be useful for showing overdue time but may be a mistake in a report.

Calculate elapsed days with subtraction or DAYS

For dates in A2 (start) and B2 (end), use =B2-A2. If A2 is January 15, 2024, and B2 is March 10, 2024, the result is 55. Format the result as a number if Sheets displays it as a date: select the result cell and choose Format > Number > Number.

The DAYS function expresses the same calculation by naming the end date first and the start date second. With the start date in A2 and the end date in B2, enter =DAYS(B2,A2); for January 15 through March 10, 2024, it returns 55. This can be easier to read in a formula that refers to several date cells, though subtraction is usually quicker for a single pair.

To count both dates as included days, add one to the elapsed-day result. With the same dates in A2 and B2, enter =B2-A2+1; the result is 56 inclusive calendar dates. Inclusive counting is common for a stay that includes both check-in and check-out dates or a campaign running on both its start and end dates. Use it only when the definition of the task calls for counting both endpoints.

Find complete months and years with DATEDIF

DATEDIF returns the number of complete units between two dates. Its arguments are a start date, an end date, and a quoted unit code. For start date A2 and end date B2, enter =DATEDIF(A2,B2,"D") to return the days between them. The same sample dates, January 15 and March 10, 2024, produce 55.

For the number of fully completed months, use unit M. With January 15, 2024, in A2 and March 10, 2024, in B2, =DATEDIF(A2,B2,"M") returns 1, because one full month has passed but two have not. For fully completed years, use unit Y. With dates in the same cells, =DATEDIF(A2,B2,"Y") returns 0 for this example.

The YM unit reports remaining months after complete years have been removed. For a birth date in A2 and a later date in B2, =DATEDIF(A2,B2,"Y") gives whole years, while =DATEDIF(A2,B2,"YM") gives additional whole months. These are useful building blocks for an age or tenure display, although they do not by themselves provide a precise breakdown including remaining days.

A frequent misunderstanding is to treat a complete-month count as the number of month boundaries crossed. January 31 to February 1 crosses into a new calendar month, but less than one complete month has elapsed. If the goal is to count calendar-month boundaries instead, and the earlier date is in A2 and the later date in B2, use =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2). For January 31 and February 1, that calculation returns 1. Choose this only when boundary counting, rather than elapsed full months, is what you need.

Count weekdays and account for holidays

NETWORKDAYS counts working days between two dates, including the start or end date when it falls on a workday. It excludes Saturdays and Sundays by default. With a Monday date in A2 and the following Friday in B2, enter =NETWORKDAYS(A2,B2); the result is 5. This is often a better measure for work schedules, turnaround estimates, or business processes than simple subtraction.

To exclude holidays too, list holiday dates in D2:D10. With the work-period start and end in A2 and B2 and the holiday list in D2:D10, use =NETWORKDAYS(A2,B2,$D$2:$D$10). If one of the weekdays in the Monday-to-Friday example is listed as a holiday, the result is 4. The dollar signs keep the holiday range fixed if you copy the formula down a column. Ensure the holiday cells are actual dates, not text labels.

For a workweek that does not run Saturday through Sunday, use NETWORKDAYS.INTL. Its optional weekend argument can specify a weekend pattern. With dates in A2 and B2 and a Monday-through-Friday workweek, enter =NETWORKDAYS.INTL(A2,B2,"0000011"); the seven-character pattern marks Monday through Sunday in order, with 1 indicating a nonworking day, so Saturday and Sunday are excluded. The function also accepts a holiday range as another argument if your schedule needs it. Check the pattern carefully, especially for a six-day workweek or a weekend that falls on different days.

Work with times as well as dates

A date-time value includes a fractional part for the time of day. Subtracting two date-time values therefore returns a number of days that may include a fraction. If A2 contains 9:30 a.m. and B2 contains 5:00 p.m. on the same date, with both cells holding date-time values, =B2-A2 returns 0.3125 days. Select the result and choose Format > Number > Duration to display the elapsed time as 7:30.

If you need a numeric number of hours instead, with the start date-time in A2 and the end date-time in B2, use =(B2-A2)*24. For the example from 9:30 a.m. to 5:00 p.m., it returns 7.5 hours. To convert the difference to minutes, use =(B2-A2)*1440, because a day has 1,440 minutes. These calculations are helpful for shift lengths and response times, but crossing midnight is handled correctly only when the cells include the actual dates as well as the times.

If the end time is entered without a date and is earlier on the clock than the start time, a simple subtraction becomes negative. Rather than automatically applying an absolute value, first decide whether the end time represents the next day or an invalid entry. An absolute value hides the direction of the difference and can make erroneous records look plausible.

Handle blanks, reversed dates, and display problems

A formula copied down a table may show a number for rows where one date has not been entered. To leave the result blank until both dates are present, with the start date in A2 and end date in B2, enter =IF(OR(A2="",B2=""),"",B2-A2). This avoids clutter from incomplete records. It does not validate the dates, so a reversed pair can still produce a negative result.

When a difference looks wrong, check the underlying values and the result format before changing the arithmetic. A result displayed as a date may simply be a numeric day count using a date format. A value that appears several years off may come from text being parsed in the wrong order, such as day-month versus month-day. Select the cells and inspect their contents, then verify the spreadsheet locale through File > Settings if the source data uses a different date convention.

Negative result: confirm which date should be the start and which should be the end. Subtraction preserves direction.
Unexpected fractional result: one or both cells may include a time component. Format them as date-time values to inspect it.
Error from DATEDIF: check that the start date is not later than the end date and that the unit code is valid.
Dates treated as text: convert or re-enter them as recognized dates, using a consistent format and locale.
Missing business days: review the holiday range and confirm that the intended weekend pattern is being used.

Google Sheets and Excel differences

The formulas in this guide use Google Sheets in a web browser. The basic date arithmetic, DAYS, DATEDIF, and workday functions are also available in Excel, but the menus and interface differ. In Sheets, use Format > Number to change the result display; do not assume that a date-looking result means the calculation itself is wrong.

Sheets may use semicolons instead of commas between formula arguments in some spreadsheet locales. The examples here use comma separators, as commonly seen in English-language settings. If Sheets flags a formula even though the function and cell references are correct, check the locale and the separator convention used by that file. Date parsing can also vary by locale, which is why unambiguous date values and consistent source data matter.

Frequently Asked Questions

What is the simplest Google Sheets formula to subtract two dates? Put the start date in A2 and the end date in B2, then subtract the first from the second using =B2-A2. The result is elapsed days; add one only if your task counts both endpoint dates inclusively.

How do I calculate the number of months between two dates? Use DATEDIF with the M unit to count complete months. It will not count a partially elapsed month as a whole one, so it can differ from a count of calendar-month boundaries.

How do I calculate working days and exclude holidays? Use NETWORKDAYS with the start and end dates, followed by a range containing holiday dates. Saturdays and Sundays are excluded by default; use NETWORKDAYS.INTL when the weekend schedule is different.

Why does my date subtraction show a date instead of a number? The result cell may be formatted as a date. Select it and choose Format > Number > Number for a day count, or choose Duration when the result is an elapsed date-time difference.

Can Google Sheets calculate hours between two dates and times? Yes. Subtract the start date-time from the end date-time and format the result as Duration, or multiply the difference by 24 to get numeric hours. Include the date as well as the time when the interval may cross midnight.

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.