Archive of Mr Excel Message Board

Back to Lotus archive index
Back to archive home

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!

Re: Date format

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.