Exclude and If statement then "" and based on date then "Return Value"

Nathan Horn

=IF([Tier Status]@row <> "Group D", IF([Escalation Required]@row, <>"", (IF([WM Next Meeting Date]@row = "", "No Meeting Scheduled", IF([WM Next Meeting Date]@row = TODAY(-14), "Pending", IF([WM Next Meeting Date]@row > TODAY(-1), "Scheduled", IF([WM Next Meeting Date]@row < TODAY(-14), "Overdue"))))),"Escalated"))

What I'm trying to do:

• Exclude "Group D"
• If Escalation cell is not blank = "Escalated"
• If within 14 days before today's date = "Pending"
• If scheduled today or after = "Scheduled"
• If more than 14 days from today's date = "Overdue"

I get #incorrect after running the formula. What's the best way to write this?

Paul Newcome

Incorrect what exactly?

Nathan Horn

#Incorrect is the only thing that returns in all date cells with the date found.

If I remove (, "Escalated") from the escalation piece in the formula, the rest of the formula works but states #Invaliddate where I want it to say "escalated".

Paul Newcome

Try expanding the column width to see if there is more to the error message.

Here is the formula with the technically correct syntax.

=IF([Tier Status]@row <> "Group D", IF([Escalation Required]@row, <>"", IF([WM Next Meeting Date]@row = "", "No Meeting Scheduled", IF([WM Next Meeting Date]@row = TODAY(-14), "Pending", IF([WM Next Meeting Date]@row > TODAY(-1), "Scheduled", IF([WM Next Meeting Date]@row < TODAY(-14), "Overdue")))), "Escalated"))

Nathan Horn

I tried that as well. I'm getting #incorrect argument set...

Paul Newcome

One of these days parenthesis won't be a problem. Haha.

Give this a go...

=IF([Tier Status]@row <> "Group D", IF([Escalation Required]@row, <>"", IF([WM Next Meeting Date]@row = "", "No Meeting Scheduled", IF([WM Next Meeting Date]@row = TODAY(-14), "Pending", IF([WM Next Meeting Date]@row > TODAY(-1), "Scheduled", IF([WM Next Meeting Date]@row < TODAY(-14), "Overdue"))))), "Escalated"))

Nathan Horn

I really appreciate your help on this. The formula is working to a degree. Where there is an escalation marked, the data coming back in the cell with the formula now states #invalid data type.

Paul Newcome

Double check that all of your date columns are in fact date type columns. If they are, then there is non-date data in one of those columns (even though it may look like a date) which we would need to explore a little more.