Theory
How long has she been a member?
Meera runs a loyalty scheme and wants each customer's exact tenure: not just 'about 2 years' but '2 years, 3 months'. She also wants supplier invoices flagged by days overdue.
Both are date maths, and both are easy because of one fact you already know: Excel stores every date as a serial number (BCA105). That single idea means dates can be subtracted, compared and measured like any number.
This lesson turns that fact into practical tools: TODAY, NOW, plain subtraction, and the precise DATEDIF.
Theory
Dates are just day-numbers
Picture a giant ruler where every day has a number (day 1 = 1 Jan 1900, up to ~46,000 today). Once a date is a number on that ruler, 'how many days between?' is just subtraction, and 'how old?' is measuring the distance. Excel dresses these numbers as calendar dates for you, but underneath, date maths is plain arithmetic on the ruler. That is why everything below works.
At a glance
The date toolkit
| Function | Returns | Use |
|---|---|---|
| TODAY() | Current date (live) | Days overdue, age |
| NOW() | Current date AND time | Timestamps |
| date2 - date1 | Days between | Invoice age |
| DATEDIF(a, b, "Y") | Complete years between | Age, tenure |
| DATEDIF(a, b, "M") | Complete months between | Months of service |
Theory
DATEDIF: exact age and tenure
Plain subtraction gives days; for years and months you want DATEDIF(start, end, unit):
"Y"-> complete years between the dates."M"-> complete months."D"-> days."YM"-> the leftover months after whole years (for '2 years 3 months').
So =DATEDIF(joindate, TODAY(), "Y") gives whole years of membership, and combining "Y" and "YM" gives the exact '2 years 3 months'. And because TODAY() is live, the tenure updates itself as time passes. (DATEDIF is a hidden gem, Excel does not even autocomplete it, but it is fully supported.)
Quiz
Aryan computes =DATEDIF(A2, TODAY(), "Y") for a member's age. Next year he opens the file. What happens to the age?
- It increases automatically, TODAY() is live so the age recalculates
- It stays the same, DATEDIF freezes the value
- It shows an error after a year
- It resets to zero
Show the answer
It increases automatically, TODAY() is live so the age recalculates
TODAY() recalculates every time the file opens or recalcs, so an age or tenure measured against TODAY() updates itself over time, next year it is one year more, automatically. That is usually what you want for 'current age'. If you needed a frozen snapshot ('age at signup'), you would use a static date (Ctrl+;) instead. The live-vs-static choice is the key exam distinction.
Think first
Days overdue
A supplier invoice was due on the date in B2. Aryan wants 'days overdue' as of today. Write the idea of the formula, and explain why it works without any special date function.
Show the answer
=TODAY() - B2. Because both are serial numbers on the date ruler, subtracting the due date from today gives the number of days between, i.e. days overdue (a negative result means it is not due yet). No fancy function needed, date subtraction just works. This is the direct payoff of 'dates are numbers': everyday business questions become one-line arithmetic.
Watch out
Where marks leak
Forgetting dates are serial numbers (so subtraction gives days). Confusing TODAY() (date, live) with NOW() (date+time) and with a static date (Ctrl+;, frozen). Not knowing DATEDIF's units ("Y"/"M"/"D"/"YM") for exact age/tenure. And treating a date column that is secretly text (left-aligned!) as a real date, then the maths fails (recall the data-types lesson). Check alignment, then compute.
Theory
Dates power real reports
Age, tenure, days-overdue, ageing reports (30/60/90 days), all of it rests on date-as-number arithmetic. Once you see dates as points on a ruler, this stops being memorisation. Next lesson shifts to financial functions, PMT for EMIs and NPV for judging an investment, real money maths a shop-owner (and a BCA graduate) actually uses.
Summary
Key takeaways
- Excel stores dates as serial numbers, so date2 - date1 gives the days between.
- TODAY() returns the current date (live); NOW() returns date and time.
- DATEDIF(start, end, unit) gives the difference in complete years ("Y"), months ("M") or days ("D").
- Combine "Y" and "YM" for exact tenure like '2 years 3 months'.
- Ages using TODAY() update automatically; use a static date (Ctrl+;) for a frozen snapshot.
- Memory hook: dates are day-numbers on a ruler, so date maths is just arithmetic.