# 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

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!