R/Y/G/G and Date Ranges

Pamela Wagner

Being a newbie when it comes to formulas, I am able to get my formula to work as long as I don't have to add another criteria. I need my R/Y/G/B to flag based on:

If the Expire Date is within 30 days = Blue ; if it's between 31 - 60d = Red; if its between 61 - 90d = Yellow; if it's between 91 - 120d = Green. What I don't have added is that I need another qualifier, first it needs to check to see if the Expired column is checked, if not, then check the date. So, IF the expired column is not checked, then check the Expire Date to see if it falls within the criteria.

I got each to work individually with (without checking the Expired column first):

=IF([Expire Date]15 <= TODAY(+30), "Blue")

=IF([Expire Date]15 >= TODAY(+31), "Red")

=IF([Expire Date]15 >= TODAY(+61), "Yellow")

=IF([Expire Date]15 >= TODAY(+91), "Green")

but I cannot make it work when I qualify it to be between the range. So the Red category needs to be between 31 - 60d; and so on to the other formulas.

Once I get them to work individually, I will then need to add them all together so they are on one string not on individual lines. So each cell will be asking: Is the Expired column checked, if no, then if it's within 30 days return Blue, if not check to see if it's between 31-60 and return Red; if not check to see...

Am I using the correct formula type of IF? Do I need an AND in there somewhere? If none of the criteria are met, do I need to tell it to return "0" so that it's not given a color at all?

My end goal is to create a report which looks at each of these and if it meets the criteria of say, all of the contracts due within 31 - 60 days it looks to see if it's checked (or Red) and the returns all of the lines that contain Red so I can see the actual contracts that I need to review.

Pamela Wagner

First I realized that I didn't really need them to be combined as I was putting each of these in their own column; so that helped. My situation was that I need to check the Expired row first and if it's not checked (not expired) then check the Expire date.

Here are my formulas:

Past due: =IF(Expired@row = 1, "Closed", IF([Expire Date]@row < TODAY(), 1))

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

31 - 60 days out: =IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(+31), [Expire Date]@row <= TODAY(+60)), "Red"))

61 - 90 days out: =IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(+61), [Expire Date]@row < TODAY(+90)), "Yellow"))

91 - 120 days out: =IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(+91), [Expire Date]@row < TODAY(+120)), "Green"))

I was also able to create my report so that it looks at each column and if it's checked to pull the data from it. This lets me see how many are past/coming due and also the ability to click on the line in the report to go straight to the contract line.

Pamela Wagner

We can close this out as I finally figured this out!

Genevieve P.

Hi Pamela,

That's great! Would you mind posting your solution? It could help others who are looking for something similar. 🙂

Pamela Wagner

First I realized that I didn't really need them to be combined as I was putting each of these in their own column; so that helped. My situation was that I need to check the Expired row first and if it's not checked (not expired) then check the Expire date.

Here are my formulas:

Past due: =IF(Expired@row = 1, "Closed", IF([Expire Date]@row < TODAY(), 1))

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

31 - 60 days out: =IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(+31), [Expire Date]@row <= TODAY(+60)), "Red"))

61 - 90 days out: =IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(+61), [Expire Date]@row < TODAY(+90)), "Yellow"))

91 - 120 days out: =IF(Expired@row = 1, "Closed", IF(AND([Expire Date]@row >= TODAY(+91), [Expire Date]@row < TODAY(+120)), "Green"))

I was also able to create my report so that it looks at each column and if it's checked to pull the data from it. This lets me see how many are past/coming due and also the ability to click on the line in the report to go straight to the contract line.