Date Formats

[Denise]

I have a report that the date was sent as 20070301 instead of 3/1/2007. If I
use the format cell and change to date it doesn't work. Does anyone know of
a formula that will modify the 20070301?

 

[Dave F]

One way: =MID(F7,5,2)&"/"&RIGHT(F7,2)&"/"&LEFT(F7,4)

Adjust cell reference to suit.

Dave

 

[Dave F]

If you need to do calculations off the parsed text string, use this:
=DATEVALUE(MID(F7,5,2)&"/"&RIGHT(F7,2)&"/"&LEFT(F7,4))

Dave

 

[Peo Sjoblom]

If they are always 8 digits in that format, select the whole range
do data>text to columns, click next twice, in step 3 under column data
format select date, then from the dropdown select YMD and click finish
No need for formulas

 

[Ron Rosenfeld]

I have a report that the date was sent as 20070301 instead of 3/1/2007. If I
use the format cell and change to date it doesn't work. Does anyone know of
a formula that will modify the 20070301?

Assuming your 3/1/2007 is MDY format (and not DMY), then:

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

will convert this to a date.

Format the cell as "m/d/y"


--ron