No Ticket Needed

Appearance

6 min fix Updated 8 Aug 2026Beginner

By , IT support technician

Quick Answer

Subtract the earlier date from the later one: =B2-A2 returns the number of days. If the answer appears as a date, select the cell and set the Number Format dropdown on the Home tab to General.

How to Calculate the Difference Between Two Dates in Excel ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2016, 2019, 2021 and later
Article Type: How-To
Last Updated: 2026-08-08

Summary ​

Excel stores every date as a number, so the gap between two of them is one subtraction. This article covers that subtraction and the formatting quirk that turns the answer into a date, then the functions that answer the harder questions: complete years and months with DATEDIF, working days with NETWORKDAYS, a four-day week or a Friday-Saturday weekend with NETWORKDAYS.INTL, and an invoice-age column that updates itself with TODAY.

Before You Start ​

  • Any version of Excel. Every function here works on Windows, Mac and the web.
  • Your dates must be real dates rather than text. Step 7 shows how to tell, and how to convert them if they are not.
  • Edit permission on the workbook. No admin rights required.

Instructions ​

1. Subtract One Date from the Other ​

The plainest question — how many days between these two dates — is answered by a minus sign.

  1. Put your earlier date in A2 and your later date in B2. For this example, 01/03/2026 and 15/03/2026.
  2. Click C2 and enter:
=B2-A2
  1. Press Enter. The cell shows 14 — the number of days between the two dates.
  2. Drag the small square at the bottom-right corner of C2 down the column to apply it to every row.

Excel can do this because a date is a number underneath: it counts days from 1 January 1900, so 15/03/2026 is stored as 46096 and 01/03/2026 as 46082. Subtracting one from the other leaves 14.

Note: The result counts the gap, not the days involved. From Monday to Friday is 4, not 5. To count both end dates, add one: =B2-A2+1.

Note: If C2 shows something like 14-Jan instead of 14, the formula is right and the cell format is wrong. Step 2 fixes it in four clicks.

2. Fix a Result That Shows as a Date Instead of a Number ​

This is the single most common surprise in this whole topic, and it is a display problem rather than a formula problem.

  1. Click the cell showing the unexpected date and read the formula bar. Your formula is still there and still correct.
  2. Open the Home tab and look at the Number Format dropdown in the Number group — the box that normally reads General. It reads Date.
  3. Open that dropdown and select General. The cell now shows 14.
  4. Select Number instead if you want a fixed number of decimal places.

Excel copied the format from the cells you subtracted. =B2-A2 produces the number 14, and a cell formatted as a date draws the number 14 as the fourteenth day of January 1900. Nothing about the value changed at any point — only the way it was drawn. That separation between what a cell holds and what it shows is worth ten minutes of your life: Understanding Number Formats in Excel covers it properly.

Tip: Press Ctrl + Shift + ~ with the cell selected to snap it to General without touching the ribbon.

3. Get Years, Months and Days with DATEDIF ​

When "how many days" is the wrong unit — someone's age, a contract term, time in a role — DATEDIF gives you complete years and months instead.

  1. Click an empty cell and enter the formula, putting the earlier date first:
=DATEDIF(A2,B2,"y")
  1. Press Enter. The cell shows the number of complete years between the two dates.
  2. Swap the last argument for the unit you need:
    • "y" — complete years.
    • "m" — complete months.
    • "d" — days, the same answer as plain subtraction.
    • "ym" — months left over after the whole years are taken out.
    • "yd" — days left over after the whole years are taken out.
  3. For a readable "2 years, 5 months" line, combine two of them in one cell:
=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"
  1. Press Enter. The cell reads 2 years, 5 months.

DATEDIF is a legacy function and Excel treats it like one. Microsoft keeps it for compatibility with old Lotus 1-2-3 workbooks. It does not appear in the formula autocomplete list as you type, it is missing from the Insert Function dialog, and no argument tooltip appears to remind you of the order. Type the whole thing yourself, including the quotation marks around the unit. It works in every current version of Excel despite the silence.

Note: Microsoft advises against the "md" unit — it has known limitations and can return a negative or plainly wrong number. Use "ym" and "yd" for the leftovers instead.

Note: DATEDIF returns #NUM! when the first date is later than the second. It has no opinion about which is which and will not swap them for you, so put the earlier date first.

4. Count Working Days with NETWORKDAYS ​

Project deadlines and service-level targets are counted in working days, and NETWORKDAYS drops the weekends for you.

  1. Click an empty cell and enter:
=NETWORKDAYS(A2,B2)
  1. Press Enter. The cell shows the number of Monday-to-Friday days in the range, counting both the start and end dates.
  2. To exclude public holidays, list them somewhere on the sheet first. Put each holiday date in its own cell down column H, from H2 to H12.
  3. Point the formula at that list as a third argument, with dollar signs so it does not drift when you fill the formula down:
=NETWORKDAYS(A2,B2,$H$2:$H$12)
  1. Press Enter. Any holiday falling on a weekday inside the range is now subtracted from the count.

Note: NETWORKDAYS includes both end dates in its count, unlike the subtraction in Step 1. Monday to Friday returns 5.

Note: Holidays already falling on a weekend are not double-counted, so a Christmas Day that lands on a Saturday takes nothing off the total. Excel checks each date once.

Tip: The dollar signs in $H$2:$H$12 matter more than they look. Without them, filling the formula down row by row slides the holiday range down the sheet until it points at empty cells and every row returns a different answer — see Understanding Absolute and Relative Cell References in Excel.

5. Handle a Weekend That Is Not Saturday and Sunday ​

NETWORKDAYS.INTL takes an extra argument that tells Excel which days your organisation treats as the weekend.

  1. Click an empty cell and enter the function with a weekend code as the third argument:
=NETWORKDAYS.INTL(A2,B2,7)
  1. Press Enter. The count now treats Friday and Saturday as the weekend, which is standard across much of the Middle East.
  2. Change the code to match your working week:
    • 1 or omitted — Saturday and Sunday.
    • 2 — Sunday and Monday.
    • 7 — Friday and Saturday.
    • 11 — Sunday only.
    • 16 — Friday only.
  3. For a working pattern the numbers do not cover, pass a seven-character text string instead. Each character is one day starting at Monday; 1 means a non-working day and 0 means a working day:
=NETWORKDAYS.INTL(A2,B2,"0001011")
  1. Press Enter. That string marks Thursday, Saturday and Sunday as non-working and counts the rest.
  2. Add your holiday list as a fourth argument when you need both:
=NETWORKDAYS.INTL(A2,B2,7,$H$2:$H$12)

Note: A weekend string must be exactly seven characters and contain nothing but 0 and 1, or the formula returns #VALUE!. Count them before you press Enter.

6. Build an Invoice-Age Column That Updates Itself ​

TODAY() returns the current date, so a column built on it re-reads itself every time the workbook opens.

  1. Put your invoice dates down column A, starting in A2.
  2. Click B2 and enter:
=TODAY()-A2
  1. Press Enter. The cell shows how many days old that invoice is. If it shows a date, set the cell to General as in Step 2.
  2. Fill the formula down the column by double-clicking the small square at the bottom-right corner of B2.
  3. For days remaining until a deadline rather than days elapsed, reverse the subtraction:
=A2-TODAY()
  1. To flag anything overdue in words, wrap it in IF:
=IF(TODAY()>A2,"Overdue","Due in "&A2-TODAY()&" days")

Note: TODAY() holds no memory of the day you typed it. Open the file next month and every age recalculates against that day's date, which is the point of the column — and the reason it is the wrong tool for recording when something actually happened. For a date that must never move, press Ctrl + ; to stamp today's date as a fixed value instead.

Tip: Pair it with conditional formatting so invoices past 30 days colour themselves red. Select the age column, then Home > Conditional Formatting > Highlight Cells Rules > Greater Than and enter 30 — the full routine is in How to Use Conditional Formatting in Excel.

7. Fix Dates That Are Really Text ​

When every formula above returns #VALUE! or a stubborn 0, the cells are holding text that looks like a date rather than a date.

  1. Click one of the date cells and look at where the value sits. A real date lines up against the right edge of its cell; text hugs the left edge. Excel does that on its own, before any formatting.
  2. Confirm it in an empty cell:
=ISNUMBER(A2)
  1. Press Enter. FALSE means the cell holds text and nothing will calculate against it.
  2. Select the whole column by clicking its letter at the top of the sheet.
  3. Open the Data tab and click Text to Columns in the Data Tools group. The Convert Text to Columns Wizard opens at step 1 of 3.
  4. Click Next twice to reach step 3 of 3.
  5. Under Column data format, select Date and pick the order the text is written in from the dropdown beside it — DMY for 15/03/2026, MDY for 03/15/2026. Getting this wrong turns the third of May into the fifth of March, so check a row you can verify before you continue.
  6. Click Finish. The dialog closes and every value shifts to the right edge of its cell. Text to Columns rewrites the column in place — press Ctrl + Z straight away if the date order came out wrong, and run it again with the other setting.

Note: Values imported from a web page or a mainframe report often carry invisible characters that block the conversion. The full clean-up routine — TRIM, CLEAN and the non-breaking space — is in How to Fix Numbers Stored as Text in Excel.

Tip: For a single stubborn value, =DATEVALUE(A2) converts the text to a date serial number in a helper column. Set that cell's format to Date afterwards, or it displays the raw number.

Troubleshooting ​

WARNING

DATEDIF will not tell you when it is wrong. It returns #NUM! if the first date is later than the second, and Microsoft advises against its "md" unit because it can return a negative or inaccurate figure. Put the earlier date first, stick to "y", "m", "d", "ym" and "yd", and spot-check one row by hand before a calculated age or contract term goes anywhere near a contract.

Symptom / ErrorPotential CauseSolution
Result shows as a date such as 14-JanResult cell inherited the date formatSet the cell to General on the Home tab — Step 2.
#VALUE! from =B2-A2One cell holds text, not a dateCheck the alignment, then convert with Data > Text to Columns — Step 7.
#NUM! from DATEDIFStart date is later than end dateSwap the first two arguments so the earlier date comes first — Step 3.
Excel does not suggest DATEDIF as you typeLegacy function, deliberately hiddenExpected. Type the whole formula including the quotation marks around the unit — Step 3.
Every row returns the same working-day countHoliday range slid as the formula filled downLock the range as $H$2:$H$12 — Step 4.
#VALUE! from NETWORKDAYS.INTLWeekend string is not seven characters of 0 and 1Count the characters and use only 0 and 1 — Step 5.
Ages recalculate every time the file opensTODAY() is meant to do thatExpected for an age column. For a fixed date, press Ctrl + ; to stamp a value instead — Step 6.
Dates arrive as text every time the report refreshesThe query declares the column as textFix it at the import rather than in the sheet — see How to Stop Excel Changing Numbers into Dates.