Imported Date Format

[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

 

[Jim May]

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Look familiar?

 

[Matt Richardson]

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

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

 

[Lee]

Thanks to all for helping.
Lee