Jan 31 2021 07:52 PM
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?
Jan 31 2021 08:19 PM
SolutionJan 31 2021 08:24 PM
@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.
Feb 01 2021 01:06 AM - edited Feb 01 2021 01:07 AM
Hi @nicola1505
Ah, it looks like a lot of the conditional formatting formulas are misaligned.
If you like I can take a look at fixing it up and then send you the file back.
If it's messed up too much I might have to just point you in the right direction and maybe someone you know, or someone here can assist
Wyn
MVP
UTC+8
Feb 01 2021 02:46 AM
@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
Feb 01 2021 03:24 AM
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.
Feb 01 2021 01:03 PM
@Wyn HopkinsThat would be amazing if you could assist me as I am only a rookie at this excel business.
Jan 31 2021 08:19 PM
Solution