Solution: Excel YYYY Dates show as 1905

Problem: Excel date formatting YYYY shows date as 1905.

Scenario:
For a pivot table, the dates needed to be categorized by Year-Month.  For example, if today's date was 2014.08.03, they needed a date column header displayed as "2014-08".  The trouble was the formulas would display the year as 1905 when clearly the date was 2014.

For example:
Here is a valid date with a formula showing the year and another showing the month.  Both want to be assembled, giving 2014-08.

 where:

• A real date (e.g. =now() or any other date) is in cell A3
   
• Just to show the year and the month as separate entities,
  cell B3 displays the date as a Year using =Year()
  and C3 displays the Month, using =Month()
   
• In the last cell, D3, attempt to assemble a new date (e.g. "2014-08") by using the separate (intermediate) date values "2014" and "08/8", as in "2014" & "-" & "08".... 
  
  or simply point the formula to the year (e.g. =B3 -- '=2014') - and then apply a date format to that last cell, you will get "1905".
   

Important Note: In the illustration above, cell D3 is formatted as "Date".
 
How to build YYYY-MM

For this article, the goal is to build a YYYY-MM date.
If using the intermediate Year and Month columns for the YYYY-MM assembly, it will fail with a 1905 date (see red formula):  

=Text(B3, "YYYY")  or
=Text(B3,"YYYY")&"-"&Text(C3,"MM")

The key: Do not point date calculations to sub-date fields (the red formula).

Use the original (true) date for the assembly (see blue formula).  Format the resulting formula with either General or Text, do not use a Date format. 

=Text(A3, "YYYY") & "-" & Text(A3, "MM")


YYYY-MM Solution:

•  Always point =Text() formulas to real date  (Cell A3);
  Do not point to intermediate cells, such as =Year()
   
• Assemble a YYYY-MM formula like this:
  
  =Text(A3,"YYYY")&"-"&Text(A3,"MM")
  
  where cell A3 is a real date-cell (regardless of how formatted).
   
• Format the YYYY-MM column (D) as "general" or "text"; not as date
   
• In the example above, cells B3 and C3 are not needed for any intermediate work but many people start with this idea and it will lead them astray

YYYY Solution:

If you wanted just the year, as a Text field, use this formula:

=Text(A3,"YYYY")



Alternate: Using a Custom Format Picture Clause:

Alternately, ignoring everything above, format the cell with custom-date format.  This preserves the "date-ness" of the data -- but has the drawback that the entire date, including the day and time survive, even though the 'displayed format' only shows Year-Month:

Click for larger view

where:

• Highlight one or more cells.  Other-mouse-click, choose "Format Cells"
• From the Category, choose "Custom"
• In the "type" field (illustrated above in black highlight), type "YYYY-MM" (no quotes)

In the case of a Pivot table, this may not work for you -- the Pivot will still see all the individual dates and times from the original data and may not group them properly, still seeing the days, not the months.



Why 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 for details.  For example, the date used in the examples above has a decimal number "41864.92", where the fraction represents a fractional-part of a day -- e.g. the Time.  That is, 2014 is 114 Years * 365 = 41,000.

Years like 2012, 13, 14, 15, etc., will always be 1905 because the serial number ('2014') is well below the current date's 41,000 number:

In other words, when you convert a cell to =Year(), with a result of "2014", you are saying two-thousand days since 01-01-1900 -- which happens to be sometime in 1905.  The year can vary, depending on an obscure base-date option in Excel and if Macintosh; I do not have details on these, but I would guess 1909.


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