Conditionally Formatting Cells Based on Date Differences in Google Sheets
As a spreadsheet user, you may have encountered the need to highlight cells based on certain conditions. One common use case is to highlight cells based on the number of days that have passed since a specific date. In this blog post, we will explore how to conditionally format cells in Google Sheets based on the difference between two dates.
Background
———-
Em_CD, a user on November 22, 2023, asked for help with conditionally formatting cells based on the number of days that have passed since the “Date of Last Contact”. The goal is to highlight cells in different colors based on the following criteria:
* Green for 0-3 days
* Yellow for 4-9 days
* Red for 10 days or more
The challenge is that most online tutorials use the =TODAY() function, which does not quite suit their needs. In this blog post, we will explore a solution to this problem using Google Sheets’ built-in functions and formatting options.
Solution
———-
To achieve the desired result, we can use the =EDATE() function to calculate the difference between the “Date of Last Contact” and today’s date. Here’s the formula for each color condition:
* Green (0-3 days): =EDATE(A2, 0) < TODAY()
* Yellow (4-9 days): =EDATE(A2, 1) < TODAY()
* Red (10+ days): =EDATE(A2, 2) < TODAY()
Here’s how the formula works:
* EDATE(): Calculates the number of days between two dates. In this case, we use A2 as the “Date of Last Contact” and today’s date as the starting point for the calculation.
* TODAY(): Returns today’s date.
* <: The less than operator is used to compare the result of the EDATE() function with today's date. If the difference is within the desired range, the cell will be highlighted accordingly.
Once you have created these formulas, you can apply them to the entire column or select specific cells that you want to format. To do this, follow these steps:
1. Select the cells you want to format.
2. Go to the “Home” tab in the top menu.
3. Click on the “Conditional Formatting” button in the “Styles” section.
4. Select “New Rule” from the drop-down menu.
5. In the “Format cells if” dropdown, select “Custom formula is”.
6. Enter the formula you created earlier (EDATE(A2, 0) < TODAY() for green, EDATE(A2, 1) < TODAY() for yellow, and EDATE(A2, 2) < TODAY() for red).
7. Click “Done” to apply the formatting.
Tips and Variations
———————
Here are some tips and variations to help you refine your conditional formatting rules:
* Use named ranges or references to make your formulas more readable and maintainable. For example, instead of using A2 as the “Date of Last Contact”, you can use a named range like “LastContact”.
* You can also use multiple conditions in your formulas to highlight cells based on more complex criteria. For example, you could use a combination of date ranges and other conditions to highlight cells that meet specific requirements.
* If you want to highlight cells based on the number of days since a specific date, rather than today’s date, simply change the starting point of the EDATE() function. For example, if you want to highlight cells based on the number of days since January 1, 2023, use =EDATE(A2, -295) < TODAY().
* You can also use different formatting options, such as colors, fonts, and backgrounds, to create more visually distinct highlighting.
Conclusion
———-
In this blog post, we explored how to conditionally format cells in Google Sheets based on the difference between two dates. We demonstrated a solution using the =EDATE() function and the less than operator to highlight cells in different colors based on the number of days that have passed since the “Date of Last Contact”. By following these steps and tips, you can create more sophisticated conditional formatting rules to suit your specific needs.