Date Formula & Conditional formating

[CokZz]

Hi Expert,

I need help to make 2 conditions in conditional formatting
I'm using google-sheet, the formula I'm using is

=ARRAY_CONSTRAIN(ARRAYFORMULA(if(K6:K791="",,30+K6:K791)), 786, 1) ,, download to excel and change to → =IF(K6:K791="",,30+K6:K791)

=AND($L6<TODAY(),$L6<>"") → for conditional formating

error:

1. why does the formula only work until row 791? I did change it to 1374 but the formula & conditional still doesn't work.
2. what formula can I use to cancel/undo the first conditional (yellow highlight + red font) whenever Column M is populated/filled by date (so the font & highlight is back to normal)
3. rows 21 to ~ are hide

Thanks,
CoKZz

[CokZz]

here the attachment

[AliGW]

Change the Applies To box setting to this: =$B$6:$N$2374

[CokZz]

Hi Ali,

Can you please let me know how to cancel conditional formatting =AND($L6<TODAY(),$L6<>"") if column M filled by date <=L6 ?

[AliGW]

Change the rule to this:

=AND($M6>$L6,$L6<TODAY(),$L6<>"")

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[CokZz]

Hi ali,

I change to M6="",

=AND($M6>$L6,$L6<TODAY(),$L6<>"")

for now, is clear

thank you!

[AliGW]

OK - happy to help.