Converting Imported Data to a Date Format

joe6638

New Member
Joined
Apr 30, 2002
Messages
1
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??
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
number 20050812 to date

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
 
Upvote 0
Similar problem - but solution doesn't work

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!!
 
Upvote 0
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.
 
Upvote 0
Hi Richard,

This works perfectly! It's exactly the result that I wanted. Thank you so much for helping me with this.
 
Upvote 0
work when importing data in 10/29/07 - 11/4/07 format?

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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top