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

Good to hear.


PS What does the formula mean?

Excel stores date and time as numbers (aka "date serial numbers"), where the
integer part is the date as the number of days since 12/31/1899 (so 1/1/1900
is 1), and the fractional part is time as a fraction of 24 hours.

You can see that if you format the cell with the above formula as Number
with 5 or more decimal places.

Normally, if the date string is of the form "Sep 1, 2009 4:45 PM" (and some
other forms), Excel recognizes it as date and time, and either Excel
converts it automatically to the date serial number (e.g. when you enter it
without quotes into a cell), or it would be trivial to cause Excel to do
that conversion, e.g. =--A1.

But in your case, the comma is missing after the day number, and the space
is missing before AM/PM. Excel does not recognize that form as date and
time.

So the formula that worked for you breaks up your string into 3 parts and
creates a new string (by concatenation, the "&" operator) with the comma and
space in the correct place so that Excel will recognize the string as date
and time. You can use Excel Help to learn the specifics of the LEFT, MID
and RIGHT functions.

The "--" operation (double negation) effectively converts the date string to
a date serial number. Any valid arithmetic operation would do the same
thing, for example adding zero or multiplying by one.


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

"C." wrote in message
...
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!