Concept
1. A date is a number
Excel stores every date as a serial number: 1-Jan-1900 = 1, and 1-Oct-2026 = 46296. The date format only changes how it looks. That's why you can subtract dates:
=B2 - A2 → number of days between them
2. TODAY and NOW
=TODAY() → today's date
=NOW() → today's date + current time
Both update every time the sheet recalculates. For a fixed date that never changes (e.g. an entry date), press Ctrl + ; (and Ctrl + Shift + ; for the time).
Days until a due date in B2: =B2 - TODAY()
3. DATEDIF — age and tenure
=DATEDIF(start_date, end_date, unit)
It's a hidden function: Excel won't suggest it as you type, but it works.
Date of birth 15-Aug-1995 in A2, 01-Oct-2026 in B2:
| Unit | Meaning | Result |
|---|---|---|
"Y" |
complete years | 31 |
"M" |
complete months | 373 |
"D" |
days | 11370 |
"YM" |
months after the complete years | 1 |
Age in "31 years 1 month" style:
=DATEDIF(A2,B2,"Y") & " years " & DATEDIF(A2,B2,"YM") & " months"
Avoid the "MD" unit; Microsoft itself warns it can give wrong results. Start date must be on or before end date; a later start gives #NUM!. Equal dates return zero. See Microsoft’s DATEDIF documentation.
4. EOMONTH — end of month
=EOMONTH(start_date, months)
=EOMONTH(A2, 0) → last day of the same month
=EOMONTH(A2, 1) → last day of next month
=EOMONTH(A2, -1) + 1 → first day of the same month
It handles leap years: EOMONTH of 10-Feb-2028 with 0 gives 29-Feb-2028. The result is a serial number — format the cell as a date.
5. WEEKDAY — day of the week
=WEEKDAY(date, [return_type])
For 1-Oct-2026 (Thursday): =WEEKDAY(A2) → 5 (Sunday = 1), and =WEEKDAY(A2, 2) → 4 (Monday = 1). Type 2 is easier: anything above 5 is a weekend.
Just want the day name? =TEXT(A2, "dddd") → Thursday.
6. NETWORKDAYS — working days
=NETWORKDAYS(start_date, end_date, [holidays])
Counts Monday–Friday, including both start and end dates. October 2026:
=NETWORKDAYS("01-Oct-2026", "31-Oct-2026") → 22
=NETWORKDAYS("01-Oct-2026", "31-Oct-2026", H2:H10) → 21
(with 02-Oct listed as a holiday in H2:H10)
For 6-day weeks (Sunday off), common in Indian shops and offices, use NETWORKDAYS.INTL with weekend code 11:
=NETWORKDAYS.INTL("01-Oct-2026", "31-Oct-2026", 11, H2:H10) → 26
Common mistakes
Using TODAY() for dates that should stay fixed. Result showing as a number like 46326 — just format it as a date. DATEDIF with start and end reversed. Forgetting NETWORKDAYS counts both ends.