Converting Imported Data to a Date Format

[joe6638]

I am importing data from my AS400 in Excel, the dates are coming into the spreadsheet as numerics. How do I convert these numbers into a date format? i.e. 3202002 should be 3/20/2002, how do I get the number to that format??

[Asala42]

Hey there,

There's likely a sleeker formula but heck try this:

=(LEFT(A2,LEN(A2)-6)&"/"&LEFT(RIGHT(A2,6),2)&"/"&RIGHT(A2,4))+0

You'll likely want to format the cell with a date format (otherwise you'll get the serial number behind the actual date).

Hope that helps out,
Adam

[almouchie]

urgent please
what can change if the text is th reverse as in the year, month then date

how can I change the formula given above
(LEFT(A2,LEN(A2)-6)&"/"&LEFT(RIGHT(A2,6),2)&"/"&RIGHT(A2,4))+0

to get the date correct
thanks

[Richard Schollar]

Hi

Try using:

=TEXT(A1,"0000-00-00")+0

[almouchie]

thanksa lot richard
it worked fine

[HilaryF]

I have a similar situation -- an inherited Excel spreadsheet with a column of "dates" in numeric format, that appear as yyyymmdd: e.g. 20040212

But when I try the suggested code above:
=TEXT(A1,"0000-00-00")+0

I just get a #VALUE! error. When I take off the final "+0" I get the hyphens added in, but I still can't convert this to an Excel date. If I go into Excel and try to change the format (to Date MM-DD-YYYY), nothing happens. Even if I copy the cell and past the value into a new cell, this doesn't work either. Can anyone help? Thanks!!

[Richard Schollar]

Hi Hilary

Give the following a shot:

=DATE(LEFT(A1&"",4)+0,mid(A1&"",5,2)+0,RIGHT(A1&"",2)+0)

altho I am surprised the Text formula didn't work.

[HilaryF]

Hi Richard,

This works perfectly! It's exactly the result that I wanted. Thank you so much for helping me with this.

[artmacc2]

Will this work for dates that are already formated and imported as:

10/29/07 - 11/4/07 data1 data2 data3 data4
11/5/07 - 11/11/07 data1 data2 data3 data4

Currently when I use this date format and create a pivot table, the dates are never in order even after sorting them.

[Richard Schollar]

Hi Armacc2 & Welcome to the Board!

You effectively have two dates there - do you want to preserve hem both, or will just the first do? I would try the TextToColumns route, and specify the "-" (w/o quotes) as the delimiter and do a MDY date format import (I think this has already be mentioned towards the top of this thread) - you can select not to import the second date if required.