Forum Discussion

nicola1505's avatar
nicola1505
Copper Contributor
Feb 01, 2021
Solved

Conditional Formatting and formulas

Hi there,   I was hoping there is someone who could assist me with a worksheet that was created. It has conditional formatting and formulas.   The conditional format is if you select every 2 y...
excel
Formulas and Functions
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Feb 01, 2021

    Hi nicola1505 

     

    The conditional formatting rules require you have an entry in column B also

     

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 01, 2021

nicola1505 

IMHO, conditional formatting works correctly, that due to logic in Planner Background sheet formula. It is

=IF(OR('OHS Training Planner'!E10="",'OHS Training Planner'!$B10=""),"",
  IF(TODAY()<DATE(YEAR('OHS Training Planner'!E10),MONTH('OHS Training Planner'!E10),DAY('OHS Training Planner'!E10)),"Booked",
  IF(OR('OHS Training Planner'!E$8="As Required",'OHS Training Planner'!E$8="Once"),"Current",
  IF(TODAY()>DATE(YEAR('OHS Training Planner'!E10),MONTH('OHS Training Planner'!E10)+
             VLOOKUP(E$5,Lists!$B$2:$D$11,2,FALSE),DAY('OHS Training Planner'!E10)),"Due",
  IF(TODAY()>DATE(YEAR('OHS Training Planner'!E10),MONTH('OHS Training Planner'!E10)+
            VLOOKUP(E$5,Lists!$B$2:$D$11,3,FALSE),DAY('OHS Training Planner'!E10)),"Soon",
"Current")))))

First it check if data is not empty that's if TODAY() is less than date in E10. If so returns "Booked" and CF correctly change the color on purple.

From my point of view formula is overcomplicated, in particular 

instead of
IF(TODAY()<DATE(YEAR('OHS Training Planner'!E10),MONTH('OHS Training Planner'!E10),DAY('OHS Training Planner'!E10))...

you may use
IF(TODAY()<'OHS Training Planner'!E10...

Can't comment the rest since have no idea what is the business logic behind this formula.

Resources