If you want to highlight a recurring event or payment dates in Google Sheets, you can use a custom format rule in Format > Conditional Formatting.

How do we use that?

Let me dig deep into it.

We can highlight a cell or cell range in several ways in Google Sheets.

Here are some of the most common practices.

1. Draw a thick border around the cells.
2. Increase the font size.
3. Use a background (fill) color.
4. Make the text Bold, Italic, Underline, Strikethrough.
5. Change the Text Color.

To highlight a recurring event or payment dates in Google Sheets, we can choose single or multiple highlighting options mentioned in points 3, 4, and 5 above.

They are the rules presently supported in Conditional Formatting.

Recurring events/payments can be weekly/biweekly/monthly meetings, monthly or periodic bill payments, subscriptions, gym memberships, etc.

Assume we have a recurring event that takes place every other week. Let’s see how to write a highlighting rule for that event.

We can later modify that formatting rule suitable for the predefined recurring event/payment interval.

Example to Highlighting Recurring Event or Payment Dates in Google Sheets

Here are two examples that to use depending on the orientation of the data that you have.

The Rule for Vertically Arranged Data

You can use the below recurring event/payment highlight rule, based on OR and MOD when the event/payment dates are arranged vertically in B2:B.

=or(B2=$F$1,mod(B2-$F$1,14)=0)

Assuming cell F1 contains the first event/payment date.

The above format rule highlights dates in column B biweekly basis starting from the event/payment start date in F1, i.e., 28/07/2021.

To apply the above rule, please follow the below quick steps.

1. Select B2:B100 (or the cell range that you want in column B).
2. Click Format > Conditional Formatting.
3. Apply to Range > B2:B100.
4. Format Rules > Custom formual is.
5. Insert the rule.
6. Formatting Style > Fill Color > Green or your choice of color.

Points to be Noted

In the above example, the dates are arranged in chronological order. It’s not necessary to follow that. Even if the dates are shuffled, the formula (format rule) will work.

The dates sequence is also not necessary.

The above rule highlights the recurring event/payment dates happening every other week (biweekly).

To change the frequency of the events, change 14 in the formula with the corresponding days between two events/payments.

I have multiple events/payments. How do I apply all of them?

Example:-

If you have one more event, enter that event start date in cell F2.

Here is the second rule to use. Don’t forget to choose a different color.

=or(B2=$F$2,mod(B2-$F$2,14)=0)

This way you can apply more format rules.

Highlight Horizontally Arranged Recurring Event or Payment Dates

This time I want to highlight the recurring event dates in B2:Z2.

The event start date is in cell B5.

=or(B2=$B$5,mod(B2-$B$5,14)=0)

To apply the rule, please follow the above six quick steps (the cell range should be B2:Z2, not B2:B100).

Resources

• Date Related Conditional Formatting Rules in Google Sheets.
• Highlight an Entire Row in Conditional Formatting in Google Sheets.
• AND, OR, or NOT in Conditional Formatting in Google Sheets.
• Highlight Top 10 Ranks in Single or Each Column in Google Sheets.

Resources Related to Highlighting Duplicates in Google Sheets

• Highlight Duplicates in Single, Multiple Columns, All Cells in Google Sheets.
• Highlight Partial Matching Duplicates in Google Sheets.
• How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.
• Highlight Visible Duplicates in Google Sheets.