Date format

Posted by Clara Denton on October 08, 2001 6:34 AM

I am using a file imported from our AS400 system and the dates are in the format of the following:

11172001 (November 17, 2001)
6012001 (June 1, 2001)

I have tried to change the format by using the date format and customizing, but nothing I try works. Any idea on how to get these dates to read correctly as dates with slashes?
Thanks!

Posted by Juan Pablo on October 08, 2001 6:55 AM

Assuming your "dates" are in A2:A20, copy this formula in B2:B20

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

Juan Pablo