How to Highlight Dates that are Earlier than in Google Sheets

You can format dates in many different ways in Google sheets. At the same time, you can also highlight dates with different rules such as the dates that are before or after a fixed date. This is very similar to Microsoft Excel and you can use the conditional formatting.

Step 1: Select the dates or the data range that you want to highlight;

Step 2: Click the "Format" tab from the ribbon;

Step 3: Click the "Conditional formatting" from the drop-down list;

Step 4: On the right of your spreadsheet, you will see the "Conditional format rule" panel. In the "Format rules" box, select "Date is before" and select "exact date" in the next box, then type the date or formula, e.g., "7/15/2018";

Step 5: If you want a different color from the default color, you can change the color in the "Formatting style" section;

Step 6: After you click the "Done" button, all the dates in the selected data range that are earlier than the date you entered should be in the proper color, e.g., green;

Step 7: Google sheets set up many rules for formatting dates, other than "date is before", you actually have many other rules such as "date is", "date is after". Each can combine with many different dates such as "today, "tomorrow", "in the last month" etc. Please select the proper rules for your data in the panel.

Step 8: If you want to clear the rules you set up, you can click the remove right after the rule in the panel;

Step 9: To remove all the formatting rules, click the "Format" tab from the ribbon again and click "Clear formatting" from the drop-down list.

Leave a Reply