Nested IF AND Excel Formula

[lashears]

Hi everyone,

need some urgent help.

I have a nested excel formula that keeps giving me the error that I'm missing a parenthesis, but when I fix what I believe is missing then it throws the whole formula into error.

Basically my nested IF)AND) nested formula is as follows:



=IF(Y4<>"Yes",0,if(S4<DATEVALUE("1/31/2021"),210,If(and(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021"),175,If(and(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021"),140,If(and(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021"),105,If(and(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021"),70,If(and(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021"),35)





can anyone tell me why I keep receive the "missing parenthesis" error?



thanks for your help!

 

[jtakw]

So June 1st is singled out?
Please confirm, I'll modify my formula accordingly.

 

[jtakw]

This modified version only considers 6/2 to 6/11 (for the month of June), all others remain same:

=IFERROR(IF(Y4<>"Yes",0,IF(YEAR(S4)=2021,IF(AND(MONTH(S4)=6,DAY(S4)>=2,DAY(S4)<=11),35,CHOOSE(MONTH(S4),210,175,140,105,70)),"")),"")

 

[lashears]

This modified version only considers 6/2 to 6/11 (for the month of June), all others remain same:

=IFERROR(IF(Y4<>"Yes",0,IF(YEAR(S4)=2021,IF(AND(MONTH(S4)=6,DAY(S4)>=2,DAY(S4)<=11),35,CHOOSE(MONTH(S4),210,175,140,105,70)),"")),"")

Yes, and thank you so much for the modified formula

 

[jtakw]

You're welcome, happy to help.

So if you need to do the same for future years, you can change the IF(YEAR(S4)=2021 to 2022, etc.
Or, if you want it to be automatic every year change it to IF(YEAR(S4)=YEAR(TODAY())

Let me know if you need me to Post the entire formula.

 

[lashears]

You're welcome, happy to help.

So if you need to do the same for future years, you can change the IF(YEAR(S4)=2021 to 2022, etc.
Or, if you want it to be automatic every year change it to IF(YEAR(S4)=YEAR(TODAY())

Let me know if you need me to Post the entire formula.

hi, I have another addition to this formula. I've tried to work it out myself but i'm not sure how to do it. Basically now I need to add the dates September 8, 2020 to January 31, 2021 will receive the 210 reimbursement. So essentially, how do I add those dates in 2020 to the beginning part of the formula?

 

[jtakw]

Will you have dates that are Before 9/8/2020 ?

 

[lashears]

Will you have dates that are Before 9/8/2020 ?

Yes, I will it can go back to 2016 actually (that was recent information which is why I didn't include it in the initial message). I actually have a formula that works now, its a combo of yours and another:

=IF(Y2<>"Yes",0,IF(YEAR(S2)<2021,210,IF(YEAR(S2)=2021,CHOOSE(MONTH(S2),210,175,140,105,70,35),0)))


However, if you have another formula that would also work I would love to take a look at that one as well. I am trying to learn multiple ways to go about this

 

[jtakw]

I don't think that formula will work...?
That's why I ask if you will have Dates Before 9/8/2020
The formula you modified (from my Post #4) will result 210 for Any Date from 2016 to 2020 - Regardless of Month and Year (And you're not using my modified version in Post #12 to account for Only 6/2/2021 to 6/11/2021) ??

So I'm a little confused now regarding what you need.

 

[lashears]

I don't think that formula will work...?
That's why I ask if you will have Dates Before 9/8/2020
The formula you modified (from my Post #4) will result 210 for Any Date from 2016 to 2020 - Regardless of Month and Year (And you're not using my modified version in Post #12 to account for Only 6/2/2021 to 6/11/2021) ??

So I'm a little confused now regarding what you need.

Gotcha, so I still needed the formula for 6/2/21 to 6/11/2021, so I needed certain dates in 2021 still excluded. However, years prior to 2021 I just need those years included and not any dates in particular excluded (if that makes sense. I will write additional information if I need to be clearer, so no worries on that).

But that's why I modified the formula into more of a combo of yours and another formula, because of the variety I needed to account for

 

[jtakw]

I wrote Both of those formulas, in Post #4 and Post #12.

So you're saying Any date that is prior to 2021 gets 210, Not limited to 9/8/2020 to 12/31/2020, correct? Then:

=IFERROR(IF(Y4<>"Yes",0,IF(YEAR(S4)<2021,210,IF(AND(MONTH(S4)=6,DAY(S4)>=2,DAY(S4)<=11),35,CHOOSE(MONTH(S4),210,175,140,105,70)))),"")