# Incorrect argument, bad syntax somewhere?

I've been banging my head on this for longer than I care to admit. I'm sure it is something stupid syntax error I just keep missing. Can anyone take a look?

=IF([Employment Status]@row <> "Active", "", IF(AND([Date of Last Review]@row = "", Tenure@row < 91), [Hire Date]@row + 61, IF(AND([PT/FT]@row = "Full Time", Position@row <> ""), [Work Anniversary]@row, IF(AND([Work Anniversary]@row > TODAY(), [Work Anniversary]@row - 184 < TODAY()), [Work Anniversary]@row), IF([Work Anniversary]@row > TODAY(), [Work Anniversary]@row - 184, [Work Anniversary]@row + 184))))

Here is the plain text of what I am trying to do (I think)

(1)IF employee not active do nothing, OTHERWISE (2)IF(AND) review date is blank, AND Tenure less than 90 days THEN set next review date to 61 days after hire date, (3)OTHERWISE IF(AND) the employee is full time, AND the employee is a leader, THEN set next review date to their anniversary date, OTHERWISE (4.0) IF(AND) this year’s work anniversary is in the future AND this year’s work anniversary is more than 6 months away THEN set the next review date to this year’s work anniversary, OTHERWISE (4.1)IF this year’s work anniversary is in the future set the next review date to 6 months before this year’s work anniversary (4.2)OTHERWISE set the next review date to 6 months before this year’s work anniversary 

1.      Ignore inactive

2.      Set all next review dates for employees with no review and tenure less than 91 days to 60 days after hire date

a.       Takes care of all employees under 91 days tenure

3.      Otherwise if the employee is a FT Leader , set next review date to next anniversary date

a.       Takes care of all FT leadership over 90 days tenure

4.      For everyone else, set next review date to next future 6 month interval based on the anniversary date

a.       Takes care of everyone else over 90 days tenure

Tags:

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭

    Noticed a couple of things:

    1. You have 5 IF statements, but only 4 parentheses on the end.
    2. Instead of the section where you have the condition the negative 184 days before the < sign, I would put the days into the TODAY portion of the logic statement.

    I haven't tested this (let me know if it works), but this is what I would try in it's place:

    =IF([Employment Status]@row <> "Active", "", IF(AND([Date of Last Review]@row = "", Tenure@row < 91), [Hire Date]@row + 61, IF(AND([PT/FT]@row = "Full Time", Position@row <> ""), [Work Anniversary]@row, IF(AND([Work Anniversary]@row > TODAY(), [Work Anniversary]@row < TODAY(184)), [Work Anniversary]@row), IF([Work Anniversary]@row > TODAY(), [Work Anniversary]@row - 184, [Work Anniversary]@row + 184)))))

    Hope this helps!:)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!