 SOLVED

# 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

8 Replies

# Re: Nest 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.

# Re: Nest Formula

@Gordon22try this

# Re: Nest Formula

@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

best response confirmed by Gordon22 (Occasional Contributor)
Solution

# Re: Nest Formula

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

# Re: Nest Formula

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

Many Thanks.

# Re: Nest Formula

@Gordon22try this

# Re: Nest Formula

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.

# Re: Nest Formula

Do let me know if there is any problem.