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

[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.

 

[FSt1]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[FARAZ QURESHI]

A1 = YYYYMMDD

Then

=date(left(A1,4),left(right(a1,4),2,right(a1,2))

 

[FARAZ QURESHI]

Sorry,

Try:

=date(left(A1,4),left(right(a1,4),2),right(a1,2))

 

[Judy CS]

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

 

[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

 

[Ron Coderre]

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)