How Excel Stores Dates
Excel represents dates as serial numbers — the number of days since 1 January 1900.
1 Jan 1900 = 1
27 Jun 2026 = 45834
Times are decimal fractions of a day:
12:00 PM = 0.5 (halfway through the day)
6:00 AM = 0.25
6:00 PM = 0.75
A full datetime:
27 Jun 2026 14:30 = 45834.604167
This is why you can do arithmetic on dates:
=B2 - A2 → number of days between two dates
Getting Today's Date and Current Time
=TODAY() → current date (updates every time the file recalculates)
=NOW() → current date AND time
=TODAY() + 30 → date 30 days from now
=TODAY() - 365 → date one year ago
These are volatile functions — they recalculate every time the file opens or any cell changes.
YEAR, MONTH, DAY — Extract Date Parts
=YEAR(A2) → 2026
=MONTH(A2) → 6 (June)
=DAY(A2) → 27
=MONTH(TODAY()) → current month number
=YEAR(TODAY()) → current year
Common use: Build a column for the year of each transaction to group by year.
=YEAR(A2) → extract year from transaction date in A2
DATE — Build a Date from Parts
=DATE(year, month, day)
=DATE(2026, 6, 27) → 27 Jun 2026
=DATE(YEAR(A2), MONTH(A2)+1, 1) - 1 → last day of A2's month
=DATE(YEAR(A2), 1, 1) → first day of A2's year
Start and End of Month
-- First day of current month
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
-- Last day of current month (first day of next month - 1)
=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1) - 1
-- Or use EOMONTH:
=EOMONTH(TODAY(), 0) → last day of current month
=EOMONTH(TODAY(), 1) → last day of next month
=EOMONTH(TODAY(), -1) → last day of previous month
HOUR, MINUTE, SECOND — Extract Time Parts
=HOUR(A2) → 14 (2 PM)
=MINUTE(A2) → 30
=SECOND(A2) → 0
=TIME(14, 30, 0) → 2:30 PM (builds a time value)
DATEDIF — Calculate Age or Duration
DATEDIF calculates the difference between two dates in years, months, or days:
=DATEDIF(start_date, end_date, unit)
| Unit | Returns |
|---|---|
"Y" | Complete years |
"M" | Complete months |
"D" | Total days |
"YM" | Months ignoring years (0–11) |
"MD" | Days ignoring months and years (0–30) |
"YD" | Days ignoring years |
=DATEDIF(A2, TODAY(), "Y") → age in complete years
=DATEDIF(A2, TODAY(), "M") → total months elapsed
=DATEDIF(A2, TODAY(), "D") → total days elapsed
-- Full age display: "27 years, 4 months"
=DATEDIF(A2, TODAY(), "Y")&" years, "&DATEDIF(A2, TODAY(), "YM")&" months"
Note: DATEDIF is an undocumented Excel function (inherited from Lotus 1-2-3) but works in all versions. The start_date must be earlier than end_date.
Days Between Dates
=B2 - A2 → total calendar days between A2 and B2
=DAYS(B2, A2) → same (Excel 2013+)
=B2 - A2 + 1 → inclusive count (include both start and end days)
NETWORKDAYS — Working Days
Count only working days (excluding weekends):
=NETWORKDAYS(start_date, end_date, [holidays])
=NETWORKDAYS(A2, B2) → working days between A2 and B2
=NETWORKDAYS(A2, B2, HolidayList!A2:A15) → excludes holidays too
NETWORKDAYS.INTL — Custom Weekends
For countries where the weekend is not Saturday/Sunday:
=NETWORKDAYS.INTL(start_date, end_date, weekend, [holidays])
=NETWORKDAYS.INTL(A2, B2, 7) → weekend = Friday/Saturday
=NETWORKDAYS.INTL(A2, B2, "0000011") → Saturday/Sunday as weekend (binary string)
WORKDAY — Add Working Days
Find the date after N working days:
=WORKDAY(start_date, days, [holidays])
=WORKDAY(TODAY(), 5) → 5 working days from today
=WORKDAY(A2, 30, HolidayList!A:A) → 30 working days after A2, skipping holidays
=WORKDAY(A2, -5) → 5 working days before A2
WEEKDAY — Day of Week Number
=WEEKDAY(A2) → 1=Sunday, 2=Monday, ... 7=Saturday (default)
=WEEKDAY(A2, 2) → 1=Monday, 2=Tuesday, ... 7=Sunday (return_type=2)
=WEEKDAY(A2, 3) → 0=Monday, 1=Tuesday, ... 6=Sunday
=TEXT(A2, "dddd") → "Friday" (day name as text — easier to display)
Use WEEKDAY with IF to flag weekends:
=IF(WEEKDAY(A2,2)>5, "Weekend", "Weekday")
→ If WEEKDAY > 5 (i.e., 6=Sat or 7=Sun), it's a Weekend
WEEKNUM — Week Number of Year
=WEEKNUM(A2) → week number (1–53)
=WEEKNUM(A2, 2) → week starts Monday (ISO standard)
=ISOWEEKNUM(A2) → ISO 8601 week number (Excel 2013+)
Practical Examples
Example 1: Employee Tenure Report
A = Join Date, B = End Date (or TODAY() for current employees)
C2 = =DATEDIF(A2, B2, "Y")&"y "&DATEDIF(A2, B2, "YM")&"m"
→ "3y 7m" (3 years, 7 months)
D2 = =IF(DATEDIF(A2, TODAY(), "Y") >= 5, "Senior", "Junior")
→ "Senior" if 5+ years experience
Example 2: Project Deadline Tracker
A = Project Name
B = Start Date
C = Deadline
D = Working Days Remaining
E = Status
D2 = =NETWORKDAYS(TODAY(), C2)
E2 = =IF(C2 < TODAY(), "Overdue", IF(D2 <= 5, "Due Soon", "On Track"))
-- Colour E column with conditional formatting: red for "Overdue", orange for "Due Soon"
Example 3: Invoice Aging Report
A = Invoice Date, B = Amount
C2 = =TODAY() - A2 → days outstanding
D2 = =IFS(C2<=30, "0-30 days",
C2<=60, "31-60 days",
C2<=90, "61-90 days",
TRUE, "90+ days")
Example 4: Monthly Sales Grouping
Transaction dates in column A, amounts in B.
-- Extract month-year for grouping:
C2 = =TEXT(A2, "mmm-yyyy") → "Jan-2026"
-- Or:
C2 = =DATE(YEAR(A2), MONTH(A2), 1) → first of that month (numeric, formattable)
-- Sum by month using SUMIF:
=SUMIF(C:C, "Jan-2026", B:B)
Example 5: Age Calculator from Date of Birth
B2 = Date of Birth
Age in years: =DATEDIF(B2, TODAY(), "Y")
Age in full: =DATEDIF(B2, TODAY(), "Y")&" years, "&DATEDIF(B2, TODAY(), "YM")&" months, "&DATEDIF(B2, TODAY(), "MD")&" days"
Comparison: Date Functions
| Function | Use |
|---|---|
TODAY() | Today's date (volatile) |
NOW() | Current date + time (volatile) |
DATE(y, m, d) | Build a date from parts |
YEAR/MONTH/DAY() | Extract parts from a date |
DATEDIF(s, e, "Y") | Years between two dates |
DAYS(end, start) | Total calendar days |
NETWORKDAYS(s, e) | Working days between dates |
WORKDAY(start, n) | Date after N working days |
EOMONTH(date, n) | End of month (n months offset) |
WEEKDAY(date) | Day of week as a number |
WEEKNUM(date) | Week number of the year |
Common Mistakes
1. Date arithmetic giving a decimal result
=B2 - A2 → result is 45 (days) but shows as "15-Feb-1900"
Fix: format the result cell as "Number" not "Date"
2. DATEDIF with reversed dates
=DATEDIF(TODAY(), A2, "Y") → #NUM! error (start > end)
=DATEDIF(A2, TODAY(), "Y") → correct
3. Dates entered as text
"27 June 2026" entered as text → date functions won't work
Fix: use Data → Text to Columns → Date format
Or: =DATEVALUE("27/06/2026") → converts text date to serial number
4. TODAY() making models volatile
For static snapshots (reports that shouldn't change after publishing):
=TODAY() → changes every day
Fix: Ctrl+; (semicolon) → inserts current date as a static value
Practice Exercises
- Calculate each employee's age in complete years from their date of birth.
- Find how many working days remain until a project deadline (use NETWORKDAYS with a holiday list).
- Given a list of invoice dates, classify each as "Current" (under 30 days), "Late" (30–60 days), or "Overdue" (over 60 days).
- Build a formula that returns the first Monday of any given month.
- Count how many transactions occurred on weekends using WEEKDAY and COUNTIF.
Summary
In this chapter you learned:
- Excel stores dates as serial numbers and times as decimal fractions
TODAY()/NOW()— get current date/time (volatile — recalculate on change)YEAR(),MONTH(),DAY()— extract parts;DATE(y, m, d)— build a dateDATEDIF(start, end, "Y"/"M"/"D")— calculate duration in years, months, or daysEOMONTH(date, n)— last day of the month n months awayNETWORKDAYS(start, end, holidays)— count working days;WORKDAY(start, n)— add working daysWEEKDAY(date)— day of week number;TEXT(date, "dddd")— day name- Arithmetic on dates:
end - start= days between - Format date result cells as Number to see day counts, not date values
Ctrl+;inserts a static date that won't change
Next up: Logical Functions — AND, OR, NOT, SWITCH, and advanced IF patterns.