How do I use 3-color gradient scale for experation dates for future dates in multiple cells?

Question

Okay, here is the situation: Lets say I have a group of employees that have certain certificates that are set to expire. Lets say in cell A2 I have the date of the certificate; depending on the course taken, these certificates will expire in 2 or 4 years.

In cell B2 I have the number 2 or 4 to correspond with the year of expiration.

What I am trying to do is to have the cell in A2 gradually change color when it is close to expiration date using the 3 color gradient scale. I am also trying to manipulate the cell to change its value if I enter either a 2 or a 4 in cell B2 (next to the date cell). I also want to apply this to multiple cells and not just dependent on that specific cell. Any Ideas would be great.

Answer 1

Hi @carlos0314 ,
I create a sample about your requirement, please check if this will help you:

The left days <730 (Red):

=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))-TODAY()<730

The left days >=730 Days, <1095 Days (Yellow):

=AND(DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))-TODAY()>=730,DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))-TODAY()<1095)

The left days >=1095 Days (Green):

=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))-TODAY()>=1095

Any unclearly please let me know and I'm glad to help you.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.