Conditional formatting for wk 3 and wk 4 data.

[AndyEd]

I'm having a bit of senior moment I think...

Excel provides automatic CF for dates that fall during the current week (this week) and the following one (next week). How can I go about highlighting dates for the following weeks (week 3 and week 4). I feel like I should be using an AND formula, incorporating the w/c dates but every time I try I can't seem to get it to work.

The dates need to dynamic, moving forward in time as new dates are added, capturing them by week. As shown below,

Current date = 08/02/2024
Current week (week 1) = Mon 05/02/24 - Fri 09/02/24 - Excel provides functionality
Next week (week 2) = Mon 12/02/24 - Fri 16/02/24 - Excel provides functionality
Week 3 - Mon 19/02/24 - Fri 23/02/24
Week 4 - Mon 26/02/24 - Fri 01/03/24

Thanks in advance.

 

[Joe4]

Excel has a WEEKNUM function that returns the week number of any date.
You can compare the week number of any date to the week number of the current date like this (for a date entry in cell A1):

=WEEKNUM(A1)-WEEKNUM(TODAY())

This could just get a little tricky if you are trying to compare dates in 2 different years.

 

[AndyEd]

Thank you kind Sir.

 

[Joe4]

You are welcome.
Glad I was able to help!