Problem: Excel date formatting YYYY shows date as 1905. YYYY-MM dates show as 1905.
Article re-arranged, showing the final solution(s) at the top. Detailed explanations, below. Revisited and tightened again on 2023.10.
Consider this sheet, where:
Cell A3 contains a real date: 10/22/2023 21:58 and is formatted as a Date.
Then build separate cells for YYYY and MM (consider these as a sub-date)
Cell B3 is =Year(A3) = 2023
Cell C3 is = Month(A3) = 10 (month 10)
Goal: Display YYYY-MM (2023-10)
Goal: For a pivot report, or graph, where you want to show YYYY-MM, regardless of the day.
Article re-arranged, showing the final solution(s) at the top. Detailed explanations, below. Revisited and tightened again on 2023.10.
Consider this sheet, where:
Cell A3 contains a real date: 10/22/2023 21:58 and is formatted as a Date.
Then build separate cells for YYYY and MM (consider these as a sub-date)
Cell B3 is =Year(A3) = 2023
Cell C3 is = Month(A3) = 10 (month 10)
Goal: Display YYYY-MM (2023-10)
Goal: For a pivot report, or graph, where you want to show YYYY-MM, regardless of the day.
 |
| Click for Larger View. Green is good. |
Problem:
Assembling a YYYY-MM from "intermediate dates" will always cause problems because these "sub-dates" are not "dates" -- they just happen to look like dates.
Importantly, look at Cell C12, which has a simple formula:
=B3 (where B3 points to the subdate "2023")
but displays as "7/15/1905" !!!
C12 was improperly assembled from a "sub-date," using a formula =B3 *and* it was formatted as a date. Both are mistakes. (By default, Excel sees C12 and, by extension =B3, as a "date" and formats both cells as Dates.)
Solution: YYYY-MM as Text:
Cell C7 shows the proper way to build YYYY-MM:
Less than good solutions:
a) This formula correctly shows as YYYY-MM, but takes two intermediate cells (the Year, and Month cells). I am not fond of this because of the extra cells and extra formulas.
b) You could take the real dates and "format them" (with a custom formatting picture clause), forcing them to display as "YYYY-MM"
This kind-of-works -- it "looks right" but will not group and sort correctly in a pivot.
c) Assembling the date twice: This formula also works, where the cell is formatted as either Date or Text.
Assembling a YYYY-MM from "intermediate dates" will always cause problems because these "sub-dates" are not "dates" -- they just happen to look like dates.
Importantly, look at Cell C12, which has a simple formula:
=B3 (where B3 points to the subdate "2023")
but displays as "7/15/1905" !!!
C12 was improperly assembled from a "sub-date," using a formula =B3 *and* it was formatted as a date. Both are mistakes. (By default, Excel sees C12 and, by extension =B3, as a "date" and formats both cells as Dates.)
Rule: Do not use intermediate or sub-dates for any date-calculation or format. Always point to a full-date cell. If you try to assemble the constituent parts as "YYYY" + "MM", it will fail -- neither the "2023" or the "10" are dates and will display as 1905 in a date-formatted cell. Reason below.
Solution: YYYY-MM as Text:
Cell C7 shows the proper way to build YYYY-MM:
- Assemble with concatenated =Text formulas, and always point to the "real date" in Cell A3.
Do not point to a sub-date; do not point to intermediate cells, such as =Year()
- assemble YYYY-MM (in cell C7) using this concatenated text formula:
=Text(A3,"YYYY") & "-" & Text(A3,"MM")
where cell A3 is a "real" date
(Gloriously, it does not matter how A3 is displayed or formatted).
- Format the YYYY-MM (cell C7) cell as either "general", "Date", or "Text";
I favor "Text". Since this is a "text" creature, it sorts and groups the way you want.
Less than good solutions:
a) This formula correctly shows as YYYY-MM, but takes two intermediate cells (the Year, and Month cells). I am not fond of this because of the extra cells and extra formulas.
=B3 & "-" & C3
b) You could take the real dates and "format them" (with a custom formatting picture clause), forcing them to display as "YYYY-MM"
This kind-of-works -- it "looks right" but will not group and sort correctly in a pivot.
=A3 (formatted with a custom date of "YYYY-MM")
Custom formattings may not show the day but they still remember their "days" and will sort as-such. Usually, when "YYYY-MM-ing", you want all that month's records to be combined into one group. Formatted dates are too granular, where each day (1-30) gets its own grouping in pivot tables or other control-break reports.
 |
c) Assembling the date twice: This formula also works, where the cell is formatted as either Date or Text.
=Year(A3) & "-" & Month(A3)
My complaint is under the hood. Two simultaneous dates are being
tracked, and both go through a date-format and conversion. There are
now three dates: One in cell A3, and two in the formula's cell. This
is not as efficient as it could be. Why have all this overhead when your goal is probably a text-version anyway?
Solution: YYYY (only) as Text:
Usually, when you want a "YYYY" -- you want the text representation of the date and you want it to break away from its "date-ness". Build the concatenated strings using the "real" date cell (regardless of how that cell is formatted or displayed):
- Point =Text() to real date (Cell A3), never to a sub-date
- Do not point to intermediate cells, such as =Year()
- Generally, format the YYYY cell as "General" or better as "Text" (no need to be *as date*)
- (As an aside, the original "real" date (A3) can be formatted in any way that you'd like)
Repeated:
For YYYY-MM, as illustrated cell C7, C8
=Text(A3, "YYYY" ) & "-" & Text(A3, "MM")
Other Discussions:
Assembling from intermediate sub-dates could work, but it requires two intermediate cells and a more complicated formula.
The dates could also be "compressed" into a common-day using this formula, shifting each date to the first of the month. With drawbacks, these dates would pivot and sort correctly:
=Date(B3, C3, 1) (on each date) or
=Date(Year(A3, Month(A3), 1)
where
B3 is an intermediate cell, "YYYY"
C3 is the intermediate month cell "MM"
"1" is a hard-coded first day of the month -- forcing all to day-one.
You still end up with a "dated" date, where the "day" (1-30, now all as "1") is still hiding there.
This assembles a new "real" date, converting all days, 1-30, to "1" -- which would make the "group" correctly, all grouped by the first of the month. But this is extra work, usually requiring a whole new column because each date needs to be converted. And you still have the problem of displaying as YYYY-MM, which means another stinky custom format. I am not fond of this either.
|
| Click for larger view |
Why 1905?
Consider a valid date in A3
and its sub-dates, Year and Month, in cells B3 and C3:
- A3 is a real date (e.g. =now() or any other date). The date must be a real date and not a text-string date. It can be formatted with different date-styles.
- In cells B and C, the real date is sub-dated into the Year and Month, where:
B3 displays Year using =Year(A3)
C3 displays the Month, using =Month(A3)
These may be useful, but not for the formulas in this article.
Now build a formula in any blank cell.
Format this cell as "Date" (not General, not Text)
=B3 (see cell C12)
Notice the result 1905.
Excel stores all dates as a sequence or serial number, which represents the number of days since (Jan-1 1900) or (Jan-1, 1905 (Macintosh)) See this Microsoft article.
The 'real' date 10/22/2023 has a serial number of "45221.95" (to see the serial number, format the cell as "Number").
where 45,000 = 124 years since 1900 (roughly 365 * 124 with leap year considerations, etc.), and .95 represents the fractional part of the day - e.g. the time, where 95 is approaching 23:00 hrs.
When the year (YYYY) is the only part of the date calculation, years like 2012, 2021, 2023, etc., will always be 1905 because the serial number ('2023') is well below the current date's 45,000 number:
In other words, converting a cell to =Year(), with a result of "2014", tells the computer it is two-thousand fourteen days since 01-01-1900 -- which happens to be sometime in 1905. I suppose in the future the year could shift to 1906, depending on an obscure base-date options and leap years.
Likewise a simple, =B3 + C3
(2023 + 10) equals 2033
and in a date-formatted cell, it is still 1905. "2033" is a light-year away from 45,000.
Your comments:
I would like to know if you quickly found your answer. This is a popular article. I would like to fine-tune it. Leave a comment.
Other Keyliner Excel Articles:
How to use Excel VLookup
Return First Word, Last Word, SuperTrim
Parsing City State Zip
Writing your own User Defined Functions in Excel
