Conditional formatting: highlight dates in current week

Icon of pencil and paintbrush for styling

Purpose: To apply conditional formatting based on dates falling in the current week

For example, we have a Gantt chart where we want to highlight the current week:

Screenshot of Gantt chart with cells highlighted based on current week
Syntax: check for dates falling in the current week

=WEEKNUM(CellRef) = WEEKNUM(NOW())

In the example Gantt chart above, we want the cells in Row 3 to highlight green when the date falls into the current week. We set up the conditional formatting rule as below.

  • highlight the cells C3 to J3 (the range that the rule will apply to, and this can be updated after the rule has been set up),
  • select ‘Conditional formatting | New rule‘ from the ‘Home’ tab on the ribbon,
  • and select the option ‘Use a formula to determine which cells to format‘
  • enter the formula below:

=WEEKNUM(C$3) = WEEKNUM(NOW())

Screenshot showing menu option to add a new conditional formatting rule in Excel
Screenshot of conditional formatting rule based on dates falling in current week

We use a mixed cell reference (C$3):

  • no $ symbol before the column label (C) so that the conditional formatting rule will check in the relevant column; this will change relative to the starting column C, and
  • $ symbol before the cell number label (3), so that it will only refer to the values in row 3.

We apply two separate rules using the SAME FORMULA, but with different formatting styles, so that we can apply:

  • solid green fill to the week date in row 3, and
  • green borders to the cells down the column (so we don’t overlay the yellow fills which are showing the task ranges).
Related posts