Forum Discussion
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 years and you enter the date in a column it turns 1 of 4 colours to show if you are expired, current, due soon or booked.
When I enter a date within the current date it turns the wrong colour.
Anyone who could help me with this?
6 Replies
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.
Wyn Hopkins thanks for getting back to me.
When i enter the name into the column it changes to purple after entering the date and I need it to change to green as it is in date.
nicola1505 try to use in your conditional formatting the formula isnumber , to ensure that text input will be filtered out, as date is considered a number, try that
