How to Calculate Days Between Dates in Excel

Excel stores every date as a number. 1 January 1900 is 1, and every day since has counted up from there. Once you know that, date arithmetic stops being mysterious — subtracting two dates is subtracting two numbers.

Here is every formula worth knowing, what each one actually counts, and the two inconsistencies in Excel’s own functions that cause most spreadsheet date bugs.

The simplest formula

=B2-A2

That is it. If A2 holds the earlier date and B2 the later, this returns the number of days between them. It works identically in Excel, Google Sheets and LibreOffice.

If it shows a date instead of a number, the result cell has inherited date formatting from the cells above. Select it and set the format to General or Number. The value was always correct; only the display was wrong.

If it returns a negative number, your dates are the wrong way round. Wrap it if the order might vary:

=ABS(B2-A2)

DAYS, and why it exists

=DAYS(B2, A2)

Identical result to subtraction, but note the argument order is reversed — end date first. It exists mainly for readability in long formulas, and because it errors clearly if you pass it text that is not a date, where subtraction would silently produce #VALUE!.

Working days only

=NETWORKDAYS(A2, B2)

Excludes Saturdays and Sundays. To exclude public holidays too, list them in a column and pass the range:

=NETWORKDAYS(A2, B2, $D$2:$D$12)

The dollar signs lock the holiday range so it does not shift when you fill the formula down.

The inconsistency that causes real bugs

This is the single most useful thing on this page, and it is not documented prominently anywhere.

• =B2-A2 is exclusive. Monday to Friday returns 4.
• =NETWORKDAYS(A2,B2) is inclusive. Monday to Friday returns 5.

Same two cells, same week, different answers — because the functions count differently by design. In a sheet that uses both, the mismatch is almost invisible and produces totals that are off by one per row.

If you need NETWORKDAYS to behave exclusively, subtract one. If you need subtraction to include both endpoints, add one. Decide which convention the sheet uses and apply it everywhere.

Non-standard weekends

=NETWORKDAYS.INTL(A2, B2, 7, $D$2:$D$12)

The third argument sets which days are the weekend:

• 1 — Saturday and Sunday (the default)
• 7 — Friday and Saturday
• 11 — Sunday only
• 17 — Saturday only

For full control, pass a seven-character string of 1s and 0s starting at Monday. "0000011" is a Saturday–Sunday weekend; "0000110" is Friday–Saturday.

Months and years, not days

Do not divide by 30 or 365. Use DATEDIF, a function Excel has never listed in its formula autocomplete but has supported for decades:

=DATEDIF(A2, B2, "d")   days
=DATEDIF(A2, B2, "m")   whole months
=DATEDIF(A2, B2, "y")   whole years
=DATEDIF(A2, B2, "ym")  months, ignoring years
=DATEDIF(A2, B2, "md")  days, ignoring months and years

Combining the last three gives a proper “2 years, 4 months, 13 days” breakdown. Dividing by 365 drifts by a day every four years and produces the classic off-by-one on birthdays and policy expiry dates.

DATEDIF requires the start date to come first and errors if the arguments are reversed — the opposite of DAYS. Excel is not consistent about this and there is no rule to remember; just check each time.

Adding days rather than counting them

=A2+30                          30 calendar days later
=WORKDAY(A2, 10, $D$2:$D$12)    10 working days later

WORKDAY counts forward from the start date without including it, while NETWORKDAYS includes it. The two functions do not mirror each other. That asymmetry is a genuine design wart and worth a comment in any sheet that uses both.

When dates will not subtract

• #VALUE! — one cell holds text that looks like a date. Excel imports CSVs this way constantly. Select the column and use Data → Text to Columns → Finish to force conversion.
• The answer is wildly wrong — a day/month/year ordering mismatch. A sheet made in one locale opened in another will read 03/04 as March 4th or 4th March depending on regional settings.
• Everything returns zero — the cells contain text, not dates. A quick test: right-align is a real date, left-align is text.
• Off by four years — a legacy Mac workbook using the 1904 date system. Check File → Options → Advanced.

When you do not want a spreadsheet

For a single one-off calculation, opening Excel is more work than the sum deserves. Our days between dates calculator does it in the browser with a working-days option built in, and nothing you type is sent anywhere.

For exact ages in years, months and days — the DATEDIF combination above — the age calculator handles the month-boundary arithmetic without the formula gymnastics.

The same date functions let you calculate age in Excel from a date of birth.