Converting standard time to military time
Select cell, format, and select Custom, and enter the string below in
the box:
dd-mmm-year hh:mm:ss
On Fri, 20 Nov 2009 09:13:25 -0800, "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))
----- original message -----
"C." wrote in message
...
Hi all,
I've been having some trouble with time conversion and can't seem to
find the answer - I'm hoping someone can help me out.
I have data (general format) in excel listed exactly as: Sep 22 2009
4:43PM
and I need data to be converted into exactly this format:
22-SEP-2009 16:43:00
I have many cases in the former format and need them all to be in the
latter.
Any suggestions?
Thanks in advance.
|