How do I change dates input as 20080426 to date format in Excel?

  • Thread starter Thread starter Judy CS
  • Start date Start date
J

Judy CS

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.
 
hi
if the dates all have the same number of characters, you might be able to
use this in a helper column added next to the data.

=MID(A2,5,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4)

the above assume data is in the A column. adjust if needed.
you can copy the formula and paste special values to turn them into dates
delete the as400 dates if needed.

regards
FSt1
 
Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish.


Gord Dibben MS Excel MVP
 
Dear FSt1

Yes, the input is all the same length of characters and structure. Based on
that, I figured there HAD to be a way of putting in the forward slahes for
date recognition.

So your suggestion worked great. Thanks for the assist. I'll be keeping
this for future reference and can modify if as needed.

This was a great help.
Thanks
Judy CS
 
Dear Faraz

Thanks for the input. This worked too. I now have two solutions for future
reference. I'll be keeping this to refer to.

Thanks
Judy CS
 
A bit late to this thread...but...

try this:

B1: =--TEXT(A1,"0000-00-00")

(format B1 as a date)

Is that something you can work with?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Back
Top