Forum Discussion
AprilIris
May 14, 2023Copper Contributor
Need to type in as military time: 1330 and have it show as military time 13:30
I have seen multiple posts about "converting" from 12 hour clock to 24 hour clock, but my need is different: I automatically time time as, for example 1:30 pm, I would simply type 1330; I need excel to see the 1330 and understand it is 1:30 pm, but show as 13:30. Is this possible? Thank you!
- JoeUser2004Bronze Contributor
AprilIris wrote: `` I need excel to see the 1330 and understand it is 1:30 pm, but show as 13:30``
As I understood it, you are starting with 1330, not 1:30 PM.
So, simply changing the format to hh:mm is not a solution.
Moveover, simply changing the format to 00\:00 would change the appearance to 13:30. But Excel would still "understand" the value to be 1330, not 1:30 PM.
IMHO, you must use a formula to convert the number 1330 to the time 13:30.
For example, if 1330 is in A1, enter the formula =--TEXT(A1, "00\:00") in B1 and format as Custom hh:mm.
Then, column B, not column A, has the required military time.
You could copy the values in column B and use paste-value to overwrite the original values in column A. Then delete the values in column B.
Then, column A has the required military time.
It would be possible to implement a VBA procedures ("macro") to automate this conversion.
- PeterBartholomew1Silver Contributor
If you type 1330 into Excel it will be read as 'one thousand three hundred and thirty' and not as a time. To convert that to a time value (as a fraction of a day) you could use
= TIME(QUOTIENT(timeInput,100),MOD(timeInput,100),0) or, using 365, = TimeValueλ(timeInput) where TimeValueλ = LAMBDA(timeInput, LET( hours, QUOTIENT(+timeInput, 100), minutes, MOD(timeInput, 100), TIME(hours, minutes, 0) ) )
The number formatting still has to be "hh:mm"
See the second part of Date And Time Entry . Simply format the cells as hh:mm instead of h:mm am/pm.