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
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.
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
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 ✭✭
@EzraYour 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 CarterFeel free to browse through the time based solutions providedHERE. 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.
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/52346/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":38,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
