IF Formula with Criteria in 2 cells producing RYG
I need a formula to put a symbol in ColumnC based on the date in ColumnA and number in ColumnB, with the following criteria:
- If ColumnB is greater than or equal to 1 return Red -OR-
- If ColumnB is less than 1 and the date is ColumnA is more than a year from today’s date return Yellow -OR-
- If ColumnB is less than 1 and the date is ColumnA is less than a year from today’s date return Green.
Everything I come up with is #INCORRECT ARGUMENT SET or #UNPARSEABLE
Answers
-
Try this:
=IF(ColumnB@row>= 1, "Red", IF(ColumnA@row> DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY())), "Yellow", "Green"))
-
I was able to get the following formula to work:
=IF(ColumnB@row >= 1, "Red", IF(AND(ColumnB@row < 1, ColumnA@row >= TODAY(365)), "Yellow", IF(AND(ColumnB@row < 1, ColumnA@row < TODAY(365)), "Green")))
Not seeing your sheet, I would also say to make sure that your Date column is formatted as a Date column to ensure you don't get an #INVALID DATA TYPE or #DATE EXPECTED error.
Hope this helps!:)
-
This didn't work for the Yellow ;/ ( I will post a pic in a sec
-
This didn't work for the yellow either. I will post a pic.
-
Hmm, yeah. A photo would help, since this is what I'm showing on my end:
-
I see... the yellow is future, it need OLDER, do I just put a negative?
-
Ahh, I wondered about that. Here's how I would revise your formula.
=IF(ColumnB@row >= 1, "Red", IF(AND(ColumnB@row < 1, ColumnA@row < TODAY(365)), "Yellow", IF(AND(ColumnB@row < 1, ColumnA@row >= TODAY(365)), "Green")))
If you'd rather use @Paul Newcome 's formula, I would edit it as follows:
=IF(ColumnB@row >= 1, "Red", IF(ColumnA@row < DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY())), "Yellow", "Green"))
Did that work as expected?
-
WINNER WINNER!!!! Thank you both!!!
=IF(Rating@row >= 1, "Red", IF(AND(Rating@row < 1, Date@row <= TODAY(-365)), "Yellow", IF(AND(Rating@row < 1, Date@row < TODAY(365)), "Green")))
-
I understand now. You are correct in using the negative in the TODAY function to represent a date that is so many days in the past from Today's date. Glad you got it to work!:)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!