Need help with time formula to remove negative values

MichSun

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)

Paul Newcome

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))

Paul Newcome

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))

MichSun

That did the trick, thank you so much Paul!

Paul Newcome

Happy to help. 👍️