Solved

# Changing numbers to dates in excel

Posted on 2011-03-17
268 Views
Hi,
I have a number representing an expiry date that is sent from a supplier in a CSV file.  For example 20130228 represents and expiry date of 28/02/2013.  When I try and format this to a date in excel all I get is a load of hash marks (see screenshot)
How do I get this data into an excel date format?
0
Question by:ianlee1
• 2
• 2

LVL 24

Expert Comment

Insert a new column next to that column (column J) and insert the following formula and copy down:

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

And then Copy this new column (J) and Past Special and select "Values" at Column I.
0

LVL 41

Accepted Solution

dlmille earned 500 total points
its a text string.

Try this formula

=Date(left(I2,4),mid(I2,5,2),right(I2,2)) to convert the string in I2 to a date.  You might insert a column at J and put this formula in, then copy down.

Dave
0

LVL 50

Expert Comment

Try using "Text to columns" functionality to convert without an extra column.

Select column of dates then use

Data > Text to columns > Next > Next > at step 3 choose "Date" option under "column adta format" and then "YMD" from the dropdown > OK

Now you can format as required

regards, barry
0

LVL 50

Expert Comment

sorry that should be "column data format".....
0

LVL 24

Expert Comment

Nice one barry
0

## Featured Post

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…