# Need help with time formula to remove negative values

Options
✭✭✭

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

That did the trick, thank you so much Paul!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!