Join 3,416 readers in helping fund MetaFilter (Hide)

Tags:

Excel help
June 22, 2012 1:34 PM   Subscribe

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) 1 user marked this as a favorite
 
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


« Older I work from home doing IT supp...   |  Please help me either articula... Newer »
This thread is closed to new comments.