- Why subtracting dates works
- Four ways to count the gap
- DAYS: the dedicated function, with one trap
- DATEDIF for days, months and years
- The DATEDIF warning Microsoft gives
- Years, months and days in one cell
- Counting from today
- Workdays only: NETWORKDAYS and NETWORKDAYS.INTL
- The gap between two times
- How to set it up
- My verdict
- Frequently asked questions
To calculate the days between two dates in Excel, subtract the earlier date from the later one, for example =B2-A2. It works because Excel stores every date as a number, so for complete months or years use DATEDIF, and for working days use NETWORKDAYS.
That tip comes straight from Microsoft's DATEDIF page, and it's the method I'd start with every time. The other functions earn their place when you need something subtraction can't give you: whole months, whole years, weekdays only, or the gap between two times of day.
Why subtracting dates works
Excel stores dates as sequential serial numbers. January 1, 1900 is serial number 1. January 1, 2008 is serial number 39448, because it's 39,447 days after January 1, 1900. Subtract one date from another and you're subtracting two numbers, so the answer is the number of days between them.
If the result shows as a date instead of a number, the cell has picked up date formatting. Format it as a number and the day count appears. My guide to changing the date format in Excel covers the formatting side.
Four ways to count the gap
| You want | Use | Example from Microsoft |
|---|---|---|
| Days between two dates | Subtract (end minus start), or DAYS | 1 June 2001 to 15 August 2002 = 440 days |
| Complete years | =DATEDIF(start,end,"Y") | 1 January 2001 to 1 January 2003 = 2 |
| Complete months | =DATEDIF(start,end,"M") | Counts only full months |
| Weeks | DATEDIF days divided by 7 | Microsoft's suggested method |
| Workdays only | NETWORKDAYS or NETWORKDAYS.INTL | Weekend code 1 = Saturday and Sunday |
| Days from today | Use TODAY() as one of the dates | Updates each time the sheet recalculates |
DAYS: the dedicated function, with one trap
DAYS is the function built for exactly this job. Its syntax is DAYS(end_date, start_date). Notice the order: the end date comes first. Most date functions, including DATEDIF and NETWORKDAYS, put the start date first, so if you switch between them it's easy to get a negative answer. Plain subtraction avoids the question altogether.
DATEDIF for days, months and years
DATEDIF(start_date, end_date, unit) calculates the number of days, months or years between two dates. The unit decides what you get back:
| Unit | Returns |
|---|---|
"Y" | Complete years |
"M" | Complete months |
"D" | Days |
"MD" | Days, ignoring months and years (Microsoft advises against it) |
"YM" | Months, ignoring days and years |
"YD" | Days, ignoring years |
Microsoft's examples make the units concrete. From 1 January 2001 to 1 January 2003 with "Y" gives 2 complete years. From 1 June 2001 to 15 August 2002 with "D" gives 440 days, and the same two dates with "YD" give 75 days, because the years are ignored. For weeks, Microsoft's walkthrough takes the days result and divides it by 7.
If the start date is later than the end date, DATEDIF returns #NUM!.
The DATEDIF warning Microsoft gives
This is the catch. DATEDIF exists in Excel to support older workbooks from Lotus 1-2-3, and Microsoft says it may calculate incorrect results under certain scenarios. It specifically doesn't recommend the "MD" unit, because it has known limitations and may give inaccurate results.
So use DATEDIF for complete years and complete months, where it's handy, but don't lean on MD. For plain days, subtraction or DAYS is safer.
Years, months and days in one cell
For time of service, or the gap between two milestones, you'll often want an answer like 2 years, 4 months, 5 days. Microsoft's walkthrough builds it in three parts:
- Use DATEDIF with
"y"for the total complete years. - Use DATEDIF with
"ym"for the months left over after the last full year. - Use a separate formula for the remaining days: subtract the first day of the ending month from the end date.
- Join the three parts in one cell with ampersands, quotes and text such as " years, ".
Microsoft notes that you can press ALT+ENTER to add line breaks inside a long formula like this, which makes it much easier to read. Age is the same calculation with a date of birth as the start, and my guide to calculating age in Excel walks through that case.
Counting from today
Put the TODAY() function in the formula instead of a typed date, and Excel uses your computer's current date. TODAY has no arguments, so the brackets stay empty. If it doesn't update when you expect it to, Microsoft says the recalculation settings under File, Options, Formulas may need changing.
Workdays only: NETWORKDAYS and NETWORKDAYS.INTL
NETWORKDAYS(start_date, end_date, holidays) returns the number of whole working days, leaving out weekends and any holidays you list in the optional third argument.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
NETWORKDAYS.INTL does the same job but lets you choose which days count as the weekend. Microsoft's method for holidays is tidy: type the holiday dates in a separate range, name it with Formulas, Define Name (Microsoft's example name is MyHolidays), and add the name as the last argument. In the weekend argument, 1 sets Saturday and Sunday as weekend days and 2 sets Sunday and Monday. Change the number if your weekend falls on different days.
The gap between two times
Elapsed time works the same way: subtract the start time from the end time. Type full times with a space before AM or PM, format the time cells as Custom h:mm AM/PM, and format the result as h:mm so it shows hours and minutes without AM or PM.
For two full date-and-time values, format both as date-times, then give the difference a custom format so it doesn't show up as a number with a decimal.
How to set it up
- Type the start date in one cell and the end date in another, as full dates.
- For days, type
=end_cell-start_celland press Enter. Format the result as a number if it shows a date. - For complete years or months, type
=DATEDIF(start_cell,end_cell,"Y")or use"M" - For workdays, type
=NETWORKDAYS(start_cell,end_cell)and add a holiday range as the third argument.
There's more on this in the guide on how to use COUNTIFS in Excel.
My verdict
Start with plain subtraction. It's the simplest formula on this page and it's the one Microsoft itself suggests. Reach for DATEDIF only when you need complete months or years, skip the MD unit, and use NETWORKDAYS whenever the answer is about working time rather than calendar time, because a deadline that's ten calendar days away might be far fewer working days.
Another page that helps here: the Excel Agent Mode guide.
Frequently asked questions
How do I calculate the number of days between two dates in Excel?
Subtract the earlier date from the later one. Excel stores dates as numbers, so the result is the number of days.
What's the DAYS function's argument order?
DAYS(end_date, start_date). The end date comes first, unlike DATEDIF and NETWORKDAYS.
Is DATEDIF reliable?
Mostly, for complete years and months. Microsoft says it may give incorrect results in some scenarios and doesn't recommend the MD unit.
How do I count only working days?
Use NETWORKDAYS, or NETWORKDAYS.INTL to choose your weekend days, and add a range of holiday dates as the last argument.
How do I count days from today?
Use TODAY() as one of the dates. It takes no arguments and uses your computer's current date.
