nested ifs with greater than less than- #unparseable

07/01/19 Edited 12/09/19

Wrestled with this enough.  Need to call in a friend.  Can anyone figure out what is wrong with my formula?

 

=IF([Last review date]1> 365), "Red", IF(AND([Last review date]1 < 365, [Last review date]1 > 275), "Yellow", IF(AND([Last review date]1 < 275, [Last review date]1 > 0), "Green", "")))

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You have an extra closing parenthesis before "Red".

     

    365), "Red", 

     

    Should be 

     

    365, "Red", 

    thinkspi.com

  • I took the parenthesis off and the error change to #invalid operation

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you post the new formula exactly as it is in the sheet?

    thinkspi.com

  • =IF([Last review date]2>365, "Red", IF(275<[Last review date]1<365, "Yellow" IF(0<[Last review date]1<275, "Green")))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ah. Ok. You will need to revert back to using the AND function.

    =IF([Last review date]1> 365, "Red", IF(AND([Last review date]1 < 365, [Last review date]1 > 275), "Yellow", IF(AND([Last review date]1 < 275, [Last review date]1 > 0), "Green", "")))

    .

    Also...

     

    What kind of data is in the [Last Review Date] column?

    thinkspi.com

  • There is a date in that column.  I pasted your formula above and received #invalid operation.

    Thanks for trying to help.  This is so frustrating.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    The issue is that you are comparing a date to a number.

     

    What is your desired outcome? What are you trying to compare with 

     

    IF(Date > Number, ......

    .

    You would either need to convert the date to a number to compare to a number or convert the number to a date to compare to the date.

    thinkspi.com

  • Thanks Paul. I thought that might be an issue, but thought I saw it used somewhere else.  I am trying to create a sheet that tracks documents and their status relative to the annual review process. Documents must be reviewed each year. I was hoping to assign a green symbol if the doc has been reviewed within the past nine months. If the document hasn't been reviewed between nine and twelve months, yellow. Lastly, if the doc hasn't been reviewed in a year, assign a red status.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ah. Ok. That's actually pretty straightforward. How accurate do you need your date calculations to be? Can you use a general 270 days for nine months and 364 for 12 or do you need it to be more exact based on days within the months and whether or not it is a leap year?

    thinkspi.com

Sign In or Register to comment.