convert a string of into a date format
 

Thanks it worked beautifully, but what if the fields are
blank, is there a way to modify this formula so that in
stead on a #value being returned nothing will return?
-----Original Message-----
Hi Fred,

"Fred" wrote in message
...
I have a a bunch of dates and time in column M that are
missing the /, I was wondering if it's possible to
convert
values of 08142003 12:30 into 08/14/2003 12:30. Any help
would be great, thanks :)

With one of those strings in cell M1, you could use a
formula like this to
get the date/time:

=DATE(MID(M1,5,4),LEFT(M1,2),MID(M1,3,2))+TIME(MI D
(M1,10,2),RIGHT(M1,2),0)

Of course, this assumes your data is consistent.

Regards,

Jake Marx
MS MVP - Excel


.

convert a string of into a date format
 

Hi Fred,
=IF(M1="","", old formula )

which would provide an error if an empty or zero length
cell is not the only problem. So you would notice it immediately
and alter your formula to match the situation. If you just want to intercept
all errors you could use...

IF(ISERR(old formula),"", old formula)

Suggest reading HELP, index (tab), IS Functions

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Fred" wrote in message ...
Thanks it worked beautifully, but what if the fields are
blank, is there a way to modify this formula so that in
stead on a #value being returned nothing will return?
-----Original Message-----
Hi Fred,

"Fred" wrote in message
...
I have a a bunch of dates and time in column M that are
missing the /, I was wondering if it's possible to
convert
values of 08142003 12:30 into 08/14/2003 12:30. Any help
would be great, thanks :)

With one of those strings in cell M1, you could use a
formula like this to
get the date/time:

=DATE(MID(M1,5,4),LEFT(M1,2),MID(M1,3,2))+TIME(MI D
(M1,10,2),RIGHT(M1,2),0)

Of course, this assumes your data is consistent.

Regards,

Jake Marx
MS MVP - Excel


.

convert a string of into a date format
 

Fred,

You might want to consider using Len(M1)
=If(Len(M1)=0,.....
or
=If(Len(M1)<???....

steve

"David McRitchie" wrote in message
...
Hi Fred,
=IF(M1="","", old formula )

which would provide an error if an empty or zero length
cell is not the only problem. So you would notice it immediately
and alter your formula to match the situation. If you just want to
intercept
all errors you could use...

IF(ISERR(old formula),"", old formula)

Suggest reading HELP, index (tab), IS Functions

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Fred" wrote in message
...
Thanks it worked beautifully, but what if the fields are
blank, is there a way to modify this formula so that in
stead on a #value being returned nothing will return?
-----Original Message-----
Hi Fred,

"Fred" wrote in message
...
I have a a bunch of dates and time in column M that are
missing the /, I was wondering if it's possible to
convert
values of 08142003 12:30 into 08/14/2003 12:30. Any help
would be great, thanks :)

With one of those strings in cell M1, you could use a
formula like this to
get the date/time:

=DATE(MID(M1,5,4),LEFT(M1,2),MID(M1,3,2))+TIME(MI D
(M1,10,2),RIGHT(M1,2),0)

Of course, this assumes your data is consistent.

Regards,

Jake Marx
MS MVP - Excel


.