Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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:

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
This formula is completely wrong. I don't know why it has so many upvotes. The other answer is the correct answer. – Zenadix Oct 16 '15 at 16:53

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 '14 at 18:59
This is the correct answer. – Zenadix Oct 16 '15 at 16:54

In addition, I've wanted to highlight 'week commencing' cells within a Gantt chart which are between a start date and end date. I've adapted the formula as such:

=AND(StartDate - WeekCommencing < 7, EndDate - WeekCommencing >= 0)

share|improve this answer

Your Answer


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.