Excel – How to Highlight Current Week with Conditional Formatting

The following instructions show how to use conditional formatting in Microsoft Excel to automatically highlight the current week, based on a column with a “Week Commencing” date (a Monday).

This allows you to visually show which row relates to the current week.

For this example, we will use the following data and assuming that the date is 19/05/2024 (19 May 2024).

Example Table Content

Employee Task Week Commencing
John Doe Project A 15/05/2024
Jane Smith Project B 22/05/2024
Mark Brown Project C 29/05/2024


  1. Open Excel File
    Open the Excel file with your data.
  2. Select the Range
    Select the range of rows you want to apply conditional formatting to (e.g., A2:C10).
  3. Open Conditional Formatting
    Go to the ‘Home’ tab, click on ‘Conditional Formatting’, then select ‘Manage Rules’.

    Click on the ‘New Rule’ button
  4. Use a Formula to Determine Which Cells to Format
    In the New Formatting Rule dialog, select ‘Use a formula to determine which cells to format’.
  5. Enter the Formula
    Enter the following formula:

    =AND($C2 >= TODAY()-WEEKDAY(TODAY(),2)+1, $C2 < TODAY()-WEEKDAY(TODAY(),2)+8)
  • This formula calculates the start of the current week (Monday) and checks if the “Week Commencing” date falls within this week.
  1. Set the Format
    Click Format, choose your desired formatting (e.g., fill colour), and click OK.
  2. Apply the Rule
    Click OK again to apply the rule.

Your rows where the “Week Commencing” dates fall within the current week will now be highlighted.

Explanation of the Formula

  • TODAY(): Returns today’s date.
  • WEEKDAY(TODAY(), 2): Returns the day of the week (Monday = 1, …, Sunday = 7).
  • TODAY() – WEEKDAY(TODAY(), 2) + 1: Calculates the most recent Monday.
  • AND($C2 >= [Last Monday], $C2 < [Next Monday]): Checks if the date in column C is within the current week.

Article Downloads

TIP: You may need to right-click and select 'save link as'.