Change format of a date field

[Sageman]

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.

 

[muddan madhu]

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))

 

[Sageman]

Brilliant - works a treat - Thanks very much.

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))

 

[Ashish Mathur]

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

 

[Satti Charvak]

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).

 

[Rick Rothstein]

This is a little more compact...

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

 

[Sageman]

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

 

[Ashish Mathur]

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com