Conditional Formatting in Google Sheets is a useful feature to differentiate between datasets based on alphabets, numbers, and dates. To highlight the datasets by a certain date range using conditional formatting in Google Sheets, you need to go to the “Format” tab of the main menu and select “Conditional formatting”.
If you wish to highlight cells containing the dates before a certain day or within a date range, you are at the right place.
In this article, let us discuss Conditional Formatting by dates in Google Sheets. We will use dates to create rules for highlighting the cell values. Make sure to read the article until the end because this guide is very straightforward.
You may have used various date functions such as the DATE, DATEVALUE, and WEEKDAY by Google Sheets. But now is the time to take your expertise to the next level using Conditional Formatting.
We are about to discuss the following scenarios. Click on the links to jump to that section,
- Highlight a single date using Conditional Formatting
- Highlight date range before and after the given date using Conditional Formatting
- Highlight dates within the given date range, e.g. 7 days (week) or 30 days (month)
Explained: Conditional Formatting in Google Sheets
As discussed earlier, conditional formatting allows you to differentiate datasets based on dates by changing the font type and color and cell background color.
To access this feature in Google Sheets, follow the steps below.
- Open the Google Sheets
- Hover to the main menu and click on “Format” tab
- Choose the “Conditional formatting” option from the popup
A new dialog box will appear on the right side of the screen, as shown below,
The Conditional Formatting dialog box is divided into two types: Single color and Color scale.
In the Single color conditional formatting, the cell values that match the criteria are highlighted by a single formatting style, e.g. font color or cell background color.
On the other hand, the Color scale lets you divide the cell values of the entire column into three categories: Minpoint, Midpoint, and Maxpoint.
Here is an example of color-scale conditional formatting where all the cells are categorized into three colors.
Now, let’s discuss the Format rules of the Single color option.
You have two options here; the first lets you create a rule based on certain conditions.
The second one allows you to change the formatting of the matching cell values.
Here is how you can format cells,
- Change the text style to Bold and Italic
- Underline the text
- Apply Strikethrough to the text
- Change the text color
- Change the cell background color
By default, the formatting is applied to change the cell background color to light blue/green.
Download the Example Google Sheet
You can download or copy the following Google Sheet that contains the spreadsheets and datasets we will use to demonstrate in this tutorial.
If you have your own dataset ready to practice the things we discuss in the following sections, you can skip downloading the above file.
How to Use Conditional Formatting Dates in Google Sheets
CASE #1: Highlight A Single Date
Let us consider a simple scenario where we wish to highlight the exact date from a column.
- Open the Google Sheet
- Hover to the date column and select all the cells with the dates
- Go to the main menu and click on the “Format” tab
- Choose “Conditional formatting”
- Click on the dropdown below the “Format cell if..” option
- Select “Date is”
- A new dropdown will appear, as shown below
- Click on that and select “exact date”
- A new empty box will be displayed. Here, you can put the date you wish to highlight. (Make sure you use the same formatting as used in the dates column)
- By default, the Formatting style is set to highlight the cell background to light green. You can change it to your preference. For now, we will keep it as is
- Next, click on the “Done” button in the green
CASE #2 – Highlight Multiple Dates before a given Date
We will learn to highlight dates that fall before the given date. Let’s assume we plan to highlight everything before January 15, 2023 (1/15/2023).
- Select all the cells containing the dates
- Chose the “Conditional formatting” option from the “Format” tab of the main menu
- Click on the dropdown below the “Format cell if..” option
- Select “Date is before”
- A new dropdown will appear; click on that and select “exact date”
- Enter the date “1/15/2023” in the empty box as shown below
- Choose the Formatting style. By default, it is set to change the cell background to light green color. We’ll keep it as is
- Click on the “Done” button
You can use these same steps to highlight the date after the given date, which is January 15, 2023 (1/15/2023). All you need to do is select the “Date is after” option when you click the dropdown below the “Format cell if..” option. Refer to the following image.
Case #3 – Conditional Formatting Dates between the given Date Range (7 or 30 days)
Consider the following example data, representing the employees and their hiring dates from January to February 2023.
Let’s highlight the hiring dates from January 2023 to see the count of employees hired in the same month.
- Select all the cells containing the dates
- Chose the “Conditional formatting” option from the “Format” tab of the main menu
- Click on the dropdown below the “Format cell if..” option
- Choose “Is between”
- Two new empty boxes will appear, as shown below
- Enter the start date for the January month as “1/1/2023” in the first box
- Next, in the second box, enter the end date for January month as “1/31/2023”
- Choose the Format style. By default, it is set to change the cell background to light green color. We’ll go with the default settings
- Click on the “Done” button in the green
The above steps can be used to highlight the dates between any given date range. You need to ensure that while entering the dates to create the rule, you follow the same date formatting as available in the dates column of the table.
Conclusion
It is very easy to differentiate between the dates that are from the past week, month or year using conditional formatting.
Don’t forget to explore all available choices from the dropdown below the “Format cell if..” option.
If you are still stuck somewhere or need help using the Conditional Formatting in Google Sheets. Please comment below. I will try my best to answer your questions as soon as possible.