I've seen a few ideas on similar topic threads but none of them worked quite right, so I'm trying again!

I have a form staff fill out to record their overtime and the reason for it. Does anyone have any formulas that I can use to populate a column showing the numbers of hours:minutes of overtime based on the "Scheduled Out Time" and "Actual Out Time" that staff members enter into the form?


Thanks much!




This is what I use:

=VALUE(LEFT([Time Alotted Hours]2, LEN([Time Alotted Hours]2) - 3)) + VALUE(RIGHT([Time Alotted Hours]2, 2)) / 60

Basically I have a column caled "Time Alotted Hours" of which it has a value like 1:30 in it for 1 hr 30 mins. 

Basically it takes the left most number (the 1 of 1:30) and converts it to a decimal, here 1.5

Then it adds the Right Most value (of 2 digits) and divides it by 60 to get a numerical decimal value. 

Together you get your conversion.

Do the rest of your formulas using standard decimals for math.

In reply to by NBurrus

Makes sense, and I may need to change my expectations of what data is input to simplify things. 

I'm hoping for a way to calculate the difference between 5:15 and 4:45 as being 00:30. I'd rather not have staff try and do the math for what portion of an hour they've gone into OT, and just enter their scheduled and actual times as the clock reads.

I've added a screen shot of the couple of columns giving me trouble below in case that helps.