Formatting time input

Question

Good day,

At the moment I type all this manually:

Start Time 8:00:00 am

End Time 8:15:00 am

Duration 15

I thought with excel it can be simplified so that I don't have to type ":" and "am/pm" and don't need to calculate the difference myself.

So I did some googling and tried using this custom format: 0":"00":"00 AM/PM

Now I can type 80000 and it will come out as 8:00:00 AM, much faster than before, unfortunately it doesn't work for PM, so if I type 150000 I want to get 1:00:00 PM, instead I see 15:00:00 AM

I also subtract these times to get duration using this formula: =TEXT(I2,"00:00:00")-TEXT(H2,"00:00:00")

Could you help me to get this going the right way please? I'm trying to make typing the time simpler and faster.

Edit: I also tried something like this: [>=120000]h:mm:ss P\M;h:mm:ss \A\M

But when I type "150000" and expect to see "1:00:00 PM" I instead see "0:00:00 PM" and value of the field at the top near Insert Function button says "7/09/2310 12:00:00 a.m."

It looks to me that the time I type is used to calculate the date instead of time. But not sure if that is right and what to do about it.

Answer 1

The format you listed, 0":"00":"00 AM/PM, is a number format, and not a date/time format. Using a number for it disallows Excel to apply its understanding of minutes, seconds, and hours.

Use for example the format h:mm:ss to get a time display that is understood as such by Excel. You can then type 8:15 and it will show up as you wanted it.

You will also be able to directly subtract the cells, or do other math with them.
Note that any calculation results will be in full days, so either you format it a similar way to see hours and minutes, or you multiply by 24 to get hours as a number, etc.

Answer 2

I found somewhat of a solution here a link

Workaround is to have a custom format field (number) where you can type without colon and than have another field which will convert that number into time. Can later hide the number column.

Seems like there should be a better solution, after all people would use time a lot and typing colon a thousand times seems like a waste of time and too much use for Shift.