# Time calculation formulate when time goes into another day

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?

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

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

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

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

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

• 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

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!