Date Functions in Excel

Date functions in Excel let you calculate with dates, because Excel stores every date as a number behind the scenes. TODAY() gives today's date, DATEDIF finds the gap between two dates, and DAY, MONTH and YEAR pull out parts of a date. With these you can work out someone's age, how many days until a due date, or how long a bill has been pending, all automatically.

Dates are numbers

Excel stores 1-Jan-1900 as 1, and counts up from there. That's why you can subtract one date from another to get the number of days between them. It also means a date can be formatted to look however you like without changing its value.

TODAY and NOW

• =TODAY() returns today's date, and updates each day you open the file.
• =NOW() returns the current date and time.

These are "live" — great for age calculations and days-remaining formulas that should always be current.

Days between two dates

Simply subtract:

=B2-A2

If A2 is the start date and B2 the end date, this gives the number of days between them. Format the result as a number, not a date.

DAY, MONTH, YEAR

Pull out parts of a date:

=DAY(A2)     → 19
=MONTH(A2)   → 7
=YEAR(A2)    → 2026

Useful for grouping data by month or year in reports.

DATEDIF (age and durations)

DATEDIF finds the difference in years, months or days:

=DATEDIF(A2, TODAY(), "Y")   → completed years (e.g. age)
=DATEDIF(A2, TODAY(), "M")   → completed months
=DATEDIF(A2, TODAY(), "D")   → days

To calculate age from a date of birth in A2: =DATEDIF(A2,TODAY(),"Y") gives the age in years.

Quick reference

Worked example (due dates and age)

• Age: date of birth in A2, =DATEDIF(A2,TODAY(),"Y") → 23.
• Days a bill is overdue: due date in B2, =TODAY()-B2 → 12 (12 days overdue).
• Due date 30 days from invoice date in C2: =C2+30.

Adding a number to a date moves it forward that many days, which is how you set payment due dates.

Pro tips

• Type dates in a format Excel recognises (19-Jul-2026 or 19/07/2026) so it treats them as dates, not text.
• If a date shows as a number like 46000, just format the cell as Date.
• =C2+30 adds 30 days; there's no special "add days" function needed.

Common mistakes

• Dates stored as text. If they left-align and won't calculate, they're text. Re-enter or use DATEVALUE.
• Forgetting to format the result. B2-A2 may show as a date; format it as a number to see the day count.
• Wrong DATEDIF unit. "Y" is years, "M" months, "D" days. Using the wrong letter gives the wrong answer.

Key takeaways

• Excel stores dates as numbers, so you can calculate with them.
• TODAY() and NOW() give the current date/time and stay live.
• Subtract dates for days between; add a number to move a date forward.
• DATEDIF calculates age and durations in years, months or days.

Practice task

In one cell put a date of birth and calculate age with DATEDIF. In another, put an invoice date and calculate the due date 30 days later, plus how many days remain until then using TODAY().

Build real, work-ready Excel skills in the ADCA program at HCI.