reformat cells for date
 

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

reformat cells for date
 

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

"Wackyracer" wrote:

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

reformat cells for date
 

Unfortunately that does not work 10408 (01/04/08) comes out as 29/06/28.

"Dennis" wrote:

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

"Wackyracer" wrote:

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

reformat cells for date
 

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.
--
David Biddulph

"Wackyracer" wrote in message
...
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

reformat cells for date
 

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.

Wackyracer wrote:

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

--

Dave Peterson

reformat cells for date
 

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)

"Wackyracer" wrote:

Unfortunately that does not work 10408 (01/04/08) comes out as 29/06/28.

"Dennis" wrote:

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

"Wackyracer" wrote:

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

reformat cells for date
 

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

"Dave Peterson" wrote in message
...
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.

Wackyracer wrote:

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

--

Dave Peterson

reformat cells for date
 

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



David Biddulph wrote:

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

"Dave Peterson" wrote in message
...
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.

Wackyracer wrote:

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

--

Dave Peterson

--

Dave Peterson

reformat cells for date
 

Perhaps it's dependent on Windows Regional Options?
--
David Biddulph

"Dave Peterson" wrote in message
...
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



David Biddulph wrote:

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

"Dave Peterson" wrote in message
...
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.

Wackyracer wrote:

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

--

Dave Peterson

--

Dave Peterson

reformat cells for date
 

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 Biddulph wrote:

Perhaps it's dependent on Windows Regional Options?
--
David Biddulph

"Dave Peterson" wrote in message
...
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



David Biddulph wrote:

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

"Dave Peterson" wrote in message
...
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.

Wackyracer wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

reformat cells for date
 

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

On Wed, 2 Apr 2008 20:51:21 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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

reformat cells for date
 

02/04/2008
in other words
DD/MM/YYYY
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 2 Apr 2008 20:51:21 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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

reformat cells for date
 

Mine are mm/dd/yyyy so that's the difference.


Gord

On Wed, 2 Apr 2008 22:58:04 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

02/04/2008
in other words
DD/MM/YYYY