Production Downtime Tracking - Crossing the Dateline

Isaac T.

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.

SS_TimeIssue.PNG

jmyzk_cloudsmart_jp

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)

jmyzk_cloudsmart_jp

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)

Isaac T.

Awesome, thanks for the quick response.

I'm not sure how i didnt come to that conclusion myself but that def. did the trick!

Paul Newcome

If you have any other challenges with time calcs, feel free to browse this thread:

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