Solved Why is Excel returning 05(1905), instead of 13(2013)?

June 29, 2018 at 16:36:51
Specs: Windows 10
Why does the following formula return 05(1905) instead of 13(2013)?


(K7) = 5/5/13


See More: Why is Excel returning 05(1905), instead of 13(2013)?

June 29, 2018 at 18:03:58
✔ Best Answer
You do not need the YEAR() function. simply do:



Report •

June 29, 2018 at 19:19:53
As to the "why" portion of your question...

It's because of how Excel stores Dates internally. Dates are stored as integer numbers, with Day 0 being 1/0/1900, Day 1 being 1/1/1900, etc. Every Date is represented by a "serial number" that indicates the number of days since 1/0/1900.

If K7 contains 5/5/2013, YEAR(K7) is evaluated (internally) as YEAR(41399) because 5/5/2013 is 41,399 days since 1/0/1900. (The Formula Evaluator will show you that.)

TEXT(YEAR(K7),"YY") is actually TEXT(2013,"YY").

2013 is seen as the serial number representation of a date. Day 2013 is 7/5/1905, therefore the TEXT function returns 05 when "YY" is used, since "05" is the year of Excel Day 2,103.

BTW...Times are presented by the decimal portion of the serial number. e.g.

5/5/2013 12:00PM is 41399.5 (Noon is half a day)

5/5/2013 9:45:34AM is 41399.4066435185 because that Date/Time is that many days, and a portion thereof, since 12:00AM on 1/0/1900.

I hope that helps. ;-)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Report •

June 30, 2018 at 16:21:03
Thank you both very much for the fix and the explanation!!

Report •

Related Solutions

July 1, 2018 at 11:41:08
I wonder if Rodenberry (Star Trek) borrowed that (Excel) system of dating (with his own embellishents added) for the way Star Trek describes a given date...


- retires to patio for ice cream...

Report •

Ask Question