Chapter 07 · basic · 5 min
Dates I: the serial-number model
A date in Excel isn't a special type — it's an ordinary number, formatted to look like a date. 2026-03-14 is stored as some integer counting days from a fixed starting point, and every date function is really just integer arithmetic with a calendar-aware label on top. This is the single idea that makes the rest of Excel's date functions predictable instead of memorized.
We'll work with one Traveloka check-in date, 2026-03-14, in A1 throughout this chapter.
The grid
A single check-in date in A1: 2026-03-14.
A date is a number you can add to
Because a date is stored as a count of days, adding 1 to it means "one day later" — no special date-arithmetic function required:
=A1+1A1 is 2026-03-14. What does adding 1 day land on?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2026-03-14 | =A1+1 |
YEAR, MONTH: reading pieces back out
YEAR, MONTH and DAY pull one component out of the underlying serial number:
=MONTH(A1)A1 is 2026-03-14. What number does MONTH return?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2026-03-14 | =MONTH(A1) |
EOMONTH + TEXT: the last day of the month, readable
EOMONTH(date, months_to_shift) jumps to the last day of a month — 0 means "this month", -1 means "last month", 1 means "next month". On its own it returns another raw serial number, so pair it with TEXT to get something readable:
=TEXT(EOMONTH(A1,0),"yyyy-mm-dd")Last day of the check-in month
In D1, return the last day of A1's month as a readable yyyy-mm-dd string.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2026-03-14 |
The trap: raw serials and case-sensitive format codes
Drop the TEXT wrapper and EOMONTH(A1,0) alone doesn't show 2026-03-31 — it shows a plain integer like 46112, the day count itself, with no calendar formatting applied automatically the way DATE() gets. Every date-returning function except DATE() needs an explicit TEXT wrap to display sensibly.
And the format code itself is case-sensitive: "yyyy-mm-dd" works, but "YYYY-MM-DD" evaluates to #VALUE! rather than silently working anyway. Always lowercase.