Formatting Issues

Highlight Excel Cells on the Second and Fourth Thursday

Bushra ParveenBushra Parveen Nov 18, 2026 868 views

Question details

Highlight reminder cells when the current date is the second or fourth Thursday of the month.

Highlight Excel Cells on the Second and Fourth Thursday
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains reminder cells that should stand out on two Thursdays each month.
Observed behavior
The selected cells should be highlighted when TODAY returns the second or fourth Thursday, and the formatting should update when the worksheet recalculates.
Before you start

Identify the reminder cells you want to format and check that your device's date is correct. The rule uses today's date, not dates stored in the selected cells.

Solution 1Recommended

Create an Excel conditional formatting rule for the reminder cells

Use a formula-based rule to highlight the selected cells on the second and fourth Thursday of each month.

1
Select the reminder cells

In Excel, select the cells or range that should receive the highlight.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.

3
Enter the formula

In the formula field, enter =AND(WEEKDAY(TODAY())=5,OR(AND(DAY(TODAY())>7,DAY(TODAY())<15),AND(DAY(TODAY())>21,DAY(TODAY())<29))).

4
Choose and save the highlight

Select Format, choose a fill color or another format, and click OK to save the format and the rule.

5
Check calculation settings

Go to Formulas > Calculation Options and select Automatic so the rule updates when Excel recalculates.

Create an Excel conditional formatting rule for the reminder cells
How the rule works: With WEEKDAY's default numbering, Thursday is 5. The second Thursday falls on days 8–14, and the fourth falls on days 22–28. All selected cells use the same current date.
Try WPS Office

Highlight Thursday reminders in WPS Spreadsheet

Use a formula-based conditional formatting rule in WPS Spreadsheet to highlight reminder cells on the second and fourth Thursday.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Select the reminder range: Select the cells you want to highlight, then choose Home > Conditional Formatting > New Rule.
  3. 3. Enter the rule: Choose the formula-based rule option and enter =AND(WEEKDAY(TODAY())=5,OR(AND(DAY(TODAY())>7,DAY(TODAY())<15),AND(DAY(TODAY())>21,DAY(TODAY())<29))).
  4. 4. Set the format and save: Choose a fill color under Format, confirm the rule, and save the workbook.
Highlight reminder cells using TODAY and WEEKDAYOpen and edit Microsoft Excel XLSX filesUse a free, familiar spreadsheet interfaceChoose a custom fill color for reminders
microsoft office alternative - wps office

Frequently Asked Questions

Does the formula check the dates in my reminder cells?

No. TODAY() checks the current date, so the same rule applies to every selected cell regardless of its contents.

Why does the formula use 5 for Thursday?

WEEKDAY uses Sunday as 1 by default, making Thursday 5. The formula also checks for days 8–14 or 22–28 to identify the second or fourth occurrence.

When will the highlight update?

The rule updates when the worksheet recalculates. In Excel, check Formulas > Calculation Options > Automatic to enable automatic recalculation.

How can I change the highlight color later?

In Excel, select the formatted cells and go to Home > Conditional Formatting > Manage Rules. Select the rule, choose Edit Rule, and change its Format.