Formulas for Calculating Time
Comments

@L_123 Yours is definitely shorter, but I have no clue which one would be more of a draw on the backend.

[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)))

@L_123 I 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 7am3pm that may span over multiple days (13 consecutive days possible).
 Overtime Hours  any hours not during 7am3pm 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 7am3pm zone during one event has my head spinning.
Katie

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 :(

@SteCoxy I 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.
I have a deployment plan schedule that has start 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 23 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!

@Sherry_STS Exactly which formulas are you using and what errors are you getting? Are you using any helper columns?

@Sherry_STS I just wrote this up for another community member 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
Link: Smartsheet Functions Help Pages Link: 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!

@Jeff Reisman All 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.

😂 I know it 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
Link: Smartsheet Functions Help Pages Link: 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!

@Jeff Reisman Hahaha. 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. 🤣🤣

hello @Paul Newcome will 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?

@Amanda P If you want the time with the date stripped out, you would use:
=RIGHT(Created@row, LEN(Created@row)  FIND(" ", Created@row))
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives