Forum Discussion

AprilIris's avatar
AprilIris
Copper Contributor
May 14, 2023

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 ...
excel
Excel for web
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 14, 2023

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"

Resources