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
Bookmarks