Excel stores every date as a number, so subtracting one date from another gives the number of days between them. No function is required.
Syntax
=B2-A2
B2
The later date.
A2
The earlier date. Put them the other way round and the answer is negative.
When to use it
Turnaround times, ages in days, how long an invoice has been outstanding.
What trips people up
The answer often appears as a date, because the cell copied the format of the cells it came from. Set the cell to General or Number and the day count shows. If a date is stored as text it will not subtract at all.
Use NETWORKDAYS(start, end), which counts Monday to Friday and can leave out a list of holidays. It counts both the first and the last day: Monday to Friday of one week gives 5, where plain subtraction gives 4.
Should I use the DAYS function instead?
=DAYS(B2,A2) gives the same answer as =B2-A2. Note that the later date comes first. The subtraction works in every version of Excel and is easier to read.
Why do I get ##### instead of a number?
The result is negative and the cell is formatted as a date, which Excel cannot show. Check that the later date is the one being subtracted from, and set the cell to General.
Related functions
DATEDIF — DATEDIF gives the gap between two dates, counted in whole years, months or days depending on the unit you ask for.
NETWORKDAYS — NETWORKDAYS counts the working days between two dates, leaving out Saturdays and Sundays, and any holidays you hand it.
EDATE — EDATE moves a date forward or back by a whole number of months, and lands on a real date every time.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
