Date Conversion

[Guest]

A file I download from a mainframe has date data in it in a ddmmyyyy format.
Example, 1/1/2007 comes across as 1012007. How do I convert to a dd/mm/yyyy
format?

Thanks
Bill Falzone

 

[Guest]

hi,
you might try a formula like this....
=LEFT(B3,1) & "/" & MID(B3,2,2) &"/" & RIGHT(B3,4)
I tried it on the example you gave and it took. and is treated by excel as a
date because i can add 1 to the results and get 1/2/2007.

regards
FSt1

 

[Guest]

Thank you

hi,
you might try a formula like this....
=LEFT(B3,1) & "/" & MID(B3,2,2) &"/" & RIGHT(B3,4)
I tried it on the example you gave and it took. and is treated by excel as a
date because i can add 1 to the results and get 1/2/2007.

regards
FSt1

 

[Peo Sjoblom]

It might be easier to use data>text to columns if you do this on a regular
basis

select the column with the dates. do data>text to column, click next twice,
under column data format select Date and DMY from dropdown, click finish

record macro while doing it and attach a keyboard shortcut for next time