SOLVED
Home

Conditional Formatting Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-727670%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-727670%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%2C%3C%2FP%3E%3CP%3EI%20have%20what%20is%20probably%20a%20really%20obvious%20conditional%20formatting%20formula%20query.%26nbsp%3B%20Basically%2C%20i%20want%20a%20row%20to%20turn%20yellow%20if%20the%20date%20in%20one%20row%20(J)%20is%20today%20or%20earlier%2C%20but%20I%20then%20want%20to%20override%20this%20and%20highlight%20the%20row%20in%20a%20different%20colour%20once%20another%20row%20(U)%20is%20populated%20with%20text%20(%22complete%22%20the%20row%20will%20turn%20grey%2C%20%22cancelled%22%20the%20row%20will%20turn%20red%20-%20if%20blank%20or%20%22in%20progress%22%20it%20remains%20yellow).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20set%20up%20the%20formulas%20(so%26nbsp%3B%3D%24U8%3D%22cancelled%22%20format%20red%20%2F%26nbsp%3B%3D%24U8%3D%22complete%22%20format%20grey%20%2F%20%3D%24J8%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3CP%3EThe%20issue%20is%2C%20the%20U8%20rules%20do%20take%20precedence%20and%20highlight%20the%20rows%20in%20the%20correct%20colour%20but%20with%20the%20exception%20of%20the%20date%20reference%20cell%20in%20J%20which%20stays%20yellow.%26nbsp%3B%20I%20tried%20changing%20it%20to%20an%20AND%20formula%20(to%20apply%20if%20U8%20is%20blank%20or%20has%20other%20content)%20but%20then%20it%20just%20stays%20white%20when%20cancelled%20or%20complete%20is%20applied.%3C%2FP%3E%3CP%3EI%20am%20sure%20this%20is%20so%20simple%2C%20but%20if%20anyone%20could%20point%20me%20in%20the%20right%20direction%3F%3C%2FP%3E%3CP%3ETrish%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-727670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728194%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368176%22%20target%3D%22_blank%22%3E%40trishmci%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Trish%2C%3C%2FP%3E%0A%3CP%3EPerhaps%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20743px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121590i814FA1695E5303BF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20yellow%20rule%3C%2FP%3E%0A%3CPRE%3E%3D(%24J8%26lt%3B%3DTODAY())*NOT(ISBLANK(%24J8))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728640%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%2C%20though%20that%20doesn't%20seem%20to%20work%20either.%3C%2FP%3E%3CP%3ESeemed%20perfect%20on%20your%20version%20though%20-%20i%20must%20be%20making%20an%20error%20with%20the%20formula%20somewhere.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729159%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368176%22%20target%3D%22_blank%22%3E%40trishmci%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729214%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much%2C%20it%20was%20driving%20me%20mad%20-%20that%20seems%20to%20work%20perfectly.%26nbsp%3B%20Not%20sure%20what%20i%20did%2C%20but%20really%20appreciate%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729222%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368176%22%20target%3D%22_blank%22%3E%40trishmci%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
trishmci
New Contributor

Hi There,

I have what is probably a really obvious conditional formatting formula query.  Basically, i want a row to turn yellow if the date in one row (J) is today or earlier, but I then want to override this and highlight the row in a different colour once another row (U) is populated with text ("complete" the row will turn grey, "cancelled" the row will turn red - if blank or "in progress" it remains yellow).  

I have set up the formulas (so =$U8="cancelled" format red / =$U8="complete" format grey / =$J8<TODAY() format yellow), and also set up the rule preference with the date at the bottom. 

The issue is, the U8 rules do take precedence and highlight the rows in the correct colour but with the exception of the date reference cell in J which stays yellow.  I tried changing it to an AND formula (to apply if U8 is blank or has other content) but then it just stays white when cancelled or complete is applied.

I am sure this is so simple, but if anyone could point me in the right direction?

Trish

5 Replies

@trishmci 

 

Hi Trish,

Perhaps like

image.png

with yellow rule

=($J8<=TODAY())*NOT(ISBLANK($J8))

@Sergei Baklan Thank you, though that doesn't seem to work either.

Seemed perfect on your version though - i must be making an error with the formula somewhere.....

 

Solution

@trishmci 

Please check attached

@Sergei Baklan thank you so much, it was driving me mad - that seems to work perfectly.  Not sure what i did, but really appreciate your help!

@trishmci , you are welcome, glad to help

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies