Converting standard time to military time

[C.]

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.

 

[Joe User]

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.]

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!

 

[Joe User]

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 -----

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!

 

[C.]

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.  Ifthat
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 -----

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!

 

[Luke M]

(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!*

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 -----

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!
.

 

[Joe User]

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 -----

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 -----

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!

 

[CellShocked]

Select cell, format, and select Custom, and enter the string below in
the box:

dd-mmm-year hh:mm:ss

 

[CellShocked]

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 -----

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!

It parses your original string.