# 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)?=TEXT(YEAR(K7),"YY")(K7) = 5/5/1305

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

#1
June 29, 2018 at 18:03:58
 You do not need the YEAR() function. simply do:=TEXT(K7,"YY")MIKEhttp://www.skeptic.com/

Report •

#2
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. ;-)message edited by DerbyDad03

Report •

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

Report •

Related Solutions

#4
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...mmm- retires to patio for ice cream...

Report •