IF and TODAY
Hi, I have the below formula which is working correctly:
=IF([Item Name]@row <> "", IF(ISBLANK([Actual Sign Off Date]@row), "TBC", IF(AND([Actual Sign Off Date]@row <= [Sign Off Required Date]@row), "ON TRACK", IF(AND([Actual Sign Off Date]@row > [Sign Off Required Date]@row), "DELAYED"))))
I have another formula which is also working correctly:
=IF(TODAY(-[Days Required from Sign Off]@row) <= [Sign Off Required Date]@row, "ON TRACK", "DELAYED")
My problem is that I'm trying to include the second formula as part of the first formula but I can't get it working. Basically what I am trying to achieve is that if the [Actual Sign Off Date] is blank, then if Todays Date minus [Days Required from Sign Off] is before the [Sign Off Required Date], it shows up as DELAYED. Below is the formula I currently have but it is showing as #INCORRECT ARGUMENT SET.
=IF([Item Name]@row <> "", IF(ISBLANK([Actual Sign Off Date]@row), "TBC", IF(TODAY(-[Days Required from Sign Off]@row) <= [Sign Off Required Date]@row), "ON TRACK", "DELAYED", IF(AND([Actual Sign Off Date]@row < [Sign Off Required Date]@row), "ON TRACK", IF(AND([Actual Sign Off Date]@row > [Sign Off Required Date]@row), "DELAYED", IF(AND([Actual Sign Off Date]@row <> ""), "ON TRACK")))))
Answers
-
Hey @Emmet McKenna
Try this
=IF([Item Name]@row <> "", IF(ISBLANK([Actual Sign Off Date]@row), "TBC", IF(TODAY() - [Days Required from Sign Off]@row <= [Sign Off Required Date]@row, "ON TRACK", IF([Actual Sign Off Date]@row < [Sign Off Required Date]@row, "ON TRACK", IF(OR([Actual Sign Off Date]@row > [Sign Off Required Date]@row, TODAY() - [Days Required from Sign Off]@row >= [Sign Off Required Date]@row), "DELAYED", IF([Actual Sign Off Date]@row <> "", "ON TRACK"))))))
I noticed you had a number of IF/AND statements in your formula that did not have multiple criteria associated with them. An IF/AND is used when you had simultaneous conditions that must be met in order for the condition to be true (ex If I go outside AND it is raining, then I get wet).
Will the formula above work for you?
Kelly
-
Hi Kelly,
Thanks for the above, unfortunately it isn't working in two ways:
- The 2nd IF statement, IF(TODAY() - [Days Required from Sign Off]@row <= [Sign Off Required Date]@row, "ON TRACK", isn't working, if the [Actual Sign Off Date] is blank it shows TBC but it doesn't change if the [Days Required from Sign Off] taken away from Today's Date is less than or equal to the [Sign Off Required Date]
- Once a date is entered in the [Actual Sign Off Date] column it changes to "ON TRACK" no matter if the date is before, equal to, or after the [Sign Off Required Date], is it the last IF statement that is causing this issue?
-
The If statement will stop on the first True statement, so perhaps the order needs to be modified? Having the last outcome expected as the first IF, then next to last, etc. can sometimes make a difference in the results.
Could you post the full formula you now have in your cell for easier reference?
-
Hi, I have tried the below formula as suggested by Kelly but it isn't working:
=IF([Item Name]@row <> "", IF(ISBLANK([Actual Sign Off Date]@row), "TBC", IF(TODAY() - [Days Required from Sign Off]@row <= [Sign Off Required Date]@row, "ON TRACK", IF([Actual Sign Off Date]@row < [Sign Off Required Date]@row, "ON TRACK", IF(OR([Actual Sign Off Date]@row > [Sign Off Required Date]@row, TODAY() - [Days Required from Sign Off]@row >= [Sign Off Required Date]@row), "DELAYED", IF([Actual Sign Off Date]@row <> "", "ON TRACK"))))))
I have tried two separate formulas below which are working separately but when I try to merge them I get #INCORRECT ARGUMENT SET
=IF([Item Name]@row <> "", IF(ISBLANK([Actual Sign Off Date]@row), "TBC", IF(([Actual Sign Off Date]@row <= [Sign Off Required Date]@row), "ON TRACK", IF(AND([Actual Sign Off Date]@row > [Sign Off Required Date]@row), "DELAYED"))))
=IF(TODAY(-[Days Required from Sign Off]@row) <= [Sign Off Required Date]@row, "ON TRACK", "DELAYED")
Basically what I am trying to achieve is that if the [Actual Sign Off Date] is blank, then if Todays Date minus [Days Required from Sign Off] is before the [Sign Off Required Date], it shows up as DELAYED.
-
Can you explain what Days Required From Sign Off indicates? Since I'm unclear on what that column indicates, I don't understand how it factors into the equation. But since you said the two formulas you were using previously work as they should, and the situation that the first equation doesn't account for is when the Actual Sign Off Date is blank, I simply subbed in the second formula in the "TBC" spot. See if this works:
=IF([Item Name]@row <> "", IF(ISBLANK([Actual Sign Off Date]@row), IF(TODAY(-[Days Required From Sign Off]@row) <= [Sign Off Required Date]@row, "ON TRACK", "DELAYED"), IF(([Actual Sign Off Date]@row <= [Sign Off Required Date]@row), "ON TRACK", IF(AND([Actual Sign Off Date]@row > [Sign Off Required Date]@row), "DELAYED"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!