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.
