Days Between Dates in Excel: DAYS, DATEDIF, NETWORKDAYS
· 7 min read
The simplest days between dates Excel formula is a subtraction: the later date minus the earlier one.
=C2 - B2This works because Excel stores each date as a whole number of days, so the difference between two dates is a number of days. For other units, use DATEDIF for whole months or years, and NETWORKDAYS for working days that skip weekends and holidays.
| You want | Formula |
|---|---|
| Calendar days | =C2-B2 or =DAYS(C2, B2) |
| Whole months | =DATEDIF(B2, C2, "m") |
| Whole years | =DATEDIF(B2, C2, "y") |
| Working days | =NETWORKDAYS(B2, C2, holidays) |
| A date N working days later | =WORKDAY(B2, N, holidays) |
The examples use invoices and quotes from the end of 2025, with 25 and 26 December 2025 and 1 January 2026 as holidays. The sheets show dates as year-month-day.
Excel formula for days between two dates
Four invoices, with the date each one was sent and the date it was paid:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Sent | Paid | Days (subtract) | Days (DAYS) | Working days | Holidays | |
| 2 | INV-201 | 2025-11-28 | 2025-12-12 | 14 | 14 | 11 | 2025-12-25 | |
| 3 | INV-202 | 2025-12-15 | 2026-01-05 | 21 | 21 | 13 | 2025-12-26 | |
| 4 | INV-203 | 2025-12-19 | 2025-12-29 | 10 | 10 | 5 | 2026-01-01 | |
| 5 | INV-204 | 2025-12-22 | 2025-12-22 | 0 | 0 | 1 |
INV-201 was sent on 28 November and paid on 12 December:
=C2 - B2Result: 14. If the cell shows a date in January 1900 instead, Excel has copied the date format from the inputs. Change the cell format to General or Number.
The DAYS function
DAYS does the same subtraction, with the end date first:
=DAYS(C2, B2)Result: 14. The name makes a long formula easier to read, and it accepts dates typed as text in quotes, such as =DAYS("2025-12-12", "2025-11-28"). Watch the order: it is end date, then start date, the reverse of most functions. Swap them and you get -14.
Counting both days
Subtraction counts the days between two dates. Sent and paid on the same day, as INV-204, gives 0. If both the first and the last day should count, as for "days the room was booked", add 1: =C2-B2+1.
Weeks between two dates
Divide by 7. =(C2-B2)/7 gives 2 for INV-201. Wrap it in INT to get only whole weeks: =INT((C3-B3)/7) returns 3 for INV-202, which took 21 days.
Working days with NETWORKDAYS
NETWORKDAYS counts Monday to Friday between two dates, and skips any dates in an optional holiday list:
=NETWORKDAYS(start_date, end_date, [holidays])For INV-202, sent on Monday 15 December and paid on Monday 5 January:
=NETWORKDAYS(B3, C3, $H$2:$H$4)Result: 13. There are 16 weekdays in that span; the three holidays are taken off. Without the holiday range it would return 16.
Two details catch people out:
- NETWORKDAYS counts both ends. INV-204, sent and paid on the same Monday, returns 1, while the subtraction returns 0. For INV-201 it returns 11: the Friday it was sent, two full weeks and nothing else.
- Lock the holiday range. Write
$H$2:$H$4so it stays put when you fill the formula down. Our absolute reference guide explains why.
If your weekend is not Saturday and Sunday, NETWORKDAYS.INTL takes a weekend code as its third argument, for example 7 for Friday and Saturday.
A date N working days later: WORKDAY
WORKDAY goes the other way. Given a start date and a number of working days, it returns the date you land on. A supplier's quotes are valid for a set number of working days:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Quote | Issued | Valid for (working days) | Expires | Calendar days instead | Holidays |
| 2 | Q-31 | 2025-12-19 | 5 | 2025-12-30 | 2025-12-24 | 2025-12-25 |
| 3 | Q-32 | 2025-12-22 | 10 | 2026-01-08 | 2026-01-01 | 2025-12-26 |
| 4 | Q-33 | 2025-12-01 | 3 | 2025-12-04 | 2025-12-04 | 2026-01-01 |
| 5 | Q-34 | 2025-12-10 | 10 | 2025-12-24 | 2025-12-20 |
Q-31 was issued on Friday 19 December with 5 working days:
=WORKDAY(B2, C2, $F$2:$F$4)Result: 2025-12-30. WORKDAY does not count the start date. It counts 22, 23 and 24 December, skips Christmas and Boxing Day and the weekend, then counts 29 and 30 December. Adding 5 calendar days in column E gives 24 December, a week too early. Q-34 shows the other risk: 10 calendar days after 10 December is 2025-12-20, a Saturday.
Months and years between dates with DATEDIF
DATEDIF counts whole units between two dates:
=DATEDIF(start_date, end_date, unit)It is an old function that Excel still supports but does not list, so it will not appear when you start typing it. The start date must come first. If it is later than the end date, DATEDIF returns #NUM!.
| Unit | Returns |
|---|---|
"y" | Whole years |
"m" | Whole months |
"d" | Days (same as subtracting) |
"ym" | Months left over after the whole years |
"yd" | Days left over after the whole years |
"md" | Days left over after the whole months. Avoid it: it can give wrong results |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Contract | Start | End | Years "y" | Months "m" | Days "d" | Months after years "ym" |
| 2 | C-11 | 2022-06-01 | 2025-10-31 | 3 | 40 | 1248 | 4 |
| 3 | C-12 | 2025-01-01 | 2025-12-31 | 0 | 11 | 364 | 11 |
| 4 | C-13 | 2024-02-29 | 2025-02-28 | 0 | 11 | 365 | 11 |
| 5 | C-14 | 2025-01-31 | 2025-02-28 | 0 | 0 | 28 | 0 |
C-11 ran from 1 June 2022 to 31 October 2025: 3 whole years, 40 whole months, and 4 months after the 3 years. Join "y" and "ym" for a label: =DATEDIF(B2,C2,"y")&" years, "&DATEDIF(B2,C2,"ym")&" months" gives "3 years, 4 months".
The other rows show that DATEDIF only counts complete units:
- C-12 runs from 1 January to 31 December 2025. That is 364 days, so it is 0 years and 11 months, not 1 year.
- C-13 starts on 29 February 2024. The anniversary has not been reached by 28 February 2025, so again 0 years.
- C-14 runs from 31 January to 28 February 2025: 0 months, because the 31st has not come round again.
For a fractional answer, such as 3.4 years, use =YEARFRAC(B2, C2) instead.
Common mistakes
An empty end date. If the paid date is blank, =C2-B2 treats it as day 0 and returns a large negative number such as -46013. Guard it with =IF(C2="", "", C2-B2), or count unpaid rows separately.
Text that looks like a date. Dates pasted from another system may be text. Subtracting them can return #VALUE!, and DATEDIF fails too. Convert them with DATEVALUE first. Our date formulas guide shows how to spot them.
Forgetting the holiday list. NETWORKDAYS and WORKDAY only skip weekends unless you give them holidays. A due date from an AI assistant that "skips weekends" may still land on a public holiday. Check whether the formula has a third argument, and whether its range covers every holiday in the period.
Mixing up inclusive and exclusive counts. Subtraction excludes the start day; NETWORKDAYS includes both ends. When a number is one off, this is usually why. Decide which one the question means before you write the formula.
Arguments in the wrong order. DAYS takes the end date first; DATEDIF and NETWORKDAYS take the start date first. A negative result or #NUM! usually means they are swapped.
FAQ
How do I calculate the number of days between two dates in Excel?
Subtract the earlier date from the later one, =C2-B2, and format the result as a number. =DAYS(C2, B2) does the same.
How do I calculate months between two dates in Excel?
Use =DATEDIF(B2, C2, "m") for whole months. It only counts a month once the same day of the month has been reached.
How do I calculate years between two dates?
Use =DATEDIF(B2, C2, "y") for whole years, or =YEARFRAC(B2, C2) for a decimal value.
How do I count working days between two dates?
Use =NETWORKDAYS(B2, C2, H2:H4), where H2:H4 lists your holidays. It counts weekdays and includes both the start and end dates.
How do I add working days to a date?
Use =WORKDAY(B2, 10, H2:H4) to get the date 10 working days after B2, skipping weekends and the listed holidays.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- How many days did delivery take?
- Rep tenure in years and months
- Working days from order to delivery
- Promise dates that land on a holidayAudit