Forum Discussion
Conditional Formatting for Date Occurring 2 Weeks (not days) in the Future
I'm using Office 365 and I have a column of dates that are sorted in ascending order (recent date on top). In Conditional Formatting\Highlight Cell Rules\A Date Occurring section, there are three provided options to format Weeks: "Last week", "This week", and "Next week". I've successfully utilized all three of those and applied a different fill color for each week. It's exactly what I need. I would also now like to do the same for weeks occurring after "Next week". For example, 2 weeks in the future, 3 weeks in the future, 4 weeks in the future. I've found lots of documentation that provides formulas for # days in the future, but that doesn't do what I need because # days in the future doesn't take into account the actual week. I need the formatting to recognize calendar weeks the same way that the provided options "Last week", "This week", and "Next week" do. Is this possible? A Microsoft Support rep just told me via Chat Client it's not. I'm having a hard time believing that's true. Thanks in advance.
11 Replies
Patrick2788Thanks much for this! It *almost* works. I added the formula four times exactly as you typed it, substituting 1 thru 4 for X to shade the next 4 weeks.
=WEEKNUM($A2)=WEEKNUM(TODAY())+1
=WEEKNUM($A2)=WEEKNUM(TODAY())+2
=WEEKNUM($A2)=WEEKNUM(TODAY())+3
=WEEKNUM($A2)=WEEKNUM(TODAY())+4So today is 02-25-20 (for those reading the post in the future). The new formulas shade in variants of gray that increase with each week for the next 1-4 weeks. I've attached a picture of what the result is. Sort of weird that the formula(s) see the same date, 03-12-20, in two different weeks: Week 2 and Week 3. Thanks again for your help. I think your formula may be the answer, but something needs to be tweaked; I'm just not sure what.
Ideally, the dates would be shaded in variant shades of gray as follows (weekend days won't appear as they are not used in this application). NOTE: Red is last week and Peach is this week (those were already done with the conditional formatting options provided by excel).
Week 1: 03-02 thru 03-06
Week 2: 03-09 thru 03-13
Week 3: 03-16 thru 03-20
Week 4: 03-23 thru 03-27
Thanks again!
If I were tackling this myself, I think I'd work out some "helper cells" or a "helper table" off in an obscure part of my workbook, where I'd first determine what range of dates falls in each of those weeks. One of those helper cells would be a simple "Y" for "2 weeks out" or "3 weeks out" etc.
And then refer to that cell (those cells) in the conditional formatting dialog box.
