Nest Formula

[Gordon22]

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

[#2]

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.

[#3]

@Gordon22try this

[#4]

@jitinm

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

[#5 · Accepted solution]

@Gordon22 

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

[#6]

Hi Quadruple_Pawn,

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

Many Thanks.

[#7]

@Gordon22try this

[#8]

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.

Thanks.

[#9]

Do let me know if there is any problem.