Date/Time formula - Need help

Hi all,

I need to add a date and time check to a SmartSheet I am working on. Currently, we are trying to track late arrivals into our building.

Example:

=IF([Actual Pick Up Time]@row > [Critical Pull Time]@row, "Late", "On-Time"

=IF([Scheduled Pickup Date]@row = [Actual Pick Up Date]@row, "On-Time", "Late")

The above statements works fine as one, but when I try nest the formulas, here is where I am having issues with "#INCORRECT ARGUMENT SET"

Tried the following:

=IF(AND([Actual Pick Up Time]@row = "", "", IF([Scheduled Pickup Date]@row = [Actual Pick Up Date]@row, "On-Time", "Late", IF([Actual Pick Up Time]@row > [Critical Pull Time]@row, "Late", "On-Time"))))

How can I combine these two formulas into one? Ideally, I want to capture when "Actual Pick Up Time" > "Critical Pull time" in conjunction with having the "Scheduled Pickup Date" match the "Actual Pick Up Date". If the "Actual Pick Up Date" > "Scheduled Pick Up Date" this should produce a late regardless of pickup time.

Hope this makes sense.

Find more posts tagged with

Formulas

Accepted answers

All comments

Kimberly Loveless

=IF([Actual Pick Up Time]@row > [Critical Pull Time]@row, "Late", "On-Time"

=IF([Scheduled Pickup Date]@row = [Actual Pick Up Date]@row, "On-Time", "Late")

You may need to use an OR/AND function within the formula as well.

Would something like this work?

=IF(AND([Actual Pick Up Time]@row > [Critical Pull Time]@row, [Actual Pick Up Date]@row > [Scheduled Pickup Date]@row), "Late", "On-Time")

Todd M Keller

I didn't see a graceful way to accomplish that, it seemed intent on ignoring one of the tests....... though I believe this one should work.

=IF([Actual Pick Up Time]@row > [Critical Pull Time]@row, "Late", IF([Scheduled Pickup Date]@row < [Actual Pick Up Date]@row, "Late", IF([Actual Pick Up Time]@row = [Critical Pull Time]@row, "On-Time", IF([Scheduled Pickup Date]@row = [Actual Pick Up Date]@row, "On-Time", "Late"))))

Good Luck......

MichSun

Hi Kimberly,

Love the solution, but it's calling the highlighted field "On-Time" because the dates match. But the timing is off, so it should be late. The one above is saying late because the timing if off, but dates are off as well.

Solution is close, should work, but any idea on why Its only picking one value and not both constraints?

MichSun

https://community.smartsheet.com/discussion/comment/363521#Comment_363521

Hi Todd,

This did the trick! Thank you to both of you for your help, much appreciated!

Kevin7859

I'm having similar issues but trying to extract the date from cells with (2019-12-22 18:50:13.342000) to columns for Month and Year.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.