reformat cells for date

W

Wackyracer

I have a csv file that contains a column with dates in it. Unfortunately the
column has the information entered as DMMYY for 5/12/08 or DDMMYY for
20/12/08. So for days 1st to 9th there is no 0 at the front end.

I need to be able to change this coloumn so it reads DD/MM/YYYY so I can
import it into another programme. Can any help?

Many thanks
 
D

Dennis

Select all the cells and then do a Format - Cells
Pick the custom format and choose DD/MM/YYYY
 
D

David Biddulph

One option =DATE(2000+RIGHT(A1,2),MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))

Another option, if your Windows Regional Settings specify d/m/y, is
=--TEXT(A1,"00\/00\/00")

Another thing worth checking is whether your CSV file actually has that
leading zero missing. If the leading zero is there but the CSV has been
opened with Excel, by default Excel would treat it as a number and not
display the leading zero. If so, read the CSV data into Excel using the Text
Import Wizard and specify the column as text.
 
D

Dave Peterson

Select the column with the "dates"
Data|Text to columns (in xl2003 menus)
Fixed width, but remove any lines that excel guessed.
Choose Date and dmy
and drop the data right back into its original location.

Then format that column the way you like.
 
D

Dennis

So the cells are actually numbers or text that represent the date ?
Assuming it is only the the days that are the problem and months 1-9 are
01-09 then you can create a new column with the formula
=RIGHT("0" & A1,6)
 
D

David Biddulph

I had wondered about that method, but for me that doesn't work with 51208,
though it does work with 201208.
 
D

Dave Peterson

It worked for me in xl2003--as long as the year was only two digits.

If I used 4 digit years, then it didn't work right.

This:
51208
61208
71208
81208
91208
101208
111208
121208

converted to:
05/12/2008
06/12/2008
07/12/2008
08/12/2008
09/12/2008
10/12/2008
11/12/2008
12/12/2008

And this:

50308
60308
70308
80308
90308
100308
110308
120308

Converted to:

05/03/2008
06/03/2008
07/03/2008
08/03/2008
09/03/2008
10/03/2008
11/03/2008
12/03/2008
 
D

Dave Peterson

I don't know. My short date format is: mm/dd/yyyy (4 digit year)

I've had trouble with this techique before, but I closed excel and reopened it
and it continued to work.

When I've had trouble, I'll use the helper column and a formula like:
=text(a1,"000000")
convert to values
and use data|text to columns.

David said:
Perhaps it's dependent on Windows Regional Options?
Click to expand...
 
G

Gord Dibben

David

51208 becomes May 12, 2008 when I run it through T to C and select MDY as format

What are your short date settings?


Gord Dibben MS Excel MVP
 
Top