In a spreadsheet, a highlighted cell can provide you enough information about the content of that cell. It can alert you about an important date, a milestone or something similar. Before going to explain you how to highlight cells based on expiry date in Google Sheets, let me explain where I’m using it.
I’m maintaining a Google Sheets file to record my personal income, daily expenses, and also some other important information like the due date of my insurance premium, my domain and hosting plan expiry dates etc. I am intelligently using conditional formatting in certain cells in that sheet to easily check the expiry dates of my domain registration, insurance premium etc. There I’ve applied conditional formatting rules to highlight cells with two different colours based on the expiry duration.
I’ve set the cell colour to Green if the expiry dates are after one month. But if the expiry dates are within one month, I’ve set the highlighting to Red. It helps me a lot. I don’t need to check all the dates individually. At a glance I can find that whether I’m safe with my registration dates.
Then how to highlight cells based on expiry date in Google Sheets? That you are going to learn here. A simple but tricky conditional formatting rule that using Google Sheets EDATE function can do the trick.
How Can this Expiry Date Based Conditional Formatting Useful to You?
You can follow the below expiry date related conditional formatting in Google Sheets in many cases. To name few;
- To visually identify the expiry dates of your company registration certificates or documents.
- Track your or your employees Passport, Driving License expiry.
- Highlight expiry dates of your kids’ school fees.
- CC due.
- Follow your Insurance Premium due date.
- Strictly make the loan repayments in time.
- For bloggers dealing with lots of blogs, pay the hosting plan as well as domain charges before expiry.
There are such countless situations where you can apply the expiry duration related conditional formatting.
Timely payment of any fees or service charges can not only save your money but also can improve your trustworthiness.
You May Also Like: Role of Indirect Function in Conditional Formatting
Tips to Highlight Cells Based on Expiry Date in Google Sheets
As told, we can use Google Sheets EDATE function here. I’ve already a detailed tutorial on Google Sheets All Date Related Functions. Still, I’m explaining the EDATE function again here.
I will explain you how to use EDATE in Google Sheets with one simple example. So no need for you to check my above tutorial now.
Cell A1 contains date 31/12/2017. See the below EDATE function and what it returns.
Similarly this function can also return a date before the referred date.
Now time to back to our tutorial. Let’s see how I’m going to use EDATE function to highlight Cells based on expiry date in Google Sheets.
Method One: Expiry Date Based Conditional Formatting
I am applying this conditional formatting in Column A. Jump to the below screenshot and you can see I’ve set three custom rules there from the Format menu conditional formatting.
Here I want to highlight all the cells in Column A with Red colour if the dates are coming within one month. If any of the dates are exactly one month from today, I want to highlight those cells with Sky Blue colour and more than one month, with Green Colour.
If any of the cell is highlighted with Red Colour, it means the due date is within one month. So I should take care of that payment. If it’s Green, I can simply ignore such cells.
Explanation to the Rules Applied (again see the screenshot below)
If today’s date is 27/01/2018;
1. Wherever the dates are less than 27/02/2018 (that means within one month), the formula (formatting rule) would highlight those cells in Column A with Red colour.
2. If any of the date in Column A is 27/01/2018, that cell would be highlighted with Sky Blue colour.
3. If any of the dates in Column A are greater than 27/02/2018, the formula would highlight those cells in Column A with Green colour.
This way you can highlight cells based on expiry date in Google Sheets. The above conditional formatting is based on one month cut off date. Now if you want to set 6 months cut off date, just change the custom formula as below.
Method 2: Expiry Date Based Conditional Formatting
The above conditional formatting rules have certain limitations. You can only set one cut off date and test whether the dates are below that, equal to that or above that. If you want to enter more conditions to highlight cells based on expiry Date in Google Sheets, follow the below steps.
In column D, I have applied the conditional formatting based on future expiry dates. Here all the custom formulas test Column D with today’s date.
If the any of the date in this column is within one month, it fills one colour, if within two months, another and so on. I’ve already set 6 different colours for 6 months. Hope you could understand the procedure.