Easy Date Question: Today plus 30 but nothing in the past

Pamela Wagner

This one should be easy but every iteration that I've tried is not working.

I would like the formula to first check the Expired column to see if it's checked, if it is, return "Closed", if it's not, check to see if it's today's date or 30 days out and if it is, return "Blue" (I would like to see any tasks that are within a month out but not worry about those that are done/expired).

I am using the following:

=IF(Expired@row = 1, "Closed", IF([Expire Date]@row <= TODAY(+30), "Blue"))

It's working but it's giving me anything that is past due (prior to today) as well. How do I get it to look forward but not back?

Paul Newcome

You would use an AND statement to establish the other end of the date range.

=IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(), [Expire Date]@row <= TODAY(+30)), "Blue"))

Paul Newcome

You would use an AND statement to establish the other end of the date range.

=IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(), [Expire Date]@row <= TODAY(+30)), "Blue"))

Pamela Wagner

Thank you Paul! I am not sure if I tried the AND statement (I had been working on the full exercise literally all day). I need more practice on putting strings together. I can get them to work fine individually (=IF(Expired@row = 1, "Closed") but when I link them with other portions, it's hard to get them to work. Appreciate your help!

Paul Newcome

Happy to help. 👍️

One thing I have found that helps (me) with putting together complex formulas is using a separate cell for each portion and using cell references.

Then you can use the formula in Cell A to replace every Cell A reference. This helps keep parenthesis and whatnot in place where they need to be and allows you to make sure each section is working on its own as well as with each of the other portions. Since everything starts out broken down, troubleshooting becomes much easier as well.