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

1 Reputation point
2021-01-29T19:03:53.59+00:00

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.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,660 questions

1. 22,556 Reputation points Microsoft Vendor
2021-02-01T08:57:29.66+00:00

Hi @carlos0314 ,

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