SOLVED

Conditional Formatting and formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2109567%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109567%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20there%20is%20someone%20who%20could%20assist%20me%20with%20a%20worksheet%20that%20was%20created.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIt%20has%20conditional%20formatting%20and%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20conditional%20format%20is%20if%20you%20select%20every%202%20years%20and%20you%20enter%20the%20date%20in%20a%20column%20it%20turns%201%20of%204%20colours%20to%20show%20if%20you%20are%20expired%2C%20current%2C%20due%20soon%20or%20booked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20enter%20a%20date%20within%20the%20current%20date%20it%20turns%20the%20wrong%20colour.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20who%20could%20help%20me%20with%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2109567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110388%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3EThat%20would%20be%20amazing%20if%20you%20could%20assist%20me%20as%20I%20am%20only%20a%20rookie%20at%20this%20excel%20business.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109827%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949817%22%20target%3D%22_blank%22%3E%40nicola1505%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20conditional%20formatting%20works%20correctly%2C%20that%20due%20to%20logic%20in%20Planner%20Background%20sheet%20formula.%20It%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(OR('OHS%20Training%20Planner'!E10%3D%22%22%2C'OHS%20Training%20Planner'!%24B10%3D%22%22)%2C%22%22%2C%0A%20%20IF(TODAY()%3CDATE%3EDATE(YEAR('OHS%20Training%20Planner'!E10)%2CMONTH('OHS%20Training%20Planner'!E10)%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20VLOOKUP(E%245%2CLists!%24B%242%3A%24D%2411%2C2%2CFALSE)%2CDAY('OHS%20Training%20Planner'!E10))%2C%22Due%22%2C%0A%20%20IF(TODAY()%26gt%3BDATE(YEAR('OHS%20Training%20Planner'!E10)%2CMONTH('OHS%20Training%20Planner'!E10)%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20VLOOKUP(E%245%2CLists!%24B%242%3A%24D%2411%2C3%2CFALSE)%2CDAY('OHS%20Training%20Planner'!E10))%2C%22Soon%22%2C%0A%22Current%22)))))%3C%2FDATE%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFirst%20it%20check%20if%20data%20is%20not%20empty%20that's%20if%20TODAY()%20is%20less%20than%20date%20in%20E10.%20If%20so%20returns%20%22Booked%22%20and%20CF%20correctly%20change%20the%20color%20on%20purple.%3C%2FP%3E%0A%3CP%3EFrom%20my%20point%20of%20view%20formula%20is%20overcomplicated%2C%20in%20particular%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Einstead%20of%0AIF(TODAY()%3CDATE%3E%3C%2FDATE%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ECan't%20comment%20the%20rest%20since%20have%20no%20idea%20what%20is%20the%20business%20logic%20behind%20this%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109801%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949817%22%20target%3D%22_blank%22%3E%40nicola1505%3C%2FA%3E%26nbsp%3Btry%20to%20use%20in%20your%20conditional%20formatting%20the%20formula%20isnumber%20%2C%20to%20ensure%20that%20text%20input%20will%20be%20filtered%20out%2C%20as%20date%20is%20considered%20a%20number%2C%20try%20that%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109730%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109730%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949817%22%20target%3D%22_blank%22%3E%40nicola1505%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAh%2C%26nbsp%3B%20it%20looks%20like%20a%20lot%20of%20the%20conditional%20formatting%20formulas%20are%20misaligned.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20like%20I%20can%20take%20a%20look%20at%20fixing%20it%20up%20and%20then%20send%20you%20the%20file%20back.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20it's%20messed%20up%20too%20much%20I%20might%20have%20to%20just%20point%20you%20in%20the%20right%20direction%20and%20maybe%20someone%20you%20know%2C%20or%20someone%20here%20can%20assist%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWyn%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMVP%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUTC%2B8%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109592%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Bthanks%20for%20getting%20back%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20i%20enter%20the%20name%20into%20the%20column%20it%20changes%20to%20purple%20after%20entering%20the%20date%20and%20I%20need%20it%20to%20change%20to%20green%20as%20it%20is%20in%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109586%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109586%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949817%22%20target%3D%22_blank%22%3E%40nicola1505%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20conditional%20formatting%20rules%20require%20you%20have%20an%20entry%20in%20column%20B%20also%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20389px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250903i959D5BAFB4AEEFC2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250904i36D38D02389373CD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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
Best Response confirmed by nicola1505 (New Contributor)
Solution

Hi @nicola1505 

 

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

image.png

 

image.png

 

@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.

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

@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

@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.

@Wyn HopkinsThat would be amazing if you could assist me as I am only a rookie at this excel business.