I am making a timesheet entry form.

I want people to be able to enter the time in 4 key presses (e.g. for 12:45, I just want them to be able to type 1245, and not need to mess about entering a colon in the middle).

To get that to work (for some value of "worked"), I changed the format of the cells to a custom number type: 00\:00

But, of course, when it goes to calculate 1200 - 0830 (to work out hours worked in the morning) it gets 03:70. Which is bad.

So data entry is fine - it looks great! But I'm guessing I need to convert those numbers back into times before I can do the "end time minus the start time = hours worked" calculations, right?

So assuming the users aren't dopes and actually enter valid times, is there a way I can convert times in the format of 00\:00 into actual hh:mm format?