Time Duration Calculation

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

Find more posts tagged with

Non-profit

Formulas

Enhancement Request

Comments

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.

SmartSheet Formula.jpg

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…