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

Join distinct collect with contains
Can someone help with this formula? Its not working as-is, but I also would like to use the "contains"/"has" for the leader column as I combined a bunch of contact columns. Here is my attempt to use has - Heres the references (other sheet) Month, all Leads, and R&B prog is the first one (project)
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Nested IF Statements
Hello. I cannot figure this out. =IF([GL Code]@row = 14030, "Leasehold") How do I add additional IF statements in the same cell, for example if the GL Code@row = 14040, "FF&E", GL Code@row = 14050, "CR Equip" Thanks.