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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!