Conditional Formatting based on calculated future dates (month only, ignore day and year)

Originally Posted by Nitsirk82

I have a very specific request and I spent much of my day trying and failing at this on my own. I'm hoping someone here might be able to help. I've created a copy of my excel removing any business related information so that I can attach a sample. I need to be able to track upcoming Group Insurance Renewals so that we know when to begin marketing for them. Some renewals are released 60 days prior to the 'renewal date' and some are released 90 days prior. For example, if today is 3/1/14, I need to know which groups we should begin marketing for: if it's a 60 day renewal that would be any 5/1 renewals, if it's a 90 day renewal that would be any 6/1 renewals. To futher complicate things, some groups renewal dates could be the 15th of the month, but they would still release with the 5/1 renewals so the formula/formatting needs to ignore the day and calculate on Month only. It should also ignore year. I'd like to color fill the dates that follow the logic above.

I'd also like the same function following the rules above, but to calculate for the next month's renewals in a different color. Example: today is 3/1/14, but I want to calculate the renewals that will be released around 4/1/14 (renewal dates 60 or 90 days from 4/1).

If all of that is too complicated, I would settle for manually entering a date in an empty cell off to the side, and having a conditional format to highlight any cells that match the month (ignore day and year). I struggled with even that myself, I googled it all day and couldn't get it to work.

Side note, if anyone has a course recommendation that goes above the basics, I would love to hear about it. I've done advanced excel seminars, but they don't go much further than conditional formatting exact matches and pivot tables.Attachment 306663

Something like this?

Close. And thank you for taking a stab at this. It didn't capture any of the 2013 dates. The reason it's important to ignore the year, is that this information is being pulled from other locations, not everyone is careful about updating the renewal date each year from 7/1/13 to 7/1/14. The renewal will still release in July regardless. So ignoring the year is important so no groups are missed.

But wow, what I saw already, very impressive. Thank you!! If the ignore part is a little too much, I could just make sure to audit and update each group more carefully. Not ideal, but I already love the improvement you just made for me.

Thank you!

Originally Posted by Nitsirk82

Close. And thank you for taking a stab at this. It didn't capture any of the 2013 dates. The reason it's important to ignore the year, is that this information is being pulled from other locations, not everyone is careful about updating the renewal date each year from 7/1/13 to 7/1/14. The renewal will still release in July regardless. So ignoring the year is important so no groups are missed.

But wow, what I saw already, very impressive. Thank you!! If the ignore part is a little too much, I could just make sure to audit and update each group more carefully. Not ideal, but I already love the improvement you just made for me.

Thank you!

I didn't understand from your OP what you wanted done with the 2013 dates. My fault.

I'm in a hurry just right now so I don't have time to write something that will ignore the years (which will be slightly tricky).

However I do have an improvement for you. v3 attached will check to see if any renewal date is still left in the prior year and will highlight these in red. So you can see at a glance where any user hasn't been careful with updating the renewal dates!

Originally Posted by Nitsirk82

That's fantastic. Thank you a thousand times

Glad to help . If you found my posts helpful, you may wish to consider taking a few seconds to rep me if you like (click on the star in the bottom left hand corner of my post).


BTW I had a think about how you wanted years ignored and I think I have the solution for you. v4 attached

Originally Posted by Nitsirk82

Absolutely, I just left some feedback. This is perfect! Thank you!

No worries and thanks for the rep

BTW if your thread is solved, please mark the thread as solved as a courtesy to other forum users.
To do this, click on Thread Tools (found on the top right hand corner of the screen) and choose Mark Thread as Solved.