Share via

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

carlos0314 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,666 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emi Zhang-MSFT 22,796 Reputation points Microsoft Vendor
    2021-02-01T08:57:29.66+00:00

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

    62370-291.png

    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.