Half the questions people bring to Excel are secretly date questions. How many days until the deadline? Which invoices are more than 30 days old? What’s the last working day of the month? And half the pain with dates comes from not knowing one small fact about how Excel stores them.
Here’s the fact: a date is just a number. Specifically, the count of days since 1 January 1900. Today’s date is a number in the 46,000s; tomorrow is that number plus one. Everything else — the slashes, the month names, “14 July 2026” — is a number format, a costume the value wears.
Once you believe that, date maths stops being mysterious:
=B2-A2
Finish date minus start date. That’s it — subtraction gives you days between, because they’re just numbers. No function required.
The functions that matter
Excel has dozens of date functions. Real work uses about six.
TODAY() — the current date, refreshed every time the workbook
recalculates. The moment a sheet contains TODAY(), it answers
questions forever: an ageing column is just =TODAY()-B2, and it’s
correct every morning without anyone touching it. (Its sibling
NOW() includes the time; you want it less often than you think.)
EOMONTH(start, months) — the end of a month, any month. The
second argument shifts: 0 is this month’s end, 1 is next
month’s, -1 is last month’s. Invoices due at month-end plus 30
days? =EOMONTH(A2,1). First day of this month? One day after the
end of last month: =EOMONTH(TODAY(),-1)+1. That little idiom reads
oddly the first time and then becomes a reflex.
EDATE(start, months) — same idea, but same-day-shift: three
months from the 14th is the 14th. Contract renewals, probation ends,
anything anniversary-shaped.
NETWORKDAYS(start, end) — days between two dates counting only
Monday to Friday, with an optional third argument for a holiday
list. This is the deadline function:
=NETWORKDAYS(TODAY(), C2, Holidays)
where Holidays is a small named range of bank holidays you type
once a year. Its partner WORKDAY(start, days) goes the other way —
“what date is 10 working days from now?”
DATE(year, month, day) — builds a date from three numbers.
This is the safe way to construct dates in formulas, and it’s
forgiving: =DATE(2026, 13, 1) happily gives you 1 January 2027.
That forgiveness is a feature — “this month plus one” never overflows.
The trap that isn’t a formula problem
If date maths gives you #VALUE!, or sorting puts March after
November, your “dates” are almost certainly text wearing a date
costume — the classic export problem. No date function fixes text;
you fix the data first. That’s step four of
the cleaning routine,
and it’s usually one pass of Text to Columns.
The quick diagnostic: real dates line up on the right of the cell and change appearance when you change the number format. Text-dates sit on the left and ignore you.
A worked example: the ageing column
Put it together — an invoice list in a Table, with a due date in column C:
=MAX(0, TODAY()-[@Due])
Days overdue, never negative, updating itself daily. Add a status column with your banding logic and you’ve built the report your finance team currently makes by hand every Monday.
Dates sit in stage three of the path because they reward exactly this kind of small-formula thinking: subtraction, one or two well-chosen functions, and the knowledge that underneath it all, a date is only a number counting patiently upward from 1900.
Six functions, one fact, no fear. That’s dates done.