Formulas for Calculating Time
Comments
-
Paul Newcome ✭✭✭✭✭✭@L_123Yours is definitely shorter, but I have no clue which one would be more of a draw on the back-end.
-
L_123 ✭✭✭✭✭✭[H]:mm - [H]:mm = [H]:mm
Columns are:
Ending Time "Out"
Starting Time "In"
=VALUE(LEFT(Out@row, FIND(":", Out@row) - 1)) - IF(VALUE(RIGHT(Out@row, 2)) > VALUE(RIGHT(In@row, 2)), 0, 1) - VALUE(LEFT(In@row, FIND(":", In@row) - 1)) + ":" + IF(LEN((IF(VALUE(RIGHT(Out@row, 2)) > VALUE(RIGHT(In@row, 2)), 0, 1) + VALUE(RIGHT(Out@row, 2)) - VALUE(RIGHT(In@row, 2)))) = 1, 0, "") + (IF(VALUE(RIGHT(Out@row, 2)) > VALUE(RIGHT(In@row, 2)), 0, 1) + VALUE(RIGHT(Out@row, 2)) - VALUE(RIGHT(In@row, 2)))
-
Paul Newcome ✭✭✭✭✭✭
@L_123I feel like a basic subtraction of start time from end time is listed in here. I'm going to have to dig through when I have some extra time and consolidate all of the solutions onto a single page again.
-
Hi Paul, have you had any time to think about my snow plow formulas? We had our first snowfall last night :) Remember I used your formulas to calculate total hours that may span over multiple days (military time). I need to now calculate these additional things:
- Straight Time- any hours during 7am-3pm that may span over multiple days (1-3 consecutive days possible).
- Overtime Hours- any hours not during 7am-3pm that may span over multiple days (1.5 hourly rate) & the same formula to calculate work on a holidays (2.0 double hourly rate for these hours).
- FEMA Time -which is essentially the same as Overtime Hours but will include the vehicles they drive. Once formulas for Straight Time & Overtime are figured out, I can apply to the FEMA stuff.
It's the spanning over multiple days that has me stumped.The in/out of the 7am-3pm zone during one event has my head spinning.
Katie
-
SteCoxy ✭✭✭✭✭
so frustrating that duration/time is still not a thing within Smartsheet without all of these technically amazing workarounds. I wonder if and when Smartsheet will ever release this as a proper functionality? I was advised 5 years ago that they were actively working on it, but nothing seems to be on the cards any time soon :(
-
Paul Newcome ✭✭✭✭✭✭
@SteCoxyI am fairly certain that it is in fact being worked on when they have the available Devs. There are a lot of complexities and variables though to program in once you start accounting for time zones and user preferences and whatnot plus all of the different ways that it could potentially be used alongside variable formats to account for. It is definitely not an easy job to tackle.
-
I've been reading through this thread (and several others) and am very close to what I'm looking for, but can't seem to find what I need to complete my calculations.
我有一个部署计划时间表,开始time, estimated duration and end time creating a running schedule where the end time is the start time for the next task. These tasks generally run continuously over the course of 2-3 days. I have changed to military time to use formulas I found for similar use cases and can get the time to show correctly until the end time goes to the next day. The times keep adding together so it becomes 24:35 instead of 00:35.
Attached is the excel example I'm trying to replicate in Smartsheet and screenshots of how far I've gotten and the formula's I'm using.
-
Hi, I'm fairly new to Smartsheet and looking for a formula to calculate total hours.
I've reviewed this thread and tried multiple formulas but I keep getting errors. Any help will definitely be appreciated! Thanks!
-
Paul Newcome ✭✭✭✭✭✭
@Sherry_STSExactly which formulas are you using and what errors are you getting? Are you using any helper columns?
-
Jeff Reisman ✭✭✭✭✭✭@Sherry_STS我只是写了这另一个社区成员last week. It's for overtime hours but no reason it won't work for start and stop times. I go through several different variations, including accounting for start times that are in the day before (such as start at 18:00 and end at 02:00, etc.)
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
Link:Smartsheet Functions Help PagesLink:Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Paul Newcome ✭✭✭✭✭✭
@Jeff ReismanAll of that should be accounted for (somewhere) in this thread as well. I think when it gets to 14 pages I will try to consolidate all of the solutions again.
-
Jeff Reisman ✭✭✭✭✭✭
Iknowit is in here somewhere! But the more times I write it up the better I get at it (at least, that's the theory I'm working with!)
Maybe it's time for an official white paper on it, or we can make a video at the next Engage conference, LOL.
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
Link:Smartsheet Functions Help PagesLink:Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Paul Newcome ✭✭✭✭✭✭
@Jeff ReismanHahaha. I know it gets a lot of traffic here in the Community and is available to anyone that has an account, but it helps me stand out as a SS Consultant. If too many people get good at it then I won't be special anymore.
-
Amanda P ✭✭✭
hello@Paul Newcomewill this only pull the hour from the system generated column?
=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12))
For example my column reads 03/06/2023 11:15 AM and using this formula I only get "11" but I need 11:15. am i doing something wrong?
-
Paul Newcome ✭✭✭✭✭✭
@Amanda PIf you want the time with the date stripped out, you would use:
=RIGHT(Created@row, LEN(Created@row) - FIND(" ", Created@row))
![https:\/\/us.v-cdn.net\/6031209\/uploads\/RY4IEQ6OGGXK\/image.png<\/a><\/p>This 'Overtime Hours' should be automatic, right now I have to fill it myself.<\/p>","bodyRaw":"[{\"insert\":\"I would like to calculate time passed from a Start Column '00:00' to an End Column '17:00' for example\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/RY4IEQ6OGGXK\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/RY4IEQ6OGGXK\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"This 'Overtime Hours' should be automatic, right now I have to fill it myself.\\n\"}]","format":"rich","dateInserted":"2023-02-14T18:13:55+00:00","insertUser":{"userID":156692,"name":"Paolo Fernandez","url":"https:\/\/community.smartsheet.com\/profile\/Paolo%20Fernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-04-24T21:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/101116\/calculating-time-passed-from-one-column-to-another","embedType":"quote","name":"Calculating time passed from one column to another"}"> https://community.smartsheet.com/discussion/101116/calculating-time-passed-from-one-column-to-another](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/RY4IEQ6OGGXK\/image.png\"){kind=link}