IF Formula with Criteria in 2 cells producing RYG

NikkiGeo

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

Paul Newcome

Try this:

=IF(ColumnB@row>= 1, "Red", IF(ColumnA@row> DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY())), "Yellow", "Green"))

brianschmidt

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!:)

NikkiGeo

https://community.smartsheet.com/discussion/comment/426762#Comment_426762

This didn't work for the Yellow ;/ ( I will post a pic in a sec

NikkiGeo

https://community.smartsheet.com/discussion/comment/426769#Comment_426769

This didn't work for the yellow either. I will post a pic.

brianschmidt

Hmm, yeah. A photo would help, since this is what I'm showing on my end:

NikkiGeo

@brianschmidt & @Paul Newcome

NikkiGeo

https://community.smartsheet.com/discussion/comment/426781#Comment_426781

I see... the yellow is future, it need OLDER, do I just put a negative?

brianschmidt

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?

NikkiGeo

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

brianschmidt

https://community.smartsheet.com/discussion/comment/426790#Comment_426790

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!:)