Need formula syntax help resolving unparsable error
So, not going to lie, I had to resort to chatgpt for this one after spending 2 weeks, banging my head on this (Got in over my head skill-wise with this one). I've already resolved a few issues I identified using answers to other questions here, but now I'm stuck. I think it has something to do with the nested OR() AND() syntax, which is something I've never used before. Can someone take a look and see what is causing the #UNPARSEABLE?
The formula calculates a next review date for anyone curious.
=IF([Employment Status]@row <> "Active", "", IF(TODAY() - [Hire Date]@row <= 60, [Hire Date]@row + 60, IF(OR([Department]@row = "Traffic", AND([PT/FT]@row = "Full Time", OR([Position]@row = "Lead", [Position]@row = "Assistant Manager", [Position]@row = "Manager", [Position]@row = "Area Manager"))), DATE(YEAR([Hire Date]@row) + CEILING((TODAY() - [Hire Date]@row) / 365, 1), MONTH([Hire Date]@row), DAY([Hire Date]@row)), IF(OR(AND([PT/FT]@row = "Part Time", [Position]@row = "Lead"), AND(OR([PT/FT]@row = "Part Time", [PT/FT]@row = "Full Time"), ISBLANK([Position]@row))), DATE(YEAR([Hire Date]@row) + FLOOR((TODAY() - [Hire Date]@row) / 180, 1) * 0.5, MONTH([Hire Date]@row) + ((FLOOR((TODAY() - [Hire Date]@row) / 180, 1) % 2) * 6), DAY([Hire Date]@row)), ""))))
It just occurred to me while looking at the code that it would have been smart to just make a binary leadership column... Well, learning opportunity anyway.
Answers
-
Without seeing the sheet or knowing what the formula is meant to do, the only way to troubleshoot it is to read it and see where it fails. Skip to the end to see where the problem is. Or read on to see how I troubleshoot...
I look at each part in turn, like this:
=IF([Employment Status]@row <> "Active", "")
Looks good. If you test this part in your sheet, it should work.
Then I add the next part
=IF([Employment Status]@row <> "Active", "", IF(TODAY() - [Hire Date]@row <= 60, [Hire Date]@row + 60))
Also looks good. Again, if tested in the sheet, this should work.
Then I add the next part and because the thing to return is a long formula in itself I just use the text "test" to see if it works:
=IF([Employment Status]@row <> "Active", "", IF(TODAY() - [Hire Date]@row <= 60, [Hire Date]@row + 60, IF(OR(Department@row = "Traffic", AND([PT/FT]@row = "Full Time", OR(Position@row = "Lead", Position@row = "Assistant Manager", Position@row = "Manager", Position@row = "Area Manager"))), "test")))
This is also good.
I can then look at the date formula on its own, without the IF.
=DATE(YEAR([Hire Date]@row) + CEILING((TODAY() - [Hire Date]@row) / 365, 1), MONTH([Hire Date]@row), DAY([Hire Date]@row))
Looks fine, so we can put that back in where we had the placeholder text.
=IF([Employment Status]@row <> "Active", "", IF(TODAY() - [Hire Date]@row <= 60, [Hire Date]@row + 60, IF(OR(Department@row = "Traffic", AND([PT/FT]@row = "Full Time", OR(Position@row = "Lead", Position@row = "Assistant Manager", Position@row = "Manager", Position@row = "Area Manager"))), DATE(YEAR([Hire Date]@row) + CEILING((TODAY() - [Hire Date]@row) / 365, 1), MONTH([Hire Date]@row), DAY([Hire Date]@row)))))
So far, so good.
Then we look at the next IF:
=IF(OR(AND([PT/FT]@row = "Part Time", Position@row = "Lead"), AND(OR([PT/FT]@row = "Part Time", [PT/FT]@row = "Full Time"), ISBLANK(Position@row))), "test")
The logic is a bit convoluted but it works!
Problem
However, the date to be returned if that logic is true does not look good:
=DATE(YEAR([Hire Date]@row) + FLOOR((TODAY() - [Hire Date]@row) / 180, 1) * 0.5, MONTH([Hire Date]@row) + ((FLOOR((TODAY() - [Hire Date]@row) / 180, 1) % 2) * 6), DAY([Hire Date]@row))
What is the %2 doing in there? The whole MONTH part is odd:
MONTH([Hire Date]@row) + ((FLOOR((TODAY() - [Hire Date]@row) / 180, 1) % 2) * 6)
The parenthesis in bold signals the end of the month definition, all the text after that is not going to be understood by smartsheet. It is expecting a comma and then the DAY part of the year.
Everything else is fine.
The only change needed is to figure out what the month should be in the situation where the Position is blank or the Position is lead and part-time. If you need help with that part, can you explain in words what this is?
-
I think I figured it out. The issue was the % modulo function that the formula was using to determine if it should add 6 months or a year. This still isn't working correctly, as it is sometimes returning #INVALID DATA TYPE errors, but at least now I can test it. I think this is caused by the formula generating dates that are not real like March 30th, but at this point, that's just a guess. I'll report back.
=IF([Employment Status]@row <> "Active", "", IF(TODAY() - [Hire Date]@row <= 60, [Hire Date]@row + 60, IF(OR([Department]@row = "Traffic", AND([PT/FT]@row = "Full Time", OR([Position]@row = "Lead", [Position]@row = "Assistant Manager", [Position]@row = "Manager", [Position]@row = "Area Manager"))), DATE(YEAR([Hire Date]@row) + CEILING((TODAY() - [Hire Date]@row) / 365, 1), MONTH([Hire Date]@row), DAY([Hire Date]@row)), IF(OR(AND([PT/FT]@row = "Part Time", [Position]@row = "Lead"), AND(OR([PT/FT]@row = "Part Time", [PT/FT]@row = "Full Time"), ISBLANK([Position]@row))), DATE(YEAR([Hire Date]@row) + FLOOR((TODAY() - [Hire Date]@row) / 180, 1) * 0.5, MONTH([Hire Date]@row) + IF(TODAY() - [Hire Date]@row >= 180, 6, 0), DAY([Hire Date]@row)), ""))))
