Thursday, August 14, 2014

Solution: Excel YYYY Dates show as 1905

Problem: Excel date formatting YYYY shows date as 1905.  YYYY-MM dates show as 1905.

I have re-arranged this article, showing the final solution(s) at the top.  More detailed explanations follow.

Assume Cell A3 contains the date:  08/13/2014 21:21 and is formatted as Date Value.
Notice cell D3, which is supposed to show only they year, shows as 1905 when it clearly should be 2014, 

D3 was improperly assembled from a "sub-date," using a formula =B3 *and* it was formatted as a date-cell.  This is a mistake. 

Do not use intermediate or sub-dates for any date-calculation or format.  Always point to a full-date cell.  

Most people are trying to format a cell as 2013-08  (YYYY-MM) and they are trying to assemble the constituent parts "YYYY" + "MM" to get the result.  This will fail.  

YYYY Solution as Text:

In the last cell, D3, where you want a simple "YYYY" result, use this text-formula, where cell A3 is a date-cell.  See the next section for a YYYY-MM example.

  • Always point =Text() to real date  (Cell A3) - never to a sub-date; 
  • Format the YYYY cell as "general" or "text" -- *not as date* 
  • Do not point to intermediate cells, such as =Year()
  • A3 can be formatted in any date-format
In the top example, cells B3 (YYYY) and C3 (MM)
are not needed for intermediate =Text(  ) work
-- using them will lead you astray.

* By doing it, all YEARS will sort correctly,

YYYY-MM Solution as Text:

If you want cell D3 to show YYYY-MM, do the following:
  •  Always point =Text() formulas to real date  (Cell A3), never to a sub-date;
    Do not point to intermediate cells, such as =Year()
  • In cell D3, where you want the answer, assemble the YYYY-MM formula:


    where cell A3 is a real date-cell (regardless of how formatted).
  • Format the YYYY-MM column as "general" or "text"; *not as date* 

Other Possible Solutions:

Using intermediate sub-dates will work with these other formulas (see first two green), but involves another cell to calculate the intermediate values. 
Click for larger view

Note cell D6 forces the first day of the month ( ,1), then uses Excel's cell formatting to show only YYYY-MM.  This preserves the "date-ness" of the cell, but it still required sub-dates.

Click for larger view

How does 1905 happen?
Imagine a pivot table where the dates need to be categorized by Year-Month.

For example, if today's date was 2014.08.13, and you needed a column header displayed as "2014-08".  The trouble is the formula displays the year as 1905.

For example:
Consider the following, where a valid date in Column A (August, 2014), is broken down into two constituent parts:

  • A real date (e.g. =now() or any other date) is in cell A3.  The date must be a real date and not a text-string date.  If you can format it as different date-styles, it is a real date.
  • To illustrate, the year and the month are shown as separate "sub-date" formulas, where

    cell B3 displays the date as a Year using =Year()and
    C3 displays the Month, using =Month()
    These may be useful, but not for the formulas in this article.
  • In the last cell, D3, where you want the answer, attempt to assemble a new date (e.g. "2014-08") by using the intermediate date values "2014" and "08/8", as in
    "2014" & "-" & "08"
    ....  or

    =Text(B3, "YYYY") & "-" & Text(C3, "MM"))      shows as 1905-01
  • Formatting D3 as "Custom Date - YYYY-MM," shows the year as "1905-01". 
How to properly build YYYY-MM

For this article, the goal is to build a YYYY-MM date. 
(Update: See illustration above for other possible formulas)

Intermediate Year and Month columns, assembled as YYYY-MM, will fail with a 1905 date
(see the red formula, illustrated below):

Both of these fail:

=Text(B3, "YYYY") 

The key:
Do *not* point date calculations at sub-date fields (the red-squared formula).
Use the original (true) date for the assembly (see the blue formula). 
Format the formula with either General or Text, do not use a Date format

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

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
  • 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 (their granular dates and times) from the original data and will 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 example, the date used in the examples above has a decimal number "41864.92", where the fraction (".92") represents a fractional-part of a day -- e.g. the Time.  That is, 2014 is 114 Years * 365 = 41,000.

When the year is the only part of the date calculation, years like 2012, 2013, 2016, 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 fourteen 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

No comments:

Post a Comment

Comments are moderated and published upon review.