I am trying to add traffic lights to my Smartsheet but I cannot get the formula to parse

sfloyd

I am trying to add Green, Yellow, Red stop lights to my Smartsheet. I am trying to add the following formula and keep getting a parsing error:

=IF([Intel Forecast Acceptance Date]17 < “10/15/2022”, “Yellow”, IF(AND([HPE Financial Qtr]17 = “Q3/2022”,[Intel Forecast Acceptance Date]17 > “07/31/22” then "Red", Green")))

17 is the row number

Intel Forecast Acceptance Date is a date

HPE Financial Qtr - Text/Number

Any ideas?

Thank you!

Paul Newcome

When referencing a date in a formula you need to use a DATE function.

DATE(yyyy,mm,dd)

You also need to retype the formula directly into the sheet as it looks like your quotes are "smart quotes" which are not recognized in formulas as valid characters. You can tell the difference by noticing the slant in yours vs the ones in this post being straight up and down.

sfloyd

I am still getting the parsing error:

=IF([Intel Forecast Acceptance Date]17 < DATE(2022,7,15), "Yellow", IF(AND([HPE Financial Qtr]17 = "Q3/2022", [Intel Forecast Acceptance Date]17 > DATE(2022/7/31) then "Red", "Green")))

What am I doing wrong?

Thank you!

Paul Newcome

You will need to remove a closing parenthesis from the end of the formula, insert a closing parenthesis after the second DATE function to close out the AND function, change the second DATE function so that it mimics the first with commas, and replace "then" with a comma.