Excel Formula for Date Minus Date: Days Between

Formulas & functions · Updated November 22, 2026

The core Excel formula for subtracting one date from another is simply =B2-A2, where B2 holds the later date and A2 holds the earlier date. This returns the number of whole days between the two dates, because Excel stores dates as serial numbers (1 = January 1, 1900). For more control, use =DATEDIF(A2,B2,"d") for days, "m" for months, or "y" for years.

Why Date Subtraction Works in Excel

Excel treats every date as an integer that counts days since January 1, 1900 (for Windows). For example, January 1, 2024 is serial number 45292. When you subtract two dates, Excel performs ordinary arithmetic on those serial numbers, so the result is the count of whole days between them. This is why you can add 7 to a date to get the date one week later, or subtract 30 to go back roughly a month.

Because of this system, the result of =B2-A2 is always a number, not a date. If you see a date format (like 01/15/1900) instead of a number, the cell is formatted as a date. Fix that by selecting the cell and pressing Ctrl+Shift+~ (General format) on Windows, or Cmd+Shift+~ on Mac.

Using DATEDIF for Days, Months, or Years

The DATEDIF function is a hidden but fully supported function that gives you more than just days. Its syntax is =DATEDIF(start_date, end_date, unit). With dates in A2 (start) and B2 (end), the common units are:

  • "d" – complete days (same as simple subtraction)
  • "m" – complete calendar months between the dates
  • "y" – complete years
  • "ym" – months remaining after full years (e.g., 1 year and 3 months gives 3)
  • "yd" – days remaining after full years (ignores months and years)
  • "md" – days remaining after full months (ignores years and months)

Example: With A2 = 01/15/2023 and B2 = 03/20/2024, =DATEDIF(A2,B2,"d") returns 430, "m" returns 14, and "y" returns 1. Note that DATEDIF requires the start date to be earlier than the end date; otherwise it returns a #NUM! error.

How to Choose Between Simple Subtraction and DATEDIF

Simple subtraction (=B2-A2) is best when you only need the exact number of days between two dates, including partial days if times are involved. It is faster, easier to read, and works in every version of Excel. Use DATEDIF when you need calendar months or years, because simple division by 30 or 365 gives inaccurate results due to varying month lengths and leap years.

For example, from January 31 to February 28 is 28 days, but DATEDIF with “m” returns 0 because no full month has passed. If you need a business-day count (excluding weekends and holidays), use =NETWORKDAYS(A2,B2) or =NETWORKDAYS(A2,B2,holidays_range) where holidays_range is a list of dates to exclude. For custom weekends (e.g., Sunday only), use =NETWORKDAYS.INTL(A2,B2,"11") where “11” means Sunday is the only weekend day.

Common Problems and How to Fix Them

Three issues appear most often when subtracting dates:

  • Result shows as a date – The cell is formatted as a date. Change to General or Number format via Home > Number Format dropdown.
  • Negative numbers or #NUM! errors – Your start date is after the end date. Swap the references or use =ABS(B2-A2) to get the absolute difference.
  • Dates stored as text – If the cell is left-aligned and subtraction returns #VALUE!, the date is text. Convert it using =DATEVALUE(A2) or use Data > Text to Columns > Delimited > Next > Date (MDY).

Another subtle issue: if your dates include times (e.g., 01/15/2024 14:30), simple subtraction returns a decimal (e.g., 2.5 for 2 days and 12 hours). To get whole days, wrap with =INT(B2-A2) or use =ROUNDDOWN(B2-A2,0).

Comparing with Other Spreadsheet Tools

Google Sheets uses the same date serial system and the same functions: =B2-A2 and =DATEDIF(A2,B2,"d") work identically. However, Google Sheets does not have the NETWORKDAYS.INTL function; it only has NETWORKDAYS with a fixed Saturday-Sunday weekend. For custom weekends in Sheets, you must use a workaround with ARRAYFORMULA and WEEKDAY. Excel for the web also supports DATEDIF and NETWORKDAYS.INTL, but the function list may be hidden in the formula autocomplete—type the full function name manually and it works.

Real-World Examples and Formulas

Assume column A has start dates (A2:A10) and column B has end dates (B2:B10). Here are practical formulas:

  • Days between: =B2-A2 → returns e.g., 14
  • Full months between: =DATEDIF(A2,B2,"m") → returns e.g., 3
  • Age in years on a specific date: =DATEDIF(A2,TODAY(),"y") → returns e.g., 25
  • Days until a deadline from today: =B2-TODAY() → returns a positive number if future, negative if past
  • Business days between two dates: =NETWORKDAYS(A2,B2) → returns e.g., 10

For a project timeline, you might calculate the remaining days with =IF(B2>TODAY(), B2-TODAY(), 0) to avoid showing negatives. Always ensure your date cells are actually dates (not text) by checking they right-align by default.

Frequently Asked Questions

How do I subtract two dates and get the result in months?

Use =DATEDIF(start_date, end_date, "m"). This returns whole calendar months. For example, from 01/15/2024 to 03/10/2024 returns 1, because a full month (Feb) has passed but March is not complete. If you need fractional months, divide days by 30.44, but that is less accurate.

Why does my date subtraction return a date like 01/00/1900?

That means the cell is formatted as a date. The underlying value is correct (e.g., 5), but Excel displays it as a date serial. Select the cell, go to Home > Number Format, and choose General or Number. The display will change to 5.

Can I subtract dates across different columns or sheets?

Yes. Reference the cells directly, e.g., =Sheet2!B2 - Sheet1!A2. The serial numbers are universal, so cross-sheet subtraction works fine. Just ensure both are true dates, not text.

What is the difference between DATEDIF and simple subtraction for days?

For whole days, they are identical. DATEDIF with “d” and =B2-A2 both return the same integer. The difference appears when you need months or years, where DATEDIF accounts for calendar structure. Also, DATEDIF ignores time components, so if your dates include times, simple subtraction gives a decimal, while DATEDIF truncates to whole days.

How do I calculate age from a birthdate in Excel?

Use =DATEDIF(birthdate, TODAY(), "y"). For example, with birthdate in A2, =DATEDIF(A2, TODAY(), "y") returns the person’s age in years. To include months and days, combine units: =DATEDIF(A2,TODAY(),"y") & " years, " & DATEDIF(A2,TODAY(),"ym") & " months".

Does Excel handle dates before 1900 correctly?

No. Excel’s date system starts at January 1, 1900 (serial 1). Dates before that are not recognized as dates; they are treated as text. If you need to work with pre-1900 dates, you must store them as text and use custom formulas, which is complex and error-prone.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.