Formula based on date

[phillipcook]

Hi everyone

i am trying to figure out a way to do the following formula:
If(and(A1=“site 1”,or(C1=“monthly”,C1=“quarterly,C1=“yearly”)),B1,””)

this formula works, however I Have a date column in D and what I need to achieve is that if C1 has “quarterly”, the date needs to also be within the month of January or July and with “yearly” it has to be within the month of July.

has anyone got any suggestions in relation to the best method with this one?

 

[jtakw]

Hi,

Assuming your Real Date value in D1, try this:

=IF(AND(A1="site 1",OR(C1="monthly",AND(C1="quarterly",OR(MONTH(D1)={1,7})),AND(C1="yearly",MONTH(D1)=7))),B1,"")

 

[phillipcook]

That worked a treat!! I just had to add the iferror at the start to stop all the value error codes. You’re a legend mate!

 

[jtakw]

You're welcome.