Time Duration Calculation

KatieTMBA

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!

Katie

Nick Burrus

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.

KatieTMBA

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.

 

 

 

 

Ezra

Could this help you out? One form field each for hours and minutes, beginning and end... but might work until they solve the need for a time formatted column type.

https://app.smartsheet.com/b/publish?EQBCT=698fa97db2e848acbba1db03ca04667b

Paul Newcome

Here is how I would set things up...

 

Convert everything to minutes in the day, subtract scheduled from actual, convert the difference into hh:mm.

 

https://app.smartsheet.com/b/publish?EQBCT=2d4a631e2b3448eea24f712de0961bcd

James Carter

@NBurrus - trying to use ur calc for time but getting an error. I have people entering time as 9:01 start time and 11:12 as stop time - this is really 901 am and 1112 am - so I want 2.11 - not getting that Any help is appreciated.

Jim

James Carter

@Ezra Your example does not take into account if the start time is PM (say 11:50 PM - 23:50 PM) and the end time is AM (say 1:50 AM) - even in military time, it doesn't work.

Paul Newcome

@James Carter Feel free to browse through the time based solutions provided HERE. If you are unable to find one that works or need help modifying one to your exact needs, you can start a new thread and then "@mention" me, and I will be happy to help.