Time calculation formulate when time goes into another day

Odyssey Medical

We have this formula that is working well to calculate total hours worked. The issue is when the time rolls into the next day. I can get a formula to work great in excel however having troubles here since smartsheet does stuff a bit differently. We do not ask for start date vs end date so only working with time. Does anyone have a solution for this?

Paul Newcome

Take a look through here. There are a number of time based solutions that should allow you to piece together what you need.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Odyssey Medical

@Paul Newcome I tried searching through that lengthy thread and couldn't come up with a solution. We are only capturing start time & end time, as you can see, when the end time rolls over midnight, the formula creates a negative value. In the case of the screenshot below, the Total Hour column formula equates to -11 however it should be 13, then we have a break of 1 so paid hours should be 12.

Paul Newcome

You will need to use an IF statement that basically says "if the end time is less than the start time then add 24 to the end time hours". If you would like some help figuring out exactly how/where to put that, go ahead and paste the formula itself here so I can just insert it into what you already have.

Odyssey Medical

@Paul Newcome here is the formula...I appreciate the help...I will work on incorporating an IF statement into it, but I am assuming you'll be quicker at it.

=IF([End Time]@row = "", "", ((VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)))

Paul Newcome

Give this a go...

=IF([End Time]@row = "", "", ((VALUE(LEFT([End Time]@row, 2)) + (VALUE(RIGHT([End Time]@row, 2)) + IF([Start time]@row< [End Time]@row, 24)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)))

Odyssey Medical

No luck unfortunately. I've made a copy of the sheet which can be accessed here with editing privileges with some dummy data. https://app.smartsheet.com/b/publish?EQBCT=2f8092235ff7406cae09e4f882ea6c2f

Paul Newcome

My apologies. I dropped it into the wrong place. Try this...

=IF([End Time]@row <> "", ((VALUE(LEFT([End Time]@row, 2)) + IF([End time]@row < [Start Time]@row, 24)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) - (VALUE(LEFT([Start Time]@row, 2)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)))