Forum Discussion

SWB_MTC's avatar
SWB_MTC
Copper Contributor
Feb 23, 2020
Solved

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 pr...
excel
Formulas and Functions
office 365
  • Patrick2788's avatar
    Patrick2788
    Feb 23, 2020

    SWB_MTC 

    conditional formatting rule:

    =WEEKNUM($A2)=WEEKNUM(TODAY())+X

     

    X=number of weeks

Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 23, 2020

SWB_MTC 

conditional formatting rule:

=WEEKNUM($A2)=WEEKNUM(TODAY())+X

 

X=number of weeks

SWB_MTC's avatar
SWB_MTC
Copper Contributor
Feb 25, 2020

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())+4

 

So 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! 

Resources