Excel date formatting
Posted by Jen on January 31, 2002 10:52 AM
I am trying to format a report with dates from an AS400 system. The problem is, the format is yymmdd - no breaks and in the wrong order. I've been trying to convert it to mm/dd/yy without much success. I need it to be recognized by Excel as a date in order to do calculations with. Thanks in advance for any help you can offer.
Jen
Posted by Barrie Davidson on January 31, 2002 11:11 AM
Jen, you could convert that cell via a formula. Just one question - do you have a rule to determine what century the date refers to?
For example:
001231 = Dec 31, 2000
051231 = Dec 31, 2005
501231 = Dec 31, 1950
Regards,
BarrieBarrie Davidson
Posted by Jen on January 31, 2002 1:22 PM
I don't need to convert it to "December...", just get it in the right order. I'm not sure how to break the one cell up into three numbers (month, day and year). The year isn't a problem - all the dates are in 1900's.
Posted by Barrie Davidson on January 31, 2002 1:27 PM
Okay, assuming your date is in cell A1, try this formula:
=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
Regards,
BarrieBarrie Davidson
Posted by Jen on January 31, 2002 2:15 PM
It worked perfectly - thank you very much!
Posted by Jen on January 31, 2002 2:43 PM
btw - can you recommend a good book for Excel VB scripting - preferable one with lots of examples.
Posted by Barrie Davidson on January 31, 2002 2:49 PM
I bought one called VBA Unleashed (I think, I don't have it here with me). I found it to be quite helpful.
Regards,
BarrieBarrie Davidson
