(In case Joe User has left)

The first function "LEFT" returns the first 6 characters from the cell A1.
This is then concatenated via the ampersand symbol with a comma, and then
with the results from the MID function. MID looks at A1, and starting with
the 7th character, returned the next 11 characters. The purpose of this is
simply to insert a comma after the 6th character of the text. We then
concatenate on a space, and add on the last two characters from the text
(RIGHT being the opposite of LEFT).

The previous functions return a text string, which you do no want to use for
time format. Thus, the double negatives on the outside parenthesis cause the
text to be converted into a negative number, and then to a positive number.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"C." wrote:

On Nov 20, 1:18 pm, "Joe User" <joeu2004 wrote:
"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!

The suggestion =--(LEFT(A1,6) & "," & MID(A1,7,11) & " " & RIGHT
(A1,2)) was perfect! Thank you so much!!

PS What does the formula mean?

Thanks again!
.