Change format of a date field
 

I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.

Change format of a date field
 

suppose u have value in cell A1 then put this formula in B1

=IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1)
&"/"&MID(A1,2,2)&"/"&RIGHT(A1,4))


On Nov 12, 2:02*pm, Sageman wrote:
I have been given a csv file - on opening in excel the dates are in the
format 25051917, *5051949, 23121979 *etc.
I have been struggling to get them into the standard date format - in this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. *Any help appreciated.

Change format of a date field
 

Brilliant - works a treat - Thanks very much.

"muddan madhu" wrote:

suppose u have value in cell A1 then put this formula in B1

=IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1)
&"/"&MID(A1,2,2)&"/"&RIGHT(A1,4))


On Nov 12, 2:02 pm, Sageman wrote:
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.

Change format of a date field
 

Hi,

You can also try this. Highlight the range and go to Data Text to
columns. While on Delimited, click on next twice and then select Date in
Column Date format. Select DMY in the drop down and select any blank cell
in destination. Click on Finish. This should get the dates in the required
format. Please note that this will not give you the correct result for
5051949. To make this work, you will have to precede 5051949 with
05051949.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sageman" wrote in message
...
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.

Change format of a date field
 

A slightly different answer from me:

you can use:

=IF(LEN(A1)=8,DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1 ,2)),DATE(RIGHT(A1,4),MID(A1,2,2),LEFT(A1,1)))

Here if the DATE FORMAT that you derived from a CSV file and your EXCEL
program is different then just extracting date using left right and mid
functions can give errors (like date of mm/dd/yyyy type in place of
dd/mm/yyyy).

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Sageman" wrote:

Brilliant - works a treat - Thanks very much.

"muddan madhu" wrote:

suppose u have value in cell A1 then put this formula in B1

=IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1)
&"/"&MID(A1,2,2)&"/"&RIGHT(A1,4))


On Nov 12, 2:02 pm, Sageman wrote:
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.

Change format of a date field
 

This is a little more compact...

=TEXT(A1,"00\/00\/0000")

--
Rick (MVP - Excel)


"Sageman" wrote in message
...
Brilliant - works a treat - Thanks very much.

"muddan madhu" wrote:

suppose u have value in cell A1 then put this formula in B1

=IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1)
&"/"&MID(A1,2,2)&"/"&RIGHT(A1,4))


On Nov 12, 2:02 pm, Sageman wrote:
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in
this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.

Change format of a date field
 

Thanks guys for all your help - very useful and some ideas to use with other
problems!

"Ashish Mathur" wrote:

Hi,

You can also try this. Highlight the range and go to Data Text to
columns. While on Delimited, click on next twice and then select Date in
Column Date format. Select DMY in the drop down and select any blank cell
in destination. Click on Finish. This should get the dates in the required
format. Please note that this will not give you the correct result for
5051949. To make this work, you will have to precede 5051949 with
05051949.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sageman" wrote in message
...
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.

Change format of a date field
 

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sageman" wrote in message
...
Thanks guys for all your help - very useful and some ideas to use with
other
problems!

"Ashish Mathur" wrote:

Hi,

You can also try this. Highlight the range and go to Data Text to
columns. While on Delimited, click on next twice and then select Date in
Column Date format. Select DMY in the drop down and select any blank
cell
in destination. Click on Finish. This should get the dates in the
required
format. Please note that this will not give you the correct result for
5051949. To make this work, you will have to precede 5051949 with
05051949.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sageman" wrote in message
...
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc.
I have been struggling to get them into the standard date format - in
this
case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated.