nested ifs with greater than less than- #unparseable

AndyGfmr

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", "")))

Paul Newcome

You have an extra closing parenthesis before "Red".

 

365), "Red", 

 

Should be 

 

365, "Red", 

AndyGfmr

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

 

Paul Newcome

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

AndyGfmr

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

Paul 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?

AndyGfmr

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 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.

AndyGfmr

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 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?