Highlight Row Based on Date in Google Sheets (2 Suitable Ways)

If you need to highlight row based on a date in Google Sheets then you have come just to the right place! In this article, we will discuss in detail how you can highlight row based on a date in Google Sheets and can apply the learning in your daily life easily and effectively. We will use conditional formatting to highlight rows in this article.

Table of Contents

A Sample of Practice Spreadsheet

Highlight Row Based on Date

2 Methods to Highlight Row Based on Date in Google Sheets

To begin the process, we need to have a dataset with Dates on it. The date format we will be using is: MM/DD/YYYY. You can use any other format you like. We have a dataset with Item Name, Price, and Delivery Date. We will use this dataset to highlight row based on date.

1. Based on Today’s Date

We can use a custom formula in Conditional formatting to see if any cell contains today’s date and highlight the entire row easily.

First, select the range of tables where you want the highlight to take place. We select the range B5:D14.
Then, go to Menu bar > Format > Conditional formatting.

Now, you will see a sidebar appear from the right-hand side. The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:D14.

Next, from the Format rules tab from Format cells if box select Custom formula is option.

Then, in the Value or formula box type in the following formula:

=$D$5:$D$14 = TODAY()

Formula Explanation:

D5:D14 denotes the column of date. We used absolute cell references ($) to lock the column.
TODAY() specifies if it is indeed today’s date or not.

You can format the highlighted table further in the Formatting style You can select a custom font size and even select a preferred color. We select light orange 2 for our example.
Finally, press Done your rows are now highlighted based on today’s date.

This is what the final table looks like:

You can also highlight dates if the dates are before or today’s date by simply replacing = with < or > in the formula or typing in the following formulas:

For dates before today:

=$D$5:$D$14 < TODAY()

For dates after today:

=$D$5:$D$14 > TODAY()

Highlight Single Cell Based on Today’s Date

You can highlight only the cells of today’s dates using the default method in Google Sheets.

First, go to Menu bar > Format > Conditional formatting.

Now, you will see a sidebar appear from the right-hand side. The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:D14.

Next, from the Format rules tab from Format cells if box select Date is option.
Then, select today.

You can format the highlighted table further in the Formatting style You can select a custom font size and a preferred color. We selected light orange 2 for our example.
Finally, press Done and you can now see the cells are highlighted according to today’s date.

This is what the table looks like:

2. If the Date Is Between a Range of Dates

You can use a custom formula with Conditional formatting to highlight the entire row if a date is between a range of dates in Google Sheets. We need to introduce AND and DATE functions if we want to highlight rows with a date between a range of dates.

For this, we can use the same dataset used in the previous method.

Follow these steps:

First, select the range of tables where you want the highlight to take place. We select the range B5:D14.
Then, go to Menu bar > Format > Conditional formatting.

Now, you will see a sidebar appear from the right-hand side. The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:D14.

Next, from the Format rules tab from Format cells if box select Custom formula is option.

Then, type in the following formula:

=AND($D5<=DATE(2022,11,21),$D5>=DATE(2022,11,19))

Formula Explanation:

D5 denotes the column where the date is located. Notice how we partially locked D5.
2022,11,21(YYYY,MM,DD) and 2022,11,19(YYYY,MM,DD) denote the range of dates. If the said date is between this date range then the entire row will be highlighted.

Then, you can format the highlighted table further in the Formatting style. From there you can select a custom font size and a preferred color too. You can even select custom formatting options. We select light orange 2 for our example.
Finally, press Done and your rows are now highlighted based on today’s date.

This is what the final table looks like:

Similar Readings

Highlight Single Cell If Date Is Between a Range of Dates

You can highlight only the cells of dates between a date range using the default method in Google Sheets.

First, go to Menu bar > Format > Conditional formatting.

Now, you will see a sidebar appear from the right-hand side. The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:D14.

Next, from the Format rules tab from Format cells if box select is between option.

Then, in the first blank box type in the start range, and in the second blank box type the end range. We type 11/19/2022 and 11/21/2022 respectively.

Now, you can format the highlighted table further in the Formatting style bar. You can select a custom font size and even select a preferred color too according to your choice. We chose light orange 2 for our example.
Finally, press Done and you can now see the cells are highlighted according to the range specified.

This is what the table looks like:

Conclusion

In this article, we showed you how to highlight row based on a date in Google Sheets in two methods. We hope this article was useful to you. Keep practicing the methods we have shown here to better understand the concept.

Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.