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

  • glowing digital lock surrounded by futuristic dollar signs, stacks of currency, and coins, connected by neon circuit lines

    FCC Reports Strong Interest in Schools and Libraries Cybersecurity Pilot Program

    The Federal Communications Commission has received 2,734 applications totaling $3.7 billion in funding requests from schools, libraries, and consortia for its Schools and Libraries Cybersecurity Pilot Program, the agency reported in a recent announcement.

  • hand touching glowing connected dots

    Registration Now Open for Tech Tactics in Education: Thriving in the Age of AI

    Tech Tactics in Education has officially opened registration for its May 7 virtual conference on "Thriving in the Age of AI." The annual event, brought to you by the producers of Campus Technology and THE Journal, offers hands-on learning and interactive discussions on the most critical technology issues and practices across K–12 and higher education.

  • futuristic crystal ball with holographic data projections

    Call for Opinions: 2025 Predictions for Education IT

    How will the technology landscape in education change in the coming year? We're inviting our readership to weigh in with their predictions, wishes, or worries for 2025.

  • computer with a red warning icon on its screen, surrounded by digital grids, glowing neural network patterns, and a holographic brain

    Report Highlights Security Concerns of Open Source AI

    In these days of rampant ransomware and other cybersecurity exploits, security is paramount to both proprietary and open source AI approaches — and here the open source movement might be susceptible to some inherent drawbacks, such as use of possibly insecure code from unknown sources.