Forum Discussion
Nest Formula
Hi,
I am using a formula in a cell:-
=IF(AND(AC$1>=$A83,AC$1<=$B83),"1","")
It tests the dates entered into 2 row cells (start and stop dates), against a date on each colunm, if true it enters a 1, if not it does nothing. I then conditionally format the cells if it is 1.
But, i also want to add a test to see if the word "holiday" is in a notes field next to the dates. I can use this:-
=IF(COUNTIF(C82,"*holiday*"),"2","0") the C82 is just my test row,
It works fine on it's own, the 2 is then used to conditionally format a different colour.
But i want to combine the two, then use the 1
=IF(AND(D$1>=$A2,D$1<=$B2,COUNTIF($C2,"*holiday*")>0),2,IF(AND(D$1>=$A2,D$1<=$B2),1,""))Maybe with this formula.
- Not sure why the end of this was removed, i want to combine the two formulas, then conditionally format based on the cell being 1 or 2 (or no formatting if neither is true).
Thanks for any pointers.Hi Jitinm,
Thank you for your test, i can't seem to modify this to see it working in my example, also i need to get "holiday" to be checked even within other text.
Below is what i am trying to do - i used =IF(AND(D$1>=$A3,D$1<=$B3,$C3="holiday"),1,0)
01/06/22 02/06/22 03/06/22 04/06/22 05/06/22 02/06/2022 03/06/2022 test 1 1 1 01/06/2022 04/06/2022 test 2 1 1 1 1 03/06/2022 05/06/2022 test 3 holiday 2 2 2 Sorry - i can seem to upload the sample i did!
Thanks
