=B2-A2 returns the number of days between the date in A2 and the later date in B2. Excel stores every date as a whole number of days (2026-03-15 is 46096), so subtracting one date from another gives the days in between.
| A | B | C | |
|---|---|---|---|
| 1 | Ordered | Delivered | Days |
| 2 | 2026-03-02 | 2026-03-09 | 7 |
| 3 | 2026-03-05 | 2026-03-07 | 2 |
| 4 | 2026-03-10 | 2026-04-02 | 23 |
| 5 | 2026-03-28 | 2026-04-03 | 6 |
| 6 | 2026-12-29 | 2027-01-04 | 6 |
The orders took 7, 2, 23, 6 and 6 days. Subtraction handles month and year boundaries on its own: the last order crosses New Year and still counts 6 days. Change a delivery date and the count follows. If the earlier date is in B, the result is negative: -7 rather than 7.
The DAYS function
=DAYS(end_date, start_date) does the same subtraction, with the end date first. It gives the same numbers as =B2-A2; the main reason to prefer it is that the name says what the formula does.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | B2-A2 | DAYS |
| 2 | 2026-01-15 | 2026-03-10 | 54 | 54 |
| 3 | 2026-02-01 | 2026-03-01 | 28 | 28 |
| 4 | 2024-02-01 | 2024-03-01 | 29 | 29 |
February 2026 has 28 days and February 2024 has 29: Excel knows the calendar, leap years included. Watch the argument order: =DAYS(A2,B2) returns -54 for the first row. DAYS needs Excel 2013 or later; subtraction works everywhere.
Count days including the start and end date
Subtraction counts the days between two dates, which is the number of nights. A booking from July 6 to July 10 is 4 nights, but a car rented from July 6 to July 10 is paid for 5 days. When both the first and last day count, add 1.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | From | To | Nights | Days (both counted) |
| 2 | 2026-07-06 | 2026-07-10 | 4 | 5 |
| 3 | 2026-07-31 | 2026-08-01 | 1 | 2 |
A single day, from 2026-07-06 to 2026-07-06, is 0 by subtraction and 1 with the +1. This is where most off-by-one errors in date sheets come from, so decide which you need before filling the column.
Weeks, months and years between two dates
Divide the days by 7 for weeks. Months and years are not a fixed number of days, so use DATEDIF, which counts complete calendar months and years.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Start | End | Weeks | Full weeks | Months | Years |
| 2 | 2025-06-15 | 2026-09-30 | 67.4 | 67 | 15 | 1 |
| 3 | 2024-01-01 | 2026-01-01 | 104.4 | 104 | 24 | 2 |
From 2025-06-15 to 2026-09-30 is 67.4 weeks, 67 full weeks, 15 complete months and 1 complete year. DATEDIF needs the earlier date first and returns #NUM! the other way round.
Working days between two dates
To count only Monday to Friday, use NETWORKDAYS(start, end). It counts both the start and the end date when they are weekdays, and can skip a list of holidays as a third argument. NETWORKDAYS and WORKDAY covers holidays and other weekends.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Calendar days | Working days |
| 2 | 2026-03-02 | 2026-03-13 | 12 | 10 |
| 3 | 2026-03-06 | 2026-03-09 | 4 | 2 |
March 2 to March 13, 2026 is 12 days counting both ends, of which 10 are weekdays. A Friday to the following Monday is 4 days and 2 working days.
Practice: delivery time
| A | B | C | |
|---|---|---|---|
| 1 | Ordered | Delivered | Days |
| 2 | 2026-05-18 | 2026-05-27 |
Your turn: In C2, calculate how many days the order took, from the order date in A2 to the delivery date in B2.
Why the result shows as a date or
Two display problems come up often with date subtraction:
- The result looks like a date (
1900-01-07, or1/7/1900with US settings). The cell kept a date format, so 7 days is shown as day 7 of Excel's calendar. Select the cells and choose Home > Number Format > General. - The cell fills with
#####. The result is negative (the dates are the wrong way round) and the cell is formatted as a date, which cannot be negative. Swap the dates, use=ABS(B2-A2), or format the cell as General to see the negative number.
If the result is #VALUE!, one of the cells holds text that only looks like a date, such as 15.03.2026 in an Excel set to US dates. Check with =ISNUMBER(A2): a real date returns TRUE.
Frequently Asked Questions
How do I calculate the number of days between two dates in Excel?
Subtract the earlier date from the later one: =B2-A2. Excel stores dates as numbers of days, so the result is the number of days between them. =DAYS(B2,A2) gives the same result.
How do I count days including the start and end date?
Add 1: =B2-A2+1. From July 6 to July 10 is 4 days by subtraction (the nights of a hotel stay) and 5 days counting both ends (the days of a rental).
How do I calculate the months between two dates in Excel?
=DATEDIF(A2,B2,"M") counts the complete months. For years use "Y". DATEDIF needs the earlier date first.
What is the difference between DAYS and subtracting dates?
None in the result: =DAYS(B2,A2) and =B2-A2 return the same number of days. DAYS takes the end date first and needs Excel 2013 or later; subtraction works in every version.
How do I calculate the weeks between two dates in Excel?
Divide the days by 7: =(B2-A2)/7 gives weeks with a decimal part, and =INT((B2-A2)/7) the complete weeks.
