Convert Number (YYMMDD) to the actual date indicated.

[robione]

Down loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?

 

[njimack]

=date(2000+left(a1,2),mid(a1,3,2)+0,day(right(a1,2)+0))

 

[robione]

Works like a charm! Thanks!

 

[robione]

Works like a charm! Thanks!

Is this the same: =date(2000+left(a1,2),mid(a1,3,2),right(a1,2)) ??

 

[Rick Rothstein]

Works like a charm! Thanks!

If that works correctly for you (all years are considered to be in the 2000's), then this shorter, single function call formula should also work for you...

=0+TEXT(A1,"2000\/00\/00")

Edit Note: If you need to be able to preserve dates before the year 2000, you can use this...

=0+TEXT(A1,19+(LEFT(A1,2)<30)&"00\/00\/00")

Just change the highlighted number to the 'breakpoint" year between the centuries.

 

[njimack]

Is this the same: =date(2000+left(a1,2),mid(a1,3,2),right(a1,2)) ??

No. If you don't add 0 to your MID and RIGHT functions, you're returning a text value, rather than a number. Your best bet is to go with Rick's suggestion, which is much neater than mine.