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


.