"C." wrote:
I do have two different types of dates:
Sep 1 2009 4:45PM
and
Sep 18 2009 11:15AM
[....]
I tried your second suggestion in a parallel
column "=--SUBSTITUTE(A1, " ", ", ", 2)" but
it returned #VALUE!

My __second__ suggestion was:

=--(LEFT(A1,6) & "," & MID(A1,7,99))

It should work in both cases above.

However....

In the above examples, there seems to be no space before PM and AM. If that
is truly the case, there are a several possible solutions.

If we can rely on the fixed format that you seem to have above, then try:

=--(LEFT(A1,6) & "," & MID(A1,7,11) & " " & RIGHT(A1,2))

Double-check that I counted the MID string length correctly.

If that does not work, then try:

=--(LEFT(A1,6) & "," & MID(A1,7,LEN(A1)-8) & " " & RIGHT(A1,2))

Don't forget to format the cell with the Custom format "dd-mmm-yyyy
hh:mm:ss" (without quotes).


----- original message -----

"C." wrote in message
...
On Nov 20, 12:13 pm, "Joe User" <joeu2004 wrote:
"C." wrote:
I have data (general format) in excel listed
exactly as: Sep 22 2009 4:43PM

If the cell format is General, then I presume the above is text.

In that case, in a parallel column or row, enter the following formula:

=--SUBSTITUTE(A1, " ", ", ", 2)

Use the Custom format "dd-mmm-yyyy hh:mm:ss" without quotes. Copy the cell
down or across.

Then you can copy-and-paste-special-value the new data over the old data,
and delete the new-data column or row.

Note: That SUBSTITUTE expression does not work if you have any dates of
the
form "Sep 2 2009"; note the 2 blanks after the month. In that case, you
can use the following for all "dates":

=--(LEFT(A1,6) & "," & MID(A1,7,99))


Thanks for the reply - I do have two different types of dates:

Sep 1 2009 4:45PM

and

Sep 18 2009 11:15AM

In the first, there are two spaces after the month and also two spaces
after the year before the time, but in the second there is only one
space after the month and only one space after the year (I'm assuming
the space in the first example is a place holder).

I would like both format types to appear like:
01-SEP-2009 16:45:00
18-SEP-2009 11:15:00

Since I have two different types, I tried your second suggestion in a
parallel column "=--SUBSTITUTE(A1, " ", ", ", 2)" but it returned
#VALUE!

Any thoughts? Can this not be done?

Thanks!