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", "")))
Comments
-
You have an extra closing parenthesis before "Red".
365), "Red",
Should be
365, "Red",
-
I took the parenthesis off and the error change to #invalid operation
-
Can you post the new formula exactly as it is in the sheet?
-
=IF([Last review date]2>365, "Red", IF(275<[Last review date]1<365, "Yellow" IF(0<[Last review date]1<275, "Green")))
-
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?
-
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.
-
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.
-
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.
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!