Nested statement help

jdub11

New Member
Joined
Feb 22, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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<>""),"")))
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,590
Office Version
  1. 365
Platform
  1. Windows
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.


Excel Formula:
=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", "")))
 
Solution

jdub11

New Member
Joined
Feb 22, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Perfect!!! Thank you so much. I tried for hours with this and it was indeed the order. Super big thanks!
 

jdub11

New Member
Joined
Feb 22, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,590
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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",""))))

1646206536319.png
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
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"

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

jdub11

New Member
Joined
Feb 22, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Yes that worked. So my issue was too many false values right? Or was it also the order?
 

jdub11

New Member
Joined
Feb 22, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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?
 

Forum statistics

Threads
1,181,212
Messages
5,928,741
Members
436,626
Latest member
SavvyOne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top