Multiple IF with AND/OR criteria - what am I doing wrong?

[Dan_B]

Hi All

I have what I thought was a simple solution but it's not yielding the results I need and although I should, I can't figure out why

This if my formula

=IF(B2>"31/12/2020",IF(OR(F2="Yes",G2="Yes"),"31/03/2021",IF(B2>"01/01/2021",AND(OR(F2="Yes",G2="Yes"),"04/12/2020",B2+E2))))

What I want is, if B2 is less than 31/12/2020 and if column F or G contains yes then show 31/03/2021 otherwise if B2 is less 01/01/2021 and if column F or G contains yes then show 04/12/2020 otherwise add B2 and E2

Not sure what I've done wrong but it's only returning FALSE

Any help would be grand!

Thanks

Dan_B

[Glenn Kennedy]

If the dates are real dates... and not text values that look like dates:

=IF(B2>31/12/2020,IF(OR(F2="Yes",G2="Yes"),31/3/2021,IF(B2>1/1/2021,AND(OR(F2="Yes",G2="Yes"),4/12/2020,B2+E2))))

[Dan_B]

Hi Glenn

thanks for the reply but it still comes back false which I'm guessing means I didn't fulfill any of the criteria

I've attached the test file I'm using to see if that helps any?

Thank again

Dan_B

[Glenn Kennedy]

Hi. I may have been being stoopid.

=IF(B2 > DATE(2020,12,31),IF(OR(F2="Yes",G2="Yes"),DATE(2021,3,31),IF(AND(B2 < DATE(2021,1,1),OR(F2="Yes",G2="Yes")),DATE(2020,12,4),B2+E2)))

It looks like the AND was in the wrong place... Sample files always help... but an expected answer helps too. So is this correct??

[Dan_B]

Howdy - I think it's nearly there except the logic is doing the same as mine in that if the B2 < 01/01/2021 then it results in FALSE instead of checking if B2 < 01/01/2021 then check if there is a YES in F2 or G2 to return 04/12/2020 and if there isn't a YES in either then B2 + E2

Sorry, my explanations are not very good but basically anything that isn't >01/01/2021 regardless of whether there is a YES in the F or G column = FALSE

[Glenn Kennedy]

As it is set up in your sheet... what is the expected answer?

[Glenn Kennedy]

Another go... but head is slowly spinning....

=IF(AND(B2 > DATE(2020,12,31),OR(F2="Yes",G2="Yes")),DATE(2021,3,31),IF(AND(B2 < DATE(2021,1,1),OR(F2="Yes",G2="Yes")),DATE(2020,12,4),B2+E2))

[Dan_B]

Thank you so much Glenn, this last one worked perfectly!!

[Glenn Kennedy]

You're welcome and thanks for the rep.