Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm working on a data table for finances. I want to highlight the row if the date is in the current week:

What it should look like:

enter image description here

The current condition formatting for the yellow fill spans B5:F15 and is the following:

=(TODAY()-$B6) < 7

The problem is that this ends up coloring the entire table yellow. Why does it do this, and how can I change the conditional formula?

What it actually looks like:

enter image description here

share|improve this question

2 Answers 2

I think I figured out the solution. I needed to check between 0 < x < 7. See below:

= AND(TODAY()-$B6)<=7, TODAY()-$B6)>=0)

This will highlight the row whose date reflects the current week.


Additionally, if you wish to highlight the row(s) in the current month, use the following conditional formula, and apply it to the entire table:

=AND(MONTH(TODAY())=MONTH($B6),YEAR(TODAY())=YEAR($B6))
share|improve this answer
    
For current month try =$B6-DAY($B6)=TODAY()-DAY(TODAY()) –  barry houdini Apr 22 '13 at 19:31
    
That does look a little cleaner, thank you! :) –  Jon Apr 22 '13 at 19:37

For the current week use

=today()-weekday(today(), 3)=$b6-weekday($b6, 3)
share|improve this answer
    
This worked for me, while the other answers did not. –  AndyM Jul 28 at 18:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.