I have dates like the following:

Apr142003 12:56AM
Jan 12 2003 10:53AM
Feb 14 2004 11:43AM
Apr 21 2003 10:53AM
*Note there is only one space between the fields*
Also on the same column are dates like this
Jul 4 2005 1:18AM (between jul and 4 are 2 spaces and between 2004 and
time is 2 spaces. 2 spaces occur when the number is only 1 digit in size but
looses the second space when the number is 2 digits in size). I've looked at
all the formatting formulas but nothing there for it. Unfortunately I can't
change it at source. Any programming ideas out there that I should persue?

Hi,

Assuming dates are enterred as
mmmx[x/d]dxyyyyx[x/h]h:mmPP
with
- x representing a space
- [x/d] or [x/h] meaning either a space or a single digit
- PP being either AM or PM

Say your date is in a1.
B1: =MID(A1,5,2)&"-"&LEFT(A1,3)&"-"&MID(A1,8,4)&" "&MID(A1,13,5)&"
"&RIGHT(A1,2)
-- returns the date properly for next step, formatted as Text.
C1: =DATEVALUE(B1) + TIMEVALUE(B1)
-- Returns date-time as number. Format the Cell as date.
--
Regards,
Sébastien


"Rory Carroll" wrote:

I have dates like the following:

Apr142003 12:56AM
Jan 12 2003 10:53AM
Feb 14 2004 11:43AM
Apr 21 2003 10:53AM
*Note there is only one space between the fields*
Also on the same column are dates like this
Jul 4 2005 1:18AM (between jul and 4 are 2 spaces and between 2004 and
time is 2 spaces. 2 spaces occur when the number is only 1 digit in size but
looses the second space when the number is 2 digits in size). I've looked at
all the formatting formulas but nothing there for it. Unfortunately I can't
change it at source. Any programming ideas out there that I should persue?