Conditional formatting - this week

mssbass

Active Member
Joined
Nov 14, 2002
Messages
253
Platform
  1. Windows
I want to highlight a column green if the date shown is in this week. What would the formula be for the conditional format?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
If you cell was A1, the Conditional Formatting Formula would look like:
Code:
=weeknum(A1)=weeknum(today())

If year needs to be factored in, then use this variation:
Code:
=AND(WEEKNUM(A1)=WEEKNUM(TODAY()),YEAR(A1)=YEAR(today()))
 
Upvote 0
I'm highlighting the whole column and trying to put conditional formatting on it?
 
Upvote 0
That's fine. If you do that, and write your formula as it pertains to the first cell in your column, Excel will automatically adjust it for all the other rows in your column.

Try it, you'll see!
 
Upvote 0
I highlighted the column, clicked on conditional formatting and pasted this in the conditional format but there are still dates that are not highlighted suc as 5/5/2015 & 5/8/2015?: =weeknum(A1)=weeknum(today())
 
Upvote 0
I apologize if these are basic questions, but let's confirm these things first:
- Are you dates entered as dates or text?
- Is your first date in cell A1?
- Did you remember to select a Formatting Option (I am embarrassed to say, I actually forgot to do that once)?
 
Upvote 0
They are dates - 1st date is in A7 but I changed that in the formula. Yes, I set it to green.
 
Upvote 0
All right. Pick any blank cell and enter this formula:
=WEEKNUM(A7)

Then pick another cell and enter this formula:
=WEEKNUM(TODAY())

Then try:
=WEEKNUM(A7)=WEEKNUM(TODAY())
that last one should return TRUE.

If not, make sure that your formula matches your range reference.
Make sure that you are NOT highlighting the whole column, but starting with cell A7.
Or else your formula and cell references will not be aligned.
 
Upvote 0
I figured it out - the best way to do this in Excel 2010 is to select column, select Conditional Formatting at top, select Highlight Cell Rules, select "A Date Occcuring" - This week is one of the options.
 
Upvote 0
Cool. Never really checked those out (usually just write them myself), but that certainly does make things easier.
The method I proposed should still work though (I tested it out for myself to confirm that before posting).
Something must have been "off kilter".
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top