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.
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.
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
posted by jillithd at 1:39 PM on June 22, 2012
Response by poster: 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
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
posted by xingcat at 1:45 PM on June 22, 2012
Response by poster: 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
Is there a way to do that on hundreds/thousands of records?
posted by repoman at 1:56 PM on June 22, 2012
Best answer: 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]
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]
« Older What is the best way find another full time (IT... | How do I explain this in simple terms? Newer »
This thread is closed to new comments.
Military-style time is one selection.
posted by xingcat at 1:39 PM on June 22, 2012