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.
Answers
-
=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")
-
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......
-
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?
-
Hi Todd,
This did the trick! Thank you to both of you for your help, much appreciated!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!