How do I subtract "break" time from my Time Tracking Columns?

KentB

I'm pretty far along on what I would have thought would be a normal function for a spreadsheet application called "Smart!" Anyway, I need to figure out the last steps in subtracting the break from the total hours worked. It shouldn't be hard as they are one of the following: 0:15; 030; 0:45 & 0:00 or 1:00, whichever that would be.

here's a link to the section that includes a sample of what I'm doing:

(3) Time Formulas - Smartsheet.com

Here's a screenshot of what it looks like:

Here's my formula so far....

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

It seems I need the -VALUE([Break]@row, FIND(":", ...stuck in somewhere, but how to get the 15 minute increments as a chosen numeral for the total hours, I'm lost!

Please help if you have this!

Thanks!

@KentB

Paul Newcome

I do remember working on a solution that incorporates breaks. Did you look through the thread to see if you could find what you needed?

Lucas Rayala

Hi @KentB just throwing this out as a resource--@Paul Newcome has done a fair bit of this.

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

KentB

Thank you, Lucas. Yes, I've seen his work for/with other people and actually posted a second question naming him specifically in asking for help. I am hopeful someone, if not him, one of the people he's worked with in the past, will jump in and help me out. :)

Paul Newcome

I do remember working on a solution that incorporates breaks. Did you look through the thread to see if you could find what you needed?

KentB

I have not. I'm sorry, I found one that looked correct, but when you do the addition, you can see it hasn't subtracted the break time out...

I've gotten to this point and it identifies all the squares; I tried putting it at the end of the equation, thinking it would make it easier to do what did work at the beginning, then subtract the break after.

It seems like I should be able to do this, but I'm stuck! I apologize!

KentB

I am still searching for the break time answer. Also, I've now noticed when I cross over midday, the number ends up negative, due to the simplified match problem of b-a = ?? It does not take into account the restart at one o'clock ....help!