How do I subtract "break" time from my Time Tracking Columns?
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
Best Answer
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hi @KentB just throwing this out as a resource--@Paul Newcome has done a fair bit of this.
-
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. :)
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!