On this page ⌄
- Subtract one date from the other
- Why subtracting dates works: serial numbers
- The result shows as a date? Format it as a number
- The DAYS function
- Count both the start and the end date
- Days since a date or until a deadline with TODAY
- Whole months and years with DATEDIF
- Count working days with NETWORKDAYS
- Skip holidays with NETWORKDAYS
- Custom weekends with NETWORKDAYS.INTL
- Negative results: dates the wrong way round
- Fix #VALUE!: dates stored as text
- Dates with times: use INT
- Common mistakes when counting days between dates
- Frequently asked questions about days between dates
- Which versions of Excel have these functions?
- Key takeaways
To count the days between two dates in Excel, subtract the earlier date from the later one. Excel stores every date as a number, so the difference between two dates is a number of days:
=end_date - start_dateDAYS does the same job as a function, DATEDIF counts whole months and years, and NETWORKDAYS counts working days only. This guide covers all of them, plus the usual problems: a result that shows up as a date, negative numbers, ######, dates stored as text and dates with times.
The examples use this shipping log in A1:D8:
| A: Order ID | B: Order date | C: Ship date | D: Amount | |
|---|---|---|---|---|
| 2 | SO-1001 | Jan 5, 2026 | Jan 8, 2026 | $420.00 |
| 3 | SO-1002 | Jan 12, 2026 | Jan 20, 2026 | $1,180.00 |
| 4 | SO-1003 | Jan 27, 2026 | Feb 2, 2026 | $760.00 |
| 5 | SO-1004 | Feb 9, 2026 | Feb 10, 2026 | $2,350.00 |
| 6 | SO-1005 | Feb 16, 2026 | Feb 26, 2026 | $515.00 |
| 7 | SO-1006 | Mar 2, 2026 | Mar 5, 2026 | $1,990.00 |
| 8 | SO-1007 | Mar 23, 2026 | Apr 1, 2026 | $880.00 |
Columns B and C hold real Excel dates, not text that looks like a date.
Subtract one date from the other
How many days did SO-1001 take to ship? Subtract its order date from its ship date:
=C2-B2→ 3It was ordered on January 5 and shipped on January 8. The same formula works across months and years, leap days included. SO-1003 went from January 27 to February 2, and SO-1007 from March 23 to April 1:
=C4-B4→ 6=C8-B8→ 9Put the later date first, or you get a negative number.
To get the shipping time for every order, type =C2-B2 in E2 and copy it down to E8. The references are relative, so each copy subtracts the two dates in its own row.
Why subtracting dates works: serial numbers
Excel stores each date as a serial number that counts days from January 1, 1900, which is day 1. A date format only changes how that number is shown. Select B2 and C2 and press Ctrl+Shift+~, the shortcut for the General format, and you see the numbers behind the dates:
=B2→ 46027=C2→ 46030So =C2-B2 is really 46030 minus 46027, which is 3. Press Ctrl+Z to show the dates again.
Times are stored as fractions of a day: noon is 0.5, so 6:00 PM on January 5, 2026 is 46027.75. That's why dates with times give answers with decimals.
The result shows as a date? Format it as a number
When you subtract two dates, Excel often gives the result the same date format as the cells it used. Then instead of 3, you see 1/3/1900, day 3 of Excel's calendar. The answer is right. It's only shown as a date. To fix it, select the cell and either:
- press Ctrl+Shift+~ to apply the General format, or
- on the Home tab, open the Number Format box and choose General.
Number works too, but it shows two decimal places, 3.00, until you click Decrease Decimal.
Your turn. The sheet below is live and uses the same shipping log. The exercise asks how long a different order, SO-1002, took to ship. Find its row, subtract the order date from the ship date and press Enter.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
How many days did order SO-1002 take to ship?
Write your formula in the green cell, G2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
The DAYS function
DAYS returns the number of days between two dates. It does what subtraction does, in the form of a function:
| Argument | What it means | Example |
|---|---|---|
| end_date | The later date | C8 |
| start_date | The earlier date | B8 |
=DAYS(C8, B8)→ 9The end date comes first, in the same order as the subtraction C8 minus B8. That's easy to get backwards, because DATEDIF and NETWORKDAYS both take the start date first. Swap the arguments and DAYS returns -9.
DAYS also accepts dates typed as text and converts them the way DATEVALUE does:
=DAYS("2026-04-01", "2026-03-23")→ 9Type text dates as year-month-day, because 04/01/2026 means April 1 in the US and January 4 in much of Europe. Or build them with DATE(year, month, day) to leave no doubt: =DAYS(DATE(2026, 4, 1), DATE(2026, 3, 23)) also returns 9.
So which should you use? For real dates, DAYS and subtraction always agree, and subtraction converts date text too: ="2026-04-01"-"2026-03-23" also returns 9. DAYS makes the formula say what it does. Subtraction works in every version, while DAYS needs Excel 2013 or later.
Count both the start and the end date
Subtraction doesn't count the first day, so Monday to Friday is 4 days. That's right for elapsed time, like the nights of a hotel stay. When both days count, as in a leave request or a five-day event, add 1.
Say the warehouse charges storage for every day an order sits on its shelves, the day it arrives and the day it ships included. SO-1006 was there from Monday, March 2 to Thursday, March 5:
=C7-B7→ 3=C7-B7+1→ 4March 2, 3, 4 and 5 make four days. With DAYS, it's =DAYS(C7, B7)+1. NETWORKDAYS already counts both ends, so don't add 1 to it.
Days since a date or until a deadline with TODAY
TODAY returns today's date and updates every time the workbook recalculates. Subtract a date from it to find how long ago something happened. On November 18, 2026, the day this guide was published:
=TODAY()-B8→ 240SO-1007 was ordered 240 days earlier. For a countdown, put the deadline first. With December 31, 2026 in H1:
=H1-TODAY()→ 43=NETWORKDAYS(TODAY(), H1)→ 3243 calendar days are left, and 32 of them are weekdays, today included. NETWORKDAYS is covered below. Once the deadline has passed, =H1-TODAY() turns negative, so you may want a message instead:
=IF(H1<TODAY(), "Overdue", H1-TODAY())→ 43Your results will be different, because TODAY moves on every day.
Whole months and years with DATEDIF
Months and years have different lengths, so dividing the days by 30 or 365 only gets you close. DATEDIF counts complete months and years for you:
=DATEDIF(start_date, end_date, unit)The start date comes first, and the unit is a code in quotes:
| Unit | What DATEDIF returns |
|---|---|
"d" | Days, the same as subtracting |
"m" | Complete months |
"y" | Complete years |
"ym" | Months left over after the complete years |
"md" | Days left over after the complete months |
"yd" | Days left over after the complete years |
"Complete" is the important word. SO-1002 was ordered on January 12 and SO-1006 on March 2. That's 49 days, but only one whole month, because the second month wouldn't be complete until March 12:
=DATEDIF(B3, B7, "d")→ 49=DATEDIF(B3, B7, "m")→ 1The other units describe a length of time in years, months and days. Say a customer opened their account on September 18, 2023, and that date is in H2. How long had they been a customer when they placed SO-1007 on March 23, 2026?
=DATEDIF(H2, B8, "y")→ 2=DATEDIF(H2, B8, "m")→ 30=DATEDIF(H2, B8, "ym")→ 6=DATEDIF(H2, B8, "md")→ 5=DATEDIF(H2, B8, "yd")→ 186That's 30 whole months, or 2 years and 6 months, plus the 5 days from March 18 to March 23. Join the pieces with & to write it out:
=DATEDIF(H2, B8, "y")&" years, "&DATEDIF(H2, B8, "ym")&" months, "&DATEDIF(H2, B8, "md")&" days"→ 2 years, 6 months, 5 daysThree things to know about DATEDIF:
- Excel doesn't suggest it. DATEDIF is an old function, kept for workbooks from Lotus 1-2-3, so it isn't in the list that appears as you type, and there's no tooltip for its arguments. Type it in full and it works in every version of Excel. Watch the spelling:
=DATEDIFF(H2, B8, "m"), with two Fs, returns #NAME?. - The start date must come first. If the start date is later than the end date, DATEDIF returns #NUM! rather than a negative number.
- Microsoft warns against "md". Its documentation says the "md" unit can return a negative number, a zero or an inaccurate result. It's fine in the example above, but a safer way to get the days left over is to move the start date forward by the whole months with EDATE, then subtract:
=B8-EDATE(H2, DATEDIF(H2, B8, "m"))→ 5EDATE(H2, 30) is March 18, 2026, 30 months after the start, and B8 is 5 days later.
For years with a decimal, use YEARFRAC:
=ROUND(YEARFRAC(H2, B8), 2)→ 2.51By default, YEARFRAC counts twelve 30-day months a year, an accounting convention, so for whole years, such as an age, DATEDIF with "y" is safer.
Count working days with NETWORKDAYS
NETWORKDAYS counts the weekdays, Monday to Friday, from one date to another, and can skip holidays as well:
=NETWORKDAYS(start_date, end_date, [holidays])| Argument | What it means | Example |
|---|---|---|
| start_date | The first date | B8 |
| end_date | The last date | C8 |
| holidays | Optional. A range of dates to skip, such as public holidays | $F$2:$F$4 |
Unlike DAYS, the start date comes first. SO-1007 was ordered on Monday, March 23 and shipped on Wednesday, April 1:
=C8-B8→ 9=NETWORKDAYS(B8, C8)→ 8There's one weekend in between, March 28 and 29, so you might expect 9 minus 2, which is 7. NETWORKDAYS returns 8 because it counts both the start and the end date: the ten dates from March 23 to April 1, minus the Saturday and the Sunday.
Short orders show it more clearly. SO-1001 was ordered on Monday, January 5 and shipped on Thursday, January 8:
=NETWORKDAYS(B2, C2)→ 4=NETWORKDAYS(B2, C2)-1→ 3Subtract 1 to count only the working days after the order date, the way subtraction does. Only do that when the start date is a weekday: a start date on a weekend isn't counted in the first place.
Skip holidays with NETWORKDAYS
Put the days off in a range and give it to NETWORKDAYS as the third argument. Say the warehouse lists the days it's closed in F2:F4:
| F: Closed | |
|---|---|
| 2 | Jan 1, 2026 |
| 3 | Jan 19, 2026 |
| 4 | Jan 30, 2026 |
They're New Year's Day, Martin Luther King Jr. Day and a Friday stocktake. SO-1003 was ordered on Tuesday, January 27 and shipped on Monday, February 2:
=NETWORKDAYS(B4, C4)→ 5=NETWORKDAYS(B4, C4, F2:F4)→ 4The stocktake on January 30 drops out. Holidays outside the two dates or on a weekend make no difference, so you can list a whole year's holidays once and use the list for every row.
Custom weekends with NETWORKDAYS.INTL
NETWORKDAYS always treats Saturday and Sunday as the weekend. If yours is different, use NETWORKDAYS.INTL, which has a weekend argument before the holidays:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])The weekend is a code number:
| Weekend code | Days off |
|---|---|
| 1, or left out | Saturday and Sunday |
| 2 | Sunday and Monday |
| 7 | Friday and Saturday |
| 11 | Sunday only |
| 17 | Saturday only |
Codes 3 to 6 and 12 to 16 cover the other pairs and single days. Say the warehouse also works on Saturdays, so only Sunday is off:
=NETWORKDAYS.INTL(B4, C4, 11)→ 6=NETWORKDAYS.INTL(B4, C4, 11, $F$2:$F$4)→ 5Saturday, January 31 now counts as a working day. Add the closed days, and the stocktake on January 30 drops out again.
For any other pattern, give the weekend as seven characters in quotes, one for each day from Monday to Sunday, where 1 is a day off and 0 a working day. "0000011" is the usual Saturday and Sunday weekend. For a four-day week, Monday to Thursday:
=NETWORKDAYS.INTL(B8, C8, "0000111")→ 7Fridays are now off as well, so March 27 doesn't count either.
Negative results: dates the wrong way round
If the start date is later than the end date, you get a negative number. Here are the same dates as above, in the wrong order:
=B8-C8→ -9=DAYS(B8, C8)→ -9=NETWORKDAYS(C4, B4)→ -5=DATEDIF(C8, B8, "d")→ #NUM!The minus sign can be useful, because it tells you which date came first. When you only want the number of days, wrap the subtraction in ABS, which drops the sign:
=ABS(B8-C8)→ 9DATEDIF can't return a negative number at all. If the order of the dates can vary, give it the earlier date with MIN and the later one with MAX: =DATEDIF(MIN(B8, C8), MAX(B8, C8), "d") returns 9.
In a cell with a date format, a negative result doesn't appear as a number. It shows ######, because Excel can't show a date before 1900. Format the cell as General to see the number. A column that's too narrow for a date shows ###### too, and widening the column fixes that one.
An empty date gives a much bigger negative number. Say SO-1008 was ordered on Monday, November 16, 2026 and hasn't shipped yet, so C9 is empty. An empty cell counts as 0, so the result is minus the order date's serial number:
=C9-B9→ -46342Test for the empty cell with IF, and either leave the result blank or count the days the order has been open so far:
=IF(C9="", "", C9-B9)=IF(C9="", TODAY()-B9, C9-B9)→ 2On November 18, 2026, the open order is 2 days old.
Fix #VALUE!: dates stored as text
Dates imported from other systems often arrive as text that only looks like a date. There are a few signs:
- The date sits on the left of the cell. Real dates, like other numbers, sit on the right unless someone changed the alignment.
=ISNUMBER(B2)returns TRUE for a real date, because a date is a number. For text, it returns FALSE.- Switching the cell to General doesn't turn it into a five-digit serial number.
When the text is in a form Excel understands, such as 2026-03-23, subtraction and DAYS convert it as they go and the formula still works. It still sorts alphabetically, though, and MAX and MIN skip it. Text Excel can't read gives #VALUE!. Say H4 holds 23.03.2026, a day-first date from a European system, in Excel set to US dates:
=C8-H4→ #VALUE!=DAYS(C8, H4)→ #VALUE!There are three ways to fix it.
DATEVALUE converts text that Excel can read. If H5 holds the text 2026-03-23, =DATEVALUE(H5) returns 46104, the real date. Format the cell as a date to see March 23, 2026.
DATE with LEFT, MID and RIGHT handles text in a layout Excel doesn't expect. Cut out the year, month and day, and let DATE build the real date from them:
=C8-DATE(RIGHT(H4, 4), MID(H4, 4, 2), LEFT(H4, 2))→ 9RIGHT takes the year, MID the month and LEFT the day, and DATE turns them into March 23, 2026. The LEFT, RIGHT and MID guide explains how each piece is cut.
Text to Columns converts a whole column in place. Select the column, choose Data > Text to Columns, click Next twice, pick Date and the order the text uses, such as DMY, and click Finish.
If a few cells can't be fixed, IFERROR can show a message instead of the error, as in =IFERROR(C8-H4, "Check date"). Fix the data where you can, though, because IFERROR hides every other mistake as well.
Dates with times: use INT
Order systems often export a time along with the date. A time is a fraction of a day, so the difference comes out with decimals. Say H6 holds 3/23/2026 4:30 PM, when SO-1007 was ordered, and H7 holds 4/1/2026 9:15 AM, when it shipped:
=H7-H6→ 8.697916667=INT(H7-H6)→ 8=INT(H7)-INT(H6)→ 9The last two answer different questions. INT around the difference counts full 24-hour periods: the order waited 8 days, 16 hours and 45 minutes, so 8. INT around each date drops the times first and counts calendar days, the same 9 you get from the dates alone. For the time in hours, multiply by 24: =(H7-H6)*24 returns 208.75.
Common mistakes when counting days between dates
| Problem | What's going on | Fix |
|---|---|---|
| A date like 1/3/1900 instead of 3 | The result took a date format from the dates | Format it as General with Ctrl+Shift+~ |
###### | A negative result in a cell with a date format, or a column too narrow for a date | Check the order of the dates and format as General, or widen the column |
| A negative number | The start date is later than the end date | Subtract the earlier date, or wrap the formula in ABS |
| A huge negative number, like -46342 | The end date is empty, and an empty cell counts as 0 | =IF(C9="", "", C9-B9) |
#VALUE! | A date is text that Excel can't read | DATEVALUE, DATE with LEFT, MID and RIGHT, or Text to Columns |
#NUM! from DATEDIF | The start date is later than the end date | Swap them, or use MIN and MAX |
#NAME? from DATEDIF | It's spelled DATEDIFF, or the unit isn't in quotes | One F, and quotes around the unit: "m" |
| One day short | Subtraction doesn't count the start date | Add 1 |
| NETWORKDAYS gives one more than expected | It counts both the start and the end date | Subtract 1 |
| Some rows ignore the holidays | The holiday range moved when the formula was copied | Lock it: $F$2:$F$4 |
| A decimal like 8.697916667 | The dates include times | =INT(H7)-INT(H6) |
Frequently asked questions about days between dates
How do I calculate age in Excel?
Use DATEDIF with "y", which counts complete years, with the date of birth first and TODAY() second. With a birth date of November 19, 1990 in H3, on November 18, 2026:
=DATEDIF(H3, TODAY(), "y")→ 35=INT((TODAY()-H3)/365)→ 36The birthday is the next day, so DATEDIF is right: the 36th year isn't complete yet. Dividing by 365 ignores the 9 leap days since 1990, so it has the person turning 36 nine days early. Dividing by 365.25 is closer, but it can still be a day out around birthdays.
How do I count the weeks between two dates?
Divide the days by 7. INT keeps the whole weeks, and MOD gives the days left over:
=(C8-B8)/7→ 1.285714286=INT((C8-B8)/7)→ 1=MOD(C8-B8, 7)→ 2SO-1007 took 1 week and 2 days to ship.
How do I add days or working days to a date?
Add the number of days: =B8+30 returns April 22, 2026. For working days, use WORKDAY. It skips weekends, and holidays if you give it a list, and starts counting on the day after the start date. =WORKDAY(B8, 5) returns Monday, March 30, 2026, five working days after March 23. If you see 46111 instead, format the cell as a date. To add whole months, use EDATE: =EDATE(B8, 1) returns April 23, 2026.
How do I count the dates that fall between two dates?
Use COUNTIFS with one condition for each end. =COUNTIFS(B2:B8, ">="&DATE(2026, 3, 1), B2:B8, "<"&DATE(2026, 4, 1)) counts the orders placed in March and returns 2. The COUNTIF guide covers date conditions, and the SUMIF and SUMIFS guide adds up amounts between two dates.
Which versions of Excel have these functions?
Subtraction, DATEDIF, TODAY, DATEVALUE and INT work in every version of Excel. NETWORKDAYS is built in from Excel 2007 onward, NETWORKDAYS.INTL needs Excel 2010 or later, and DAYS needs Excel 2013 or later. All of them work in Microsoft 365, on Windows and Mac, and in Excel for the web. Google Sheets has DAYS, DATEDIF, NETWORKDAYS, NETWORKDAYS.INTL and TODAY too, with the same arguments. For Microsoft's own examples, see the DAYS function, DATEDIF function and NETWORKDAYS function pages.
For more practice, the dates topic has the free days-to-ship and order-month exercises on the same shipping log, plus Premium exercises on working days with NETWORKDAYS, whole months with DATEDIF and the longest shipping time.
Key takeaways
- Excel stores dates as numbers, so
=C2-B2gives the days between two dates. If the result shows as a date, format the cell as General with Ctrl+Shift+~. =DAYS(end_date, start_date)does the same with the end date first. Add 1 to count both the start and the end date, and use TODAY() for the days since or until a date.=DATEDIF(start_date, end_date, "m")counts whole months and "y" whole years. The start date must come first, or you get #NUM!.=NETWORKDAYS(start_date, end_date, [holidays])counts weekdays, both ends included, and NETWORKDAYS.INTL lets you choose the weekend. Lock the holiday range with dollar signs before copying the formula down.
Practice
Keep going with DAYS and DATEDIF exercises
Reading gets you started. Solving a few of these is what makes it stick.
- 1Days to shipFreeHow many days did order SO-1002 take to ship?→
- 2Which month?FreeWhich month number was order SO-1004 placed in?→
- 3Working days PremiumHow many working days (Monday to Friday) did SO-1005 take, counting both the order and ship dates?→
- 4Whole months between dates PremiumHow many whole months passed between the order dates of SO-1001 and SO-1007?→
- 5Calculate inside MAX PremiumWhat was the longest shipping time of any order, in days? Use one formula.→

About the author
Brian Millot
Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.