Google Sheets Date Difference: Formulas and Examples

In Google Sheets, subtract the earlier date from the later date to get the difference in days: if the start date is in A2 and the end date is in B2, enter =B2-A2. For complete months or years, use DATEDIF; for weekdays, use NETWORKDAYS.

Subtract dates to get the number of days

Dates in Sheets are stored as numbers, so subtracting one date from another returns the number of calendar days between them. With a start date in A2 and an end date in B2, =B2-A2 returns 9 when A2 is June 1, 2025, and B2 is June 10, 2025. The result excludes the start date itself: it counts the elapsed days from the start of June 1 to the start of June 10.

If you want to count both the first and last dates as days in a span, add one. With those same dates in A2 and B2, =B2-A2+1 returns 10. This inclusive count can be useful for counting calendar dates covered by an event or booking, but it is not the same as elapsed time. Choose the approach that matches what “how many days?” means in your situation.

To apply the calculation down a column, enter the formula in the first result row and use the small blue fill handle to copy it down. Each copied formula adjusts its row references automatically. If the end date can be earlier than the start date, decide how you want to handle that: subtraction will return a negative number rather than silently changing the order.

How Sheets stores dates

A date that Sheets recognizes is stored as a serial number, with the displayed date acting as a readable format. This is why date subtraction works and why a result may initially appear as another date rather than a count. The underlying result is still a number of days; the cell’s number format controls how it looks.

If the result appears as a calendar date, select the result cell and choose Format > Number > Number. You can also use the toolbar’s number-format controls. For whole days, set zero decimal places if needed. Avoid converting the dates to text just to make them look consistent: text dates may not subtract correctly, particularly when they were imported or entered in an ambiguous format.

For reliable inputs, use the date picker or enter dates in a format Sheets recognizes under the spreadsheet’s locale. A string such as 03/04/2025 can mean March 4 or April 3 depending on locale. Check File > Settings to review the spreadsheet’s locale, and use an unambiguous date entry when sharing data across regions. A date that is left-aligned or behaves like ordinary text may not be a real date value.

Use DATEDIF for completed months and years

Subtraction gives days, not calendar months or years. A month does not have a fixed number of days, so dividing a day result by 30 is only an approximation. For completed calendar units between two dates, Sheets supports DATEDIF. Its syntax is DATEDIF(start_date,end_date,unit), where the unit is supplied in quotation marks.

With the earlier date in A2 and the later date in B2, =DATEDIF(A2,B2,"M") returns the number of complete months between them. For example, January 15 to March 14 is one completed month, while January 15 to March 15 is two. The function measures full units, not the number of month names touched by the date range.

With the same cell layout, =DATEDIF(A2,B2,"Y") returns complete years. The unit “D” returns days, while “YM” returns remaining months after complete years are removed, and “YD” returns remaining days after complete years are removed. These partial-unit options can help build an age or tenure display, but they do not necessarily express a duration in the everyday sense. In particular, a result in “YM” is not total months.

DATEDIF expects the start date to be earlier than or equal to the end date. If the dates are reversed, it can return an error. Check the date order before relying on the result, or use ordinary subtraction if a signed difference is meaningful. The function is useful for completed calendar units; it is not a general replacement for subtracting dates.

Count weekdays and exclude holidays

For working days rather than calendar days, use NETWORKDAYS. It counts weekdays between the start and end dates, including both endpoints when they are weekdays. With a start date in A2 and an end date in B2, =NETWORKDAYS(A2,B2) counts Monday through Friday and excludes Saturday and Sunday. A Monday-to-Friday span therefore returns 5, while a Monday-to-next-Monday span returns 6.

To exclude holidays as well, put holiday dates in D2:D10. With the start and end dates still in A2 and B2, =NETWORKDAYS(A2,B2,D2:D10) subtracts listed holidays that fall on counted weekdays within the range. Keep the holiday cells as real dates, not text labels. The function uses a standard Saturday-and-Sunday weekend; if your workweek uses different weekend days, Sheets also provides NETWORKDAYS.INTL, which lets you specify a weekend pattern.

These formulas answer different questions. Use subtraction for elapsed calendar days, and NETWORKDAYS for scheduled working days. Neither automatically accounts for company leave policies, shifts, or local public holidays: those rules must be represented in the input data or handled separately.

Calculate months, years, and ages carefully

For a simple age in completed years, place the birth date in A2 and the date to calculate age on in B2. The formula =DATEDIF(A2,B2,"Y") returns whole years completed by that date. If you use TODAY() as the second argument, the result updates when Sheets recalculates, so it represents age as of the current date rather than a fixed reporting date.

For a tenure or subscription period, decide whether you need full months, full years, or a year fraction. For full months, use the “M” unit. A value such as 1.5 months is not what DATEDIF returns; the function counts completed units. For a decimal number of years, Sheets offers YEARFRAC, but its result depends on the selected day-count convention. It is not interchangeable with completed calendar years, and different conventions can produce different fractions.

Be especially careful around month ends and leap days. A range from the last day of one month to a date in the next month may not contain a completed month if the corresponding day has not arrived. That can be correct for an anniversary-based calculation even if it feels surprising when counting month labels on a calendar. When a result affects billing, eligibility, or a deadline, state the counting rule explicitly and check representative boundary dates.

Find the difference between dates and times

Sheets date-time values can include both a date and a time. Subtracting them returns a fraction of a day when the elapsed time is less than 24 hours. For instance, a difference of 12 hours is 0.5 days. With a start date-time in A2 and an end date-time in B2, =B2-A2 calculates the elapsed portion of a day as well as any whole days.

To display the result as elapsed time instead of a decimal, select the result and choose Format > Number > Duration. This is preferable to an ordinary clock-time format for durations that may exceed 24 hours, because a clock display can wrap around at a day. If you want only the elapsed hours as a number, multiply the day difference by 24: with the same A2 and B2 layout, =(B2-A2)*24 returns 36 for a 36-hour interval.

Make sure the inputs really contain times if the calculation depends on them. A cell displaying only a date may still contain a hidden time component, and imported timestamps may include time-zone conventions that Sheets does not interpret as expected. Compare the source data and formatting before treating a surprising decimal as a formula error.

Common problems and how to fix them

The result looks like a date. Change the result cell to Format > Number > Number, or choose Duration for an elapsed-time result.
The formula returns an error. Check that both inputs are valid date values and that the start date precedes the end date when using DATEDIF. Imported date strings may need conversion or re-entry.
The answer is one day off. Decide whether the question asks for elapsed days or an inclusive count of dates. Add 1 only when both endpoints should be counted.
The answer is negative. The end date is earlier than the start date. Confirm the cells or keep the negative result if direction matters.
The weekday count seems wrong. NETWORKDAYS includes weekdays at both ends and excludes weekends. Check whether the endpoints are weekdays and whether holiday cells contain valid dates.
The formula will not parse. Spreadsheet locale settings can affect the argument separator. The examples here use commas; if Sheets expects semicolons in your locale, use the separator indicated by the sheet’s formula parser.

When debugging, test with two dates whose difference you can verify on a calendar, then inspect the input cells and the result format. This separates a calculation problem from a display or data-entry problem.

Choose the formula that matches the question

There is no single best date-difference formula for every task. Match the function to the unit and counting convention you actually need:

Elapsed calendar days: subtract the start date from the end date.
Inclusive number of dates: subtract and add 1, if both endpoints count.
Completed months or years: use DATEDIF with “M” or “Y”.
Weekdays excluding weekends: use NETWORKDAYS.
Weekdays excluding listed holidays: add a holiday range to NETWORKDAYS.
Hours and minutes: subtract date-time values and format the result as Duration.

Excel also supports familiar functions such as DATEDIF and NETWORKDAYS, but this page uses Google Sheets in a web browser. Menu labels and behavior can differ between spreadsheet applications, so use Sheets’ own Format menu when changing result display. The core date-subtraction idea is the same: dates are numeric values, and their difference is a numeric duration.

Frequently Asked Questions

What is the formula for the difference between two dates in Google Sheets?
Subtract the earlier date from the later date. If the start date is in A2 and the end date is in B2, use =B2-A2 for elapsed days.

How do I calculate the number of months between two dates?
Use =DATEDIF(A2,B2,"M"), with the earlier date in A2 and the later date in B2. It returns completed months, not a decimal fraction or a count of calendar months touched.

How do I count days including both start and end dates?
With the start date in A2 and end date in B2, use =B2-A2+1. This counts both endpoints, so use it only when that inclusive convention fits the task.

Can Google Sheets calculate working days between dates?
Yes. Use =NETWORKDAYS(A2,B2) to count weekdays between the dates, or supply a holiday range as a third argument to exclude those dates too.

Why is my date difference showing as a date?
The formula result is being displayed with a date format. Select the result cell and choose Format > Number > Number for a day count, or Format > Number > Duration for elapsed time.

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.