Nested statement help

[jdub11]

I am trying to create a nested IF statement to look at a cell and do 3 things:

1. look to see if cell is blank, if so leave blank,
2. date in cell- todays date is < 30 print" DUE" in cell.
3. look to see if cell has NA typed in the cell, if so leave blank

My code works in terms of the blank, and print DUE, but I get #value! when source cell has NA.

Please help!!

=IF(AND('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$AB9=""),"", IF(AND('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$AB9-TODAY()<30), "Due", IF(AND('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$AB9<>""),"")))

 

[Alex Blakenburg]

I am not sure that your Due logic is correct but start with this.
If you don't eliminate the NA before you try to subtract today from AB9 then it will be trying to subtract the text NA and cause your error.
Also your ANDs are not doing anything.

=IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$AB9="","", 
        IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$AB9="NA","",
             IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$AB9-TODAY()<30, "Due", "")))

 

[jdub11]

Perfect!!! Thank you so much. I tried for hours with this and it was indeed the order. Super big thanks!

 

[Alex Blakenburg]

No problem. Thanks for letting me know and glad I could help.

 

[jdub11]

Hello, I tried to add this last line IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<1,"OVER",""
And I am getting an error for too many arguments.

=IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="","",IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="NA","", IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<45, "<45","", IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<1,"OVER",""))))

 

[Alex Blakenburg]

Hello, I tried to add this last line IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<1,"OVER",""
And I am getting an error for too many arguments.

=IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="","",IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="NA","", IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<45, "<45","", IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<1,"OVER",""))))

1) The order in IF statements definitely matters.
It is never going to get to <1 because <45 will kick in before that.
2) You had an additional ,"" in the "<45", so that made your final If statement an additional parameter.

Try this:

=IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="","",
         IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="NA","", 
                    IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<1,"OVER",
                              IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<45, "<45",""))))

 

[bebo021999]

I guess you want to get days between cell D5 and today's date, with follow pattent:
<1 (in the past) => "OVER"
1 to 29 days: "DUE"
30 to 44 days: "<45"
45 days up: ">45"

=IFERROR(CHOOSE(MATCH(TRUE,'[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<{1,30,45},0),"OVER","DUE","<45"),">45")

 

[jdub11]

Yes that worked. So my issue was too many false values right? Or was it also the order?

 

[jdub11]

1) The order in IF statements definitely matters.
It is never going to get to <1 because <45 will kick in before that.
2) You had an additional ,"" in the "<45", so that made your final If statement an additional parameter.

Try this:

=IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="","",
         IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5="NA","",
                    IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<1,"OVER",
                              IF('[EUCOM Revised SEMA Training Matrix 20220219.xlsx]Conditional '!$D5-TODAY()<45, "<45",""))))

View attachment 59095

Yes that worked. Was it the just the order, or also the "" for the false answer?

 

[Alex Blakenburg]

The "too many arguments" was the "" for the false answer.