Formulas for Calculating Time

Paul Newcome

@L_123 Yours 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_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.

Katie Lafferty

Screenshot 2022-12-12 083357.jpg

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:

1. Straight Time - any hours during 7am-3pm that may span over multiple days (1-3 consecutive days possible).
2. 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).
3. 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

@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.

Ramona Morseman

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 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.

Deployment Plan Schedule.docx

Sherry_STS

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_STS Exactly which formulas are you using and what errors are you getting? Are you using any helper columns?

Jeff Reisman

@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.)

https://community.smartsheet.com/discussion/101116/calculating-time-passed-from-one-column-to-another

Paul Newcome

@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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/363623#Comment_363623

😂 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.

Paul Newcome

@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. 🤣🤣

Amanda P

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?

Paul Newcome

@Amanda P If you want the time with the date stripped out, you would use:

=RIGHT(Created@row, LEN(Created@row) - FIND(" ", Created@row))