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


=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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!