nested ifs with greater than less than- #unparseable

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

Find more posts tagged with

Formulas

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…