Date Formula Generator

Pick what you want to work out from two dates and get the formula, including the ones Excel hides from the function list.

When to use TODAY

Ageing invoices, or working out how overdue something is
Adding a payment term to an invoice date
Counting working days between two dates for a deadline

How the formula works

Taking the example on this page apart, piece by piece:

=DATEDIF(A2,B2,"D")

A2
The start date.
B2
The end date.
"D"
The unit: "D" counts whole days. Use "M" for whole months or "Y" for whole years.

Where it goes wrong

Typing a date straight into a formula
A bare date string is read according to the reader's locale, so the same file can mean March in London and January in New York. Point at a cell, or build it with DATE().
Dates stored as text
If a date sits on the left of its cell rather than the right, Excel is treating it as text, and every date calculation on it will fail or mislead.
Expecting DATEDIF to autocomplete
It does not appear in Excel's function list at all. It still works — Microsoft keeps it for compatibility — you just have to type the whole name.

Practise it

A generator gives you the formula. These give you the reason it works — and tell you which assumption broke when it does not.

Calculate invoice age with TODAY · intermediate

Questions

How do I get the number of days between two dates?

Subtract one from the other, or use DATEDIF with a D at the end. Both give whole days. DATEDIF earns its place when you want whole months or years instead, by changing that last argument.

How do I calculate someone's age?

DATEDIF from the date of birth to TODAY with a Y at the end gives completed years, which is what age means — it does not round up the day before a birthday.

How do I skip weekends?

WORKDAY adds working days to a date and NETWORKDAYS counts them between two dates. Both treat Saturday and Sunday as the weekend, both accept a list of holidays, and the INTL versions let you define a different week.

Practise TODAY Learn TODAY