Excel help

June 22, 2012 1:34 PM

I have an easy (hopefully) question regarding time formatting in Excel.

If I have a cell that has '2:33pm' and is formatted as hh:mm (i think), how do I convert that to military time? Thanks.

posted by repoman to computers & internet (7 answers total)

If you right-click and select "Format Cells," then in "Number," select "Time."

Military-style time is one selection.
posted by xingcat at 1:39 PM on June 22, 2012

Hi. You can do a custom format like this: h:mm. Try that and see if it works for you.
posted by jillithd at 1:39 PM on June 22, 2012

I think the original cell '2:33pm' is not a value but text. If that makes a difference.
posted by repoman at 1:44 PM on June 22, 2012

You have to separate the number from the "PM" with a space, and then it'll be a time value.
posted by xingcat at 1:45 PM on June 22, 2012

You have to separate the number from the "PM" with a space, and then it'll be a time value.

Is there a way to do that on hundreds/thousands of records?
posted by repoman at 1:56 PM on June 22, 2012

If the cells are in text format, you'll need to convert them into numerical values first, and then apply the military time format. If the data is written as follows in cell A1:

2:33pm

If it is text, then the following formula in cell B1 will convert it to military time:

=TEXT(VALUE(LEFT(TRIM(A1),LEN(TRIM(A1))-2)&" "&RIGHT(TRIM(A1),2)),"h:mm")
posted by Jasper Friendly Bear at 1:57 PM on June 22, 2012 [4 favorites]

That was cool. Thanks so much.
posted by repoman at 2:06 PM on June 22, 2012