Imported Date Format

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

I have imported data from an accounting program and the date format is
20070101 for January 1, 2007. I have looked at various sites that would use
Left(),Mid(),Right() to break the numbers and then put them back into
2007/01/01 but was wondering if anyone had a simpler solution.
Thanks,
Lee Coleman
 
I have imported data from an accounting program and the date format is
20070101 for January 1, 2007. I have looked at various sites that would use
Left(),Mid(),Right() to break the numbers and then put them back into
2007/01/01 but was wondering if anyone had a simpler solution.
Thanks,
Lee Coleman
Click to expand...

Highlight the cells. Format cells > number tab > Select Custom from
the dropdown list

In the "Type:" box, type in the following:-

0000"/"00"/"00

And this will display it as you wish. Excel wont treat it like a date
however, it is just formatted to look like one - the value will still
be 20070101.

If you do wish to use functions you can try the following:-

=LEFT(A1,4)&"/"&MID(A1,5,2)&"/"&RIGHT(A1,2)

That is assuming that your date is in A1.

Hope this helps,
Matt Richardson
http://teachr.blogspot.com
 
Back
Top