Forum Discussion

Fyse Jamali's avatar
Fyse Jamali
Copper Contributor
Apr 17, 2018
Solved

Advanced Conditional Formatting?

Hello,   I am looking to create a table with a list of individuals who have completed a specific training and have received a certificate for it. Let's say for example it is CPR Training. I visual...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 17, 2018

    Hi Fyse,

    Select your dates, in ribbon Conditional formatting, New rule, Use formula...

    Add formula

    =(EDATE($H5,24)>=TODAY())*(EDATE($H5,21)<=TODAY())

    and apply yellow format

    Ok and apply

    Repeat Conditional formatting -> Manage rule, New rule, etc, but with formula

    =(TODAY()>=EDATE($H5,24))

    Result is like this

    and attached.

    Sorry, I was wrong with previous variant of formulas, corrected.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 17, 2018

Hi Fyse,

Select your dates, in ribbon Conditional formatting, New rule, Use formula...

Add formula

=(EDATE($H5,24)>=TODAY())*(EDATE($H5,21)<=TODAY())

and apply yellow format

Ok and apply

Repeat Conditional formatting -> Manage rule, New rule, etc, but with formula

=(TODAY()>=EDATE($H5,24))

Result is like this

and attached.

Sorry, I was wrong with previous variant of formulas, corrected.

Fyse Jamali's avatar
Fyse Jamali
Copper Contributor
Apr 17, 2018

Hi Sergei,

 

For the longest time, I couldn't figure out why I was not getting any yellow boxes. I realized in my dates, I did not leave an example for a yellow cell. But, you adjusted the date to show it. Everything works perfectly! Thank you so much for this, I appreciate it!

 

Fyse

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 17, 2018

    You are welcome

Resources