IF and TODAY

Emmet McKenna

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

Kelly Moore

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

Emmet McKenna

Hi Kelly,

Thanks for the above, unfortunately it isn't working in two ways:

1. 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]
2. 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?
3. 
   

ker9

Hi @Emmet McKenna

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?

Emmet McKenna

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. 

kirstie858

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