Hi lovely Excel forumers. I've searched, but I keep finding things that are quite different from what I want to do.
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?
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?