Production Downtime Tracking - Crossing the Dateline
Thanks for taking the time to read this post.
I am attempting to restructure my companies end-of-shift reporting system with smartsheet to facilitate better automated data processing. An issue i am currently dealing with is how to calculate the impact or "Yield" in minutes of line downtime as we cross from one day to another. The formula i initially slapped together is...
([End HR]@row * 60 + [End MIN]@row) - (([Start HR]@row * 60) + [Start Min.]@row)
This is driven by a 24 hour time format entry system and works perfectly for what i was trying to do for 2 of our 3 shifts. 3rd shift starting at 11:00pm (2300 hrs) and ending at 7:00am (0700 hrs) creates a huge "negative" result
I dont want to implement a second date field if i dont need to as these entries are done by hourly staff while working on the shop floor, i'm trying to ensure that the entry form bloat stays to a minimum.
see image for randomly generated sample data and formula testing result.
Best Answer
-
Hi @Isaac T.
=([End HR]@row + IF([End HR]@row < [Start HR]@row, 12, 0)) * 60 + [End Min]@row - ([Start HR]@row * 60 + [Start Min]@row)
Answers
-
Hi @Isaac T.
=([End HR]@row + IF([End HR]@row < [Start HR]@row, 12, 0)) * 60 + [End Min]@row - ([Start HR]@row * 60 + [Start Min]@row)
-
Awesome, thanks for the quick response.
I'm not sure how i didnt come to that conclusion myself but that def. did the trick!
-
If you have any other challenges with time calcs, feel free to browse this thread:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!