How to Calculate the Number of Days Between Two Dates in Excel
How many days until the deadline? How many working days did the project take? How many months has someone been a customer? Excel handles all of these with a few simple formulas.
1. Days between two dates: just subtract
Because Excel stores dates as numbers, you can subtract one from another:
=B2-A2
where A2 is the start date and B2 is the end date. The answer is the number of days between them.
If you get a date instead of a number (e.g. 14/01/1900), change the cell's format to General or Number on the Home tab.
Include both the start and end day? Add 1: =B2-A2+1. Useful for things like holiday bookings.
You can also use =DAYS(B2, A2), which does exactly the same. Note the end date comes first.
2. Working days (Monday to Friday)
=NETWORKDAYS(A2, B2)
counts the days from A2 to B2, including both dates, skipping Saturdays and Sundays.
Skip bank holidays too
1. List your bank holiday dates somewhere, for example in H2:H10.
2. Add the range as a third argument:
2. Add the range as a third argument:
=NETWORKDAYS(A2, B2, $H$2:$H$10)
Different weekends
If your weekend isn't Saturday and Sunday, use NETWORKDAYS.INTL. For example, to count days where only Sunday is off:
=NETWORKDAYS.INTL(A2, B2, 11)
As you type the formula, Excel shows a list of weekend options to choose from.
3. Months or years between dates: DATEDIF
=DATEDIF(A2, B2, "m")
The last part controls what's counted:
| Code | Counts |
|---|---|
| "d" | Days |
| "m" | Complete months |
| "y" | Complete years |
| "ym" | Months left over after the whole years |
| "md" | Days left over after the whole months |
The start date must be earlier than the end date, or you'll get #NUM!.
DATEDIF is a hidden function. It doesn't appear in Excel's autocomplete or the function list, but it works in every version. Type it in full.
4. Days until (or since) a date
Use TODAY() so the answer updates every day:
• Days until a deadline: =A2-TODAY()
• Days since an invoice was sent: =TODAY()-A2
• Days since an invoice was sent: =TODAY()-A2
Combine with conditional formatting to highlight anything overdue.
5. Add or subtract days from a date
• 30 days after A2: =A2+30
• 2 weeks before A2: =A2-14
• 10 working days after A2: =WORKDAY(A2, 10)
• 10 working days after, skipping holidays: =WORKDAY(A2, 10, $H$2:$H$10)
• Exactly 3 months after A2: =EDATE(A2, 3)
• The last day of the month: =EOMONTH(A2, 0)
• 2 weeks before A2: =A2-14
• 10 working days after A2: =WORKDAY(A2, 10)
• 10 working days after, skipping holidays: =WORKDAY(A2, 10, $H$2:$H$10)
• Exactly 3 months after A2: =EDATE(A2, 3)
• The last day of the month: =EOMONTH(A2, 0)
Time as well as dates
If your cells include times, subtracting gives a decimal. 1.5 means one and a half days. To see it as hours, multiply by 24: =(B2-A2)*24.
Troubleshooting
#VALUE! One of the dates is stored as text. Check if it's left-aligned, and see our guide on fixing date formats.
A negative number. The dates are the wrong way round. Swap them, or wrap the formula in ABS().
#NUM! from DATEDIF. The start date is after the end date.
The answer looks like a date. Format the result cell as Number.
Wrapping up
Subtract dates for days, use NETWORKDAYS for working days, DATEDIF for months and years, and TODAY() for countdowns that update themselves.
