Extract Year, Month or Day from a Date
Two questions arrive at this hub with opposite answers. A month you calculate with must come back as a number; a month you only read should stay a date. The functions produce the numbers, and they can do it because a date is stored as a serial count of days rather than as text. A custom number format produces the reading, and it changes the display only, so the column still sorts chronologically instead of alphabetically from April. TEXT sits between the two and trades the sortable value for characters — worth it only when the characters themselves are the output, as a label, a key, or something to join.
The formula
=YEAR(A2)2026=MONTH(A2)8 — a number, never a name=DAY(A2)14=TEXT(A2,"mmmm")August (text, so it sorts alphabetically)=TEXT(A2,"mmm yyyy")Aug 2026=EOMONTH(A2,0)the last day of A2's month=EOMONTH(A2,-1)+1the first day of A2's month=DAY(EOMONTH(A2,0))how many days that month has=WEEKNUM(A2,2)week number, weeks starting Monday=ISNUMBER(A2)TRUE if A2 is a real date, FALSE if it is textA worked example
A2 shows 14 August 2026. Switch that cell to the General format and it reads 46248 — that integer is the thing YEAR, MONTH and DAY are actually reading, and the date you see is only a number format painted over it.
=MONTH(A2)
8, right-aligned like any other number. To read "August" instead, use =TEXT(A2,"mmmm"), which returns text. To make A2 itself display as August without changing what it holds, apply the custom number format mmmm to the cell — the serial 46248 stays underneath, so the column still sorts chronologically instead of alphabetically from April.
Which one do I need?
| If you want to… | Use |
|---|---|
| The year, month or day as a number, to feed into another formula | =YEAR(A2), =MONTH(A2), =DAY(A2) |
| The month spelled out — "August" or "Aug" | =TEXT(A2,"mmmm") or =TEXT(A2,"mmm"). The result is text: it will sort April, August, December, and SUM will ignore it. |
| Month and year together for grouping, and the column must still sort by date | Leave the value alone and apply the custom number format mmm yyyy. Reach for =TEXT() only when you need the characters themselves — as a label, a key, or a concatenation. |
| Hiding the year from the display without losing it | A formatting problem, not an extraction one — the custom format dd mmm shows day and month while the cell still holds a complete date |
| The first or last day of the month a date falls in | =EOMONTH(A2,-1)+1 for the first, =EOMONTH(A2,0) for the last |
| The date carries a time you want gone before extracting anything | =INT(A2) discards the fractional part, leaving midnight on the same day |
| MONTH returns #VALUE!, or the values sit against the left edge of their cells | The column is text. Select it and run Data > Text to Columns > Next > Next > Date, choosing the D/M/Y order the text actually uses, then Finish. |
Frequently asked questions
Why does =MONTH(A2) return 8 instead of August?
By design. MONTH exists to produce a number that other formulas can compare, sort and calculate with — =MONTH(A2)=8 is a test you can write, "August"=8 is not. Names come from elsewhere: =TEXT(A2,"mmmm") if you need the word as a value, or a custom number format if you only need the cell to look like the word.
How can I tell whether a cell holds a real date or text that only looks like one?
=ISNUMBER(A2) settles it: TRUE means Excel is holding a serial number it can do arithmetic on, FALSE means characters. Two quicker checks agree with it — real dates right-align in an unformatted cell while text left-aligns, and switching the cell to the General format turns a real date into a five-digit number but leaves text exactly as it was.
Why do YEAR and MONTH return #VALUE! on a column imported from a CSV?
Imported dates arrive as text, and often in a day/month order that does not match your regional settings, so Excel refuses to guess. Select the column, choose Data > Text to Columns, click Next twice, pick Date and set the order to match what the text actually says — DMY for 03/09/2026 meaning 3 September — then Finish. The column converts in place and the date functions start working.
Does extracting the year change the original date?
No. YEAR, MONTH and DAY read the cell they point at and write their answer into their own cell; the source is untouched and keeps its full value. Only a number format or an overtyped entry changes what a date cell contains, which is why formatting is the right tool when the goal is how the date looks rather than a number to calculate with.
New guides and tools, once a month
DE + EN · double opt-in · no spam