Need help with time formula to remove negative values
Currently I am looking at values labeled "Late" and then comparing that time to get the difference from the critical pull time vs the actual pickup time. However, there are circumstances where the actual pickup time is alot earlier than the critical pull time, thus giving me a negative value. Is there a way for me to put a 0 or even have the row display no value?
Example 1:
Critical Pull time:17:30
Actual Pick Up Time: 18:45
Difference=75 mins
Example 2:
Critical Pull Time: 22:00
Actual Pick up Time: 10:45
Difference: -675
Formula:
=IF([Pull Ontime?]@row = "Late", ((VALUE(LEFT([Actual Pick Up Time]@row, 2)) + VALUE(RIGHT([Actual Pick Up Time]@row, 2)) / 60) - (VALUE(LEFT([Critical Pull Time]@row, 2)) + VALUE(RIGHT([Critical Pull Time]@row, 2)) / 60)) * 60)
Best Answer
-
You can use a MAX statement
=IF([Pull Ontime?]@row = "Late", MAX(((VALUE(LEFT([Actual Pick Up Time]@row, 2)) + VALUE(RIGHT([Actual Pick Up Time]@row, 2)) / 60) - (VALUE(LEFT([Critical Pull Time]@row, 2)) + VALUE(RIGHT([Critical Pull Time]@row, 2)) / 60)) * 60, 0))
Answers
-
You can use a MAX statement
=IF([Pull Ontime?]@row = "Late", MAX(((VALUE(LEFT([Actual Pick Up Time]@row, 2)) + VALUE(RIGHT([Actual Pick Up Time]@row, 2)) / 60) - (VALUE(LEFT([Critical Pull Time]@row, 2)) + VALUE(RIGHT([Critical Pull Time]@row, 2)) / 60)) * 60, 0))
-
That did the trick, thank you so much Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!