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 five Traveloka check-in dates throughout this chapter, picked so that each one sits somewhere awkward: the last day of a month, the last week of a year, a February in a leap year.
The grid
Column A holds five check-in dates as real dates. Columns B to D are where the formulas go.
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, and no special handling when the answer crosses into a new month:
=A3+1A3 is the last day of January. Excel doesn't need to know that; it adds 1 to a day count and then formats the new count as a date.
A3 is 2026-01-31. What does adding 1 day land on?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Check-in | Next day | Month | Month end |
| 2 | 2026-03-14 | |||
| 3 | 2026-01-31 | =A3+1 | ||
| 4 | 2028-02-15 | |||
| 5 | 2026-12-28 | |||
| 6 | 2026-04-30 |
YEAR, MONTH: reading pieces back out
YEAR, MONTH and DAY pull one component out of the underlying serial number:
=MONTH(A2)The answer is a plain number, not a month name — which is exactly what you want when you're going to group or compare by it later.
A2 is 2026-03-14. What number does MONTH return?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Check-in | Next day | Month | Month end |
| 2 | 2026-03-14 | =MONTH(A2) | ||
| 3 | 2026-01-31 | |||
| 4 | 2028-02-15 | |||
| 5 | 2026-12-28 | |||
| 6 | 2026-04-30 |
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(A2,0),"yyyy-mm-dd")This is the formula to fill down, because the column is full of months that end on different days: 31 for March, 30 for April, and 29 for row 4 — February 2028 is a leap year. Nobody should have to remember that; EOMONTH does.
Last day of each check-in month
In D2, return the last day of A2's month as a readable yyyy-mm-dd string. Then fill down and check February 2028 in row 4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Check-in | Next day | Month | Month end |
| 2 | 2026-03-14 | |||
| 3 | 2026-01-31 | |||
| 4 | 2028-02-15 | |||
| 5 | 2026-12-28 | |||
| 6 | 2026-04-30 |
The trap: raw serials and case-sensitive format codes
Drop the TEXT wrapper and EOMONTH(A2,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.