Days between dates in Excel: subtraction, times, DATEDIF and NETWORKDAYS

Excel stores a date as a whole number of days from an origin and a time as the fraction after it. Everything about counting between two dates follows from that one fact, including every off-by-one anyone has ever reported.

The functions below are copyable. None of them supplies a closure list, and none of them decides whether your count should include its endpoints. Those are yours.

What to take away

Subtraction

With the earlier date in A2 and the later in B2:

=B2-A2            exclusive calendar days
=B2-A2+1          inclusive of both days
=DAYS(B2,A2)      the same as B2-A2, with the arguments reversed

Friday 5 March to Monday 8 March 2027 gives 3, or 4 inclusive. 1 February 2028 to 1 March 2028 gives 29, because 2028 has a 29 February.

Swap the cells and the sign flips; a negative in a date-formatted cell appears as a row of hash signs, which is a format issue, not an arithmetic error.

Times in the cells

A cell holding 5 March 2027 at 15:00 is 46451.625. Subtract a cell holding 8 March at 11:00, which is 46454.4583, and the result is 2.83 days. Rounded, that is 3; truncated, it is 2. Whichever the sheet does, it did not ask you.

=INT(B2)-INT(A2)          whole days between the dates, times ignored
=(B2-A2)*24               elapsed hours, times included

Decide which you mean. A count of dates ignores the times; a count of elapsed time keeps them; a count of working hours needs a window and a different formula, set out under working hours.

DATEDIF

DATEDIF(start, end, unit) is not in the function list but works in every version. The units:

Use "d", "m" and "y" freely. Treat "md" with care. Microsoft's own documentation warns that it can return wrong results around month ends; the safest course is to compute days-after-months yourself with EDATE. DATEDIF returns an error when start is later than end, unlike subtraction, which returns a negative.

Working days

<figure class="inline-figure">
  <img src="/inline/days-between-dates-excel-fig4-1280.webp" alt="Comparison table showing NETWORKDAYS equals WORKDAY steps plus one (Reading days between dates excel with a sceptical eye)" loading="lazy" decoding="async" />
  <figcaption>A phantom error appears unless you reconcile NETWORKDAYS with WORKDAY steps plus one. <a href="https://workdaydatecalculator.com/image-licence">Image: Workday Date Calculator</a></figcaption>
</figure>
=NETWORKDAYS(A2,B2)                    both endpoints in, Saturday-Sunday rest days
=NETWORKDAYS(A2,B2,Closures)           the same, minus closures on working days
=NETWORKDAYS.INTL(A2,B2,"0000110")     Friday-Saturday rest days, string read Monday to Sunday

Monday 1 March to Monday 8 March 2027 gives 6. Monday 1 March to Monday 15 March gives 11, and 10 if Closures contains the Wednesday in between. Saturday 6 to Sunday 7 gives 0. A single working day counted against itself gives 1, and 0 if that day is in Closures.

WORKDAY(A2,5) from Monday 1 March returns Monday 8 March. NETWORKDAYS over that span returns 6. A sheet that checks one against the other will flag a phantom error on every row; the reconciliation is NETWORKDAYS = WORKDAY steps + 1 whenever the start is a working day.

Closures is a range you supply. This page supplies none, for any country. Entries that are text rather than dates are ignored without warning, and a range that does not span the window treats the uncovered part as fully open; both produce a plausible wrong number.

A check block worth keeping

Six rows at the bottom of any sheet that counts dates, each with the expected value beside it and a flag:

Test	Formula	Expect

When someone changes a format, repoints a range or pastes a text date, the flags go red before the wrong count goes anywhere.

What the sheet cannot decide

Whether the document you count under means exclusive, inclusive or clear days; whether "days" means calendar or working; and whose closure list applies. The six readings a pair of dates can carry are under counting between two dates.

The day-number arithmetic the sheet performs underneath B2-A2 is worth seeing once. It shows why subtraction is exact and why the weekday check works.

When a service has to reproduce the same count, the serial-versus-text contract between the two is where the day goes missing. The four decisions inside "business day", which NETWORKDAYS answers with one silent default each, are under adding business days.

Common questions

Why does my count come out as a date?

Because the result cell inherited a date format from its neighbors. The value is right; set the format to General or Number.

Why is DATEDIF not in the autocomplete list?

It is kept for compatibility and not advertised. It works in every current version; type it in full.

Can NETWORKDAYS exclude the start day?

Not by argument. Subtract one when the start is a working day, or use NETWORKDAYS(A2+1,B2), which is the same thing and reads more honestly.

Why does subtraction give a number rather than a date?

Because a date in a sheet is a day count from a fixed origin, in the manner of the astronomical Julian day number, and subtracting two counts leaves a count rather than a position. Format the cell as a number and the result reads correctly.

Should imported dates be in a particular order?

In ISO 8601 order, largest component first, whenever you have the choice. It parses the same way under every locale, which removes the failure where only the ambiguous rows transpose.

Does the closures range need to be sorted or deduplicated?

No. Order, duplicates and weekend entries are harmless. Only text entries and gaps in coverage cause wrong answers.