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)

Find more posts tagged with

Accepted answers

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

All comments

Paul Newcome

You can use a MAX statement

MichSun

That did the trick, thank you so much Paul!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.