Forum Discussion

BigAaron91's avatar
BigAaron91
Copper Contributor
May 14, 2024

Help creating a training matrix! Please

Hi everyone, 

 

I hope someone can help me out.

I'm wanting to creat a spreadsheet that acts as a training matrix, for the staff in my team.

 

What I would like to do is list what training staff have undertaken - when they finished the course and when the qualification expires.

 

However! I'd love to be able to have the cells change colour depending on  how much time they have left before that qualification expires.

 

For example, if a qualification expires in a month then cell turns orange.

Or if there is over 6 months before it expires, the cell stays green.

So essentially the cell changes according to how much time you have left.

 

Hopefully that makes sense.

Does anyone have any smart ideas or suggestions? 

 

Many, many thanks in advance!!

Admin
Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 14, 2024

    BigAaron91 

    Let's say the training name is in D2 and down, and the expiration date in F2 and down.

    Select D2 and down, or if you want to color multiple columns, the entire range that you want to format, starting in row 2.

    The active cell in the selection should be in row 2.

    Select green as fill color. This will be the default.

     

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =$D2<EDATE(TODAY(), 6)

    Click Format...
    Activate the Fill tab.
    Select yellow as fill color.
    Click OK, then click OK again.

     

    Repeat these steps, but with the formula

    =$D2<EDATE(TODAY(), 1)

    and orange as fill color.

     

    Repeat them again with the formula

    =$D2<TODAY()

    and red as fill color.