Auto-Highlight Rotating Weekly Tasks in Google Sheets

Highlighting rotating weekly tasks can be a simple yet powerful way to track which week you’re currently in. Whether you’re managing office responsibilities, shared team duties, or classroom tasks, an automatic visual cue helps you stay organized and avoid confusion.

Table of Contents

Why Use Rotating Highlights?

Let’s say you have five columns — one for each week — and the tasks assigned to team members rotate weekly. Each week, the highlight shifts to the next column. Once you reach the final column (Week 5, for example), the highlight automatically resets and starts again from Week 1.

This kind of setup is especially useful in Google Sheets when you’re managing a recurring weekly schedule.

Example: Weekly Task Rotation in Google Sheets

Imagine you have three team members and a few office tasks to assign each week. Over five weeks, each person takes on a different task, and the rotation repeats after that.

Here’s a simple dataset showing this rotation:

Weekly task rotation across 5 weeks in Google Sheets

Let’s say this data is in range A1:F4. We want to apply auto-highlighting to columns B to F (Week 1 to Week 5), so that:

Week 1 is highlighted in the first week
Week 2 in the second week, and so on
After Week 5, the highlight loops back to Week 1

Formula to Auto-Highlight Rotating Weekly Tasks in Google Sheets

Here’s the formula that powers the highlighting rotation:

=LET(
   start_date, DATE(2025, 7, 22), 
   weeks, 5, 
   freeze, IF(start_date < TODAY(), TODAY() - MOD(TODAY() - start_date, weeks * 7), start_date), 
   seq, TODAY() - freeze, 
   week_count, (COUNTIF($B$1:B$1, "<>") - 1) * 7, 
   ISBETWEEN(seq, week_count, week_count + 7, TRUE, FALSE)
)

Customizing the Formula for Your Setup

You’ll need to adjust three things in the formula:

DATE(2025, 7, 22) — Replace this with the actual date you apply the formatting.
weeks, 5 — Replace 5 with the number of weekly columns you’re rotating through.
$B$1:B$1 — This refers to the header row of the columns you’re highlighting. Make sure each header cell contains a value, as it’s used for counting the column position.

How to Apply the Rule

Select the range B2:F4 (or your actual task range).
Go to Format > Conditional formatting.
Under Format rules, choose Custom formula is.
Paste the formula.
Choose a highlight color.
Click Done.

That’s it! Your Google Sheet will now auto-highlight rotating weekly tasks, and the highlight will shift each week — looping back after the final column.

How the Formula Works (Breakdown)

Here’s a breakdown of how the formula highlights one column per week and loops back seamlessly:

=LET(
   start_date, DATE(2025, 7, 22), 
   weeks, 5, 
   freeze, IF(start_date < TODAY(), TODAY() - MOD(TODAY() - start_date, weeks * 7), start_date), 
   seq, TODAY() - freeze, 
   week_count, (COUNTIF($B$1:B$1, "<>") - 1) * 7, 
   ISBETWEEN(seq, week_count, week_count + 7, TRUE, FALSE)
)

1. `start_date`

The date from which you want the highlighting to begin — in this case, July 22, 2025. This should be the actual date you apply the conditional formatting.

2. `weeks`

The total number of weekly columns in your setup — 5 in this case.

3. `freeze`

This is the key part that makes the highlight loop correctly every few weeks.

IF(start_date < TODAY(), TODAY() - MOD(TODAY() - start_date, weeks * 7), start_date)

What it does:

It “freezes” the start of the current week cycle.
If today is still within the first 5-week cycle (35 days), it sticks to the original start_date.
Once 35 days have passed, it resets to the start of the next cycle.

Example:

Start date: July 22, 2025
Cycle length: 5 weeks × 7 days = 35 days
Until August 25, 2025, freeze returns July 22, 2025
On August 26, 2025, freeze becomes August 26, 2025, and the cycle restarts

This ensures that the highlight resets every 5 weeks — like clockwork.

4. `seq`

Calculates how many days have passed since the most recent cycle started. This helps identify the current week.

5. `week_count`

Calculates the offset for each week column:

(COUNTIF($B$1:B$1, "<>") - 1) * 7

Column B (Week 1) → 0
Column C (Week 2) → 7
Column D (Week 3) → 14
…and so on.

6. `ISBETWEEN(...)`

The final condition that determines whether to highlight the cell:

ISBETWEEN(seq, week_count, week_count + 7, TRUE, FALSE)

It checks whether the number of days (seq) since the start of the current cycle falls within the 7-day window assigned to each weekly column.

If TRUE, that column is highlighted.

In Summary

This smart formula uses LET, TODAY, MOD, COUNTIF, and ISBETWEEN to:

Track your position in a repeating weekly cycle
Match it to the correct column in your table
Auto-highlight only the current week
Reset automatically after the final column

No manual edits, no moving rules — just set it and forget it.

Wrap-Up

With one formula and a single conditional formatting rule, you can easily auto-highlight rotating weekly tasks in Google Sheets. It’s flexible, scalable, and works with any number of weeks.