May 14 2023 10:56 AM
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!
May 14 2023 11:36 AM
See the second part of Date And Time Entry . Simply format the cells as hh:mm instead of h:mm am/pm.
May 14 2023 02:21 PM
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"
May 14 2023 02:21 PM - edited May 14 2023 02:23 PM
@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.