Nest Formula

Copper Contributor


I am using a formula in a cell:-




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

8 Replies
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.

@Gordon22try this



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)


02/06/202203/06/2022test 1 11  
01/06/202204/06/2022test 21111 
03/06/202205/06/2022test 3 holiday  222


Sorry - i can seem to upload the sample i did!





best response confirmed by Gordon22 (Copper Contributor)



Maybe with this formula. 

Hi Quadruple_Pawn,

Yes - this got it. I will go through the format carefully to see if i can follow.

Many Thanks.

@Gordon22try this

Hi Jitinm,

Yes, that what i was looking for - i will need to go through these and see if i can follow, but the function is correct, thank you for taking the time.

Do let me know if there is any problem.