That crazy IT dept. . .

[Circlewag]

My IT dept sent me a table with a column of dates entered as either 5 or 6 characters, (ex. 101309 = Oct 13, 2009 and 90409 = Sept 4, 2009). Excel NOT happy to convert to a date format. Is there a formula I can use to convert these to a conventional date format?

 

[schielrn]

Welcome to the Board!

Maybe try selecting the column and going to data-->text to columns. Click Next twice, select the radio button for Date and make sure the drop down is MDY and then click finish.

Hope that helps.

 

[c_m_s_jr]

my it dept sent me a table with a column of dates entered as either 5 or 6 characters, (ex. 101309 = oct 13, 2009 and 90409 = sept 4, 2009). Excel not happy to convert to a date format. Is there a formula i can use to convert these to a conventional date format?

try:
=if(len(a2)=6,date("20"&right(a2,2),left(a2,2),mid(a2,3,2)),date("20"&right(a2,2),left(a2,1),mid(a2,2,2)))

 

[Circlewag]

I can stop scratching my head now. . Thanks all. . these solutions worked beautifully

 

[Richard Schollar]

Hi

If your regional settings are MDY then you can use:

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

as well.