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?

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

Adjust cell reference to suit.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Denise" wrote:

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?

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dave F" wrote:

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

Adjust cell reference to suit.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Denise" wrote:

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?

If they are always 8 digits in that format, select the whole range
do datatext 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

--
Regards,

Peo Sjoblom


"Denise" wrote in message
...
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?

On Wed, 4 Apr 2007 11:12:02 -0700, Denise
wrote:

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