Gray out last week and next week

[xoomoon]

Hey everyone,

I have 1 month worth of schedule for work in Excel and I want to make it easy for everyone to read it when the file opens up. So basically, I want to keep the current week's schedule and gray out with transparency the week before and the week after the current week.

I want it to automatically do this every time I'm at a new week.
Is there a way to do this? I looked at conditional formatting but can't seem to figure out the exact formula.

Many thanks in advance!

Ryan

 

[etaf]

where do you have a date in the spreadsheet
you can use today() in a conditional formatting to compare todays date with the row and then format the row
but would like to know the dates you have ?

 

[xoomoon]

where do you have a date in the spreadsheet
you can use today() in a conditional formatting to compare todays date with the row and then format the row
but would like to know the dates you have ?

Thanks for the answer.

I have this week 2/9 - 2/15.
The new week always starts on a Saturday.
So i would want 2/2 - 2/8 and 2/16 - 2/22 to be grayed out(transparent).
Still want to be able to look at any dates.
On a full months worth of schedule, when i'm on the first week, the last 3 weeks would be grayed out and when i'm on the last week of the month, the first 3 weeks would be grayed out. Not sure how to do this based on the formula.

 

[xoomoon]

is there a formula to just keep the current week and gray out all other active cells?
This is ultimately what I want. Keep current week and gray out all other weeks.
This way I don't have to play with the before and after week.

 

[etaf]

yes you can use conditional formatting
i understand what you want to do
but i still dont know how the data is set out on the schedule - so difficult to give an answer
if the date for the week beginning is in column A
then you could test column A for if this week or last week or next week

 

[xoomoon]

thanks

so here are the corner cells where the columns are located at.
A2 - A75
AR2 - AR75

current week is
s2- S75
z2-z75

thanks

 

[etaf]

whats in S2 and whats in z2

lets say you have 3 weeks showing

S2 = 2/2 Z2 = 2/8
S3 = 2/9 Z3 = 2/15
S4 = 2/16 Z4 = 2/22

so you can use in conditional formatting
select range A2:AR75
formula
=NOT(AND(TODAY()>=$S2,TODAY()<=$Z2))

https://www.dropbox.com/s/rtio5v9xayl7wyp/WeekSelect_etaf.xlsx?dl=0