Date/Time formula - Need help

Options

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.

Tags:

Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    edited 02/21/23
    Options

    =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
    Todd M Keller ✭✭✭✭
    Options

    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
    MichSun ✭✭✭
    Options

    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
    MichSun ✭✭✭
    Options

    Hi Todd,


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

  • Kevin7859
    Kevin7859 ✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!