Introduction
Any questions or issues you may have while using Smartsheet.One of them is "How do you track production downtime when the date line is crossed?"In particular, when using a 24-hour time input, you may have problems calculating time zones that span the next day.Smartsheet community members have provided helpful answers to questions like these.Here are more questions and answers in the Smartsheet Community.
Summary of user questions
Isaac T. has a time formula that works well for 3 out of 2 shifts, but for shifts that span from 11:00pm to 7:00am the next day, it has a big "negative result". He says he will come out.He wants to solve this problem without using two date fields.
Summary of my answer
jmyzk_cloudsmart_jp provided a formula to solve this problem by comparing the end time with the start time and adding 12 if the end time is less than the start time.
Question and answer details
Isaac T. ✭✭ 08/10/23
Thank you for reading this post.
I'm currently trying to rebuild my company's end of shift reporting system using Smartsheet.The problem I'm working on now is how to calculate the line downtime "Yield" in minutes at the transition from one day to the next.
The formula I initially put together is as follows...
([End HR]@row * 60 + [End MIN]@row) – (([Start HR]@row * 60) + [Start Min.]@row)
This formula is based on a 24-hour time input and works as I intended for 3 of the 2 shifts.But the 11rd shift from 00:7pm to 00:3am shows a big "negative result".
jmyzk_cloudsmart_en ✭✭✭✭✭ 08/10/23
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. ✭✭ 08/10/23
Thank you for the quick reply.I don't know why I didn't come to that conclusion myself, but it definitely worked!
Paul Newcome ✭✭✭✭✭✭ 08/10/23
See this thread for other issues with time calculations:
Formulas for calculating time
Conclusion
The Smartsheet community is a great platform for sharing effective solutions to various problems.In this example, community members quickly and effectively resolved the challenge of calculating production downtime at the turn of the day.If you have any problems or questions, we encourage you to join the community.