I want to highlight a column if the date in cell #4 is this week.

[John R.]

I used conditionaly formatting to hightlight the cell in row 4 if the date is
"this week" but now I have been asked to have the entire column (well, row 4
down) to highlight if the date is "this week".

I need to use conditionaly formatting as the cells have to be used for other
data.

Thanks,

 

[T. Valko]

Is "this week" the literal text string this week or is "this week" a date
that falls within this week? If it's a date then how does this week run?
From Sunday to Saturday or from Monday to Sunday (or something else)?

 

[John R.]

Sorry. "this week" is a calendar date. All the dates I will have are Monday
dates, signifying the work week a specific task is to take place. 2007's
conditional formating has an option "Format only cells that contain" and then
select "Dates Occurring" and "This week" that works great for the cells with
the Monday date. I just can't figure out how to get the column to highlight
if today is in the same week as the Monday date. If it were a month, I
figured that out, but Excel doesn't have the same function for weeks (that
I'm aware of).

Does this answer your questions?

 

[John R.]

I just discovered WEEKNUM. This appears to work, I will need to copy to all
columns I want the highlighting to occur in (every other column in a series).

=(WEEKNUM(W$4)=WEEKNUM(TODAY()))

 

[T. Valko]

Try one of these...

A1 = a Monday date

These formulas will return either TRUE or FALSE if today's date is within
the week based on the Monday date in cell A1. The week runs from Monday to
Sunday.

=TODAY()=MEDIAN(TODAY(),A1,A1+6)

Or:

=AND(TODAY()>=A1,TODAY()<=A1+6)