Formatting Cells Based on Date Ranges in Google Sheets

Conditional Formatting

In Google Sheets, as in other spreadsheet programs, you can set the formatting of a cell (text color, background color) based on the data contained within that cell. This is called "conditional formatting," and it's valuable in that it provides visual cues for your users. A red cell, for example, might indicate an impending sue date. The data can include dates, text and numbers.

In our first tutorial on Google Sheets, we looked at extending the software's default conditional formatting options through the use of formulas. That solution is good for a limited range of dates, but it might get cumbersome in spreadsheets that span longer periods, since each possible date requires a unique formula.

But there is a way to simplify the conditional formatting setup by using date ranges rather than individual dates.

As with last week's tutorial, begin by selecting the range of cells to which you wish to apply your conditional formatting. In my case, I want to apply my formatting to an entire column (the "Due Date" column in my example), so I'll click the top of the column to select the entire thing.

google sheets example 1
Step 1: Select the range of cells to which you wish to apply your conditional formatting.

 

Then choose Format > Conditional formatting from the menu.

google sheets example 2
Step 2: Open the Conditional Formatting dialog.

 

I'll set up four ranges of dates for my column. The ranges I'm choosing are arbitrary. You can set whatever range you want.

To begin, change the default condition ("Text contains") to "Is between." This will cause two text fields to appear. You will put your ranges inside these boxes. For my first range, I want to set anything due this week to have a red background with white text.

So in the first box, I enter the formula for today:

=TODAY() +0

And in the second box, I'll enter:

=TODAY() +7

That covers all dates from today to seven days from now.

Then change the text and background colors in the dialog.

Click "Save rules" when you're done.

google sheets example 3
Step 3: Enter the range of dates using the same formula from last week's tutorial to define the dates.

 

Now I'll repeat this for three more date ranges.

First, click "+Add another rule" for each new date range you want to create.

google docs spreadsheet conditional formatting add rule
Step 4: Click "+Add new rule."

 

Then repeat the process you used earlier, making sure that none of your dates overlap. Set colors for each range individually. In my case, I'll use cooler colors for dates that are farther away (greens and blues), hotter colors for dates that are getting closer (reds and yellows).

Click Save rules when you're done.

google sheets example 4
Step 5: Repeat.

 

And here's the result.

google sheets example 5
Voila!

Next time around: extracting the date from a URL.

 

About the Author

David Nagel is the former editorial director of 1105 Media's Education Group and editor-in-chief of THE Journal, STEAM Universe, and Spaces4Learning. A 30-year publishing veteran, Nagel has led or contributed to dozens of technology, art, marketing, media, and business publications.

He can be reached at [email protected]. You can also connect with him on LinkedIn at https://www.linkedin.com/in/davidrnagel/ .


Featured

  • teacher and children working with a LEGO Education Science kit

    LEGO Education Debuts Science Kits for Hands-on Learning

    LEGO Education has announced a new learning solution to engage students in hands-on science learning. Available in three kits by grade band, LEGO Education Science provides 120-plus standards-aligned science lessons, teacher materials, and select LEGO bricks and hardware.

  • computer monitor with glowing digital data and graphs bursting out in an abstract, energetic explosion of lines and elements against a dark background

    New OpenAI Agent Turns ChatGPT into a Research Analyst

    OpenAI has unveiled a new "Deep Research" feature that enhances ChatGPT with the capabilities of a "research analyst" that automates time-consuming research by retrieving, analyzing, and synthesizing online information.

  • silhouetted human figures stand opposite a glowing digital brain, surrounded by abstract circuits and shadowy shapes

    Tech Execs Expect AI Advancements to Increase Security Threats

    Forty-one percent of tech executives in a recent international survey said they believe advancements in AI will significantly increase security threats. NetApp's second annual Data Complexity Report points to 2025 as "AI's make or break year."

  • outline of a modern school building as glowing blue geometric shapes, surrounded by binary code streams, with golden orbs and lines representing funding, set against a dark gray gradient with faint grid patterns

    FCC Cybersecurity Pilot Participants Selected

    The Federal Communications Commission has officially selected the participants for its Schools and Libraries Cybersecurity Pilot, the three-year program exploring the use of Universal Service funds to improve school and library defenses against cyber attacks.