Need to type in as military time: 1330 and have it show as military time 13:30

[AprilIris]

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!

[#2]

@AprilIris 

See the second part of Date And Time Entry . Simply format the cells as hh:mm instead of h:mm am/pm.

[#3]

@AprilIris 

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"

[#4]

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