Why does the following formula return 05(1905) instead of 13(2013)? =TEXT(YEAR(K7),"YY")

(K7) = 5/5/13

05

You do not need the YEAR() function. simply do: =TEXT(K7,"YY")

MIKE

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

Thank you both very much for the fix and the explanation!!

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...

Ask Your Question

Weekly Poll