Multiple nested if functions

[halefamily104]

Hello All,

I am working on a formula that returns text based on the date returned in a cell. I am getting a "there is a problem with this formula". I cannot figure out what the problem is. Here is the formula:

=IF(EH16=DATEVALUE("3/31/1900"),"Missing Date",""),IF(AND((TODAY()-EH16)>30,EI16=""),"Not Due"), IF(AND(EH16<TODAY(),EI16=""),"Overdue"), IF(EI16="","Not Done"), IF(EI16<>"","Current","")))))

Any ideas?

 

[Fluff]

Try it like

=IF(EH16=DATEVALUE("3/31/1900"),"Missing Date",IF(AND((TODAY()-EH16)>30,EI16=""),"Not Due", IF(AND(EH16<TODAY(),EI16=""),"Overdue", IF(EI16="","Not Done", IF(EI16<>"","Current","")))))

 

[halefamily104]

Beautiful Fluff! Thanks so much! That worked! I have a question. Everything is working fine except the "Not Due" portion:

=IF(EH16=DATEVALUE("3/31/1900"),"Missing Date",IF(AND((TODAY()-EH16)>30,EI16=""),"Not Due", IF(AND(EH16<TODAY(),EI16=""),"Overdue", IF(EI16="","Not Done", IF(EI16<>"","Current","")))))

The formula will identify the ones that are "Not Due" based on the date in EH16. If that date is greater than 30 days, it should return "Not Due". Is the formula correct?

 

[Fluff]

That will return "Not Due" if EI16 is blank & EH16 is more than 30 days in the past.

 

[halefamily104]

It should return it if its more than 30 days in the future. Did you mean future?

 

[halefamily104]

It should return it if its more than 30 days in the future. Did you mean future?

Because it is returning "Not Due" for dates that are overdue

 

[Fluff]

Did you mean future?

Nope, I meant past. For the future you need to subtract today from EH16

 

[halefamily104]

Nope, I meant past. For the future you need to subtract today from EH16

This is incorrect? IF(AND((TODAY()-EH16)>30,EI16=""),"Not Due"
Do I need to switch it around?

 

[Fluff]

Yes & Yes.

 

[halefamily104]

Switched and BEAUTIFUL! That worked! Thanks so much! Final result:

=IF(EH16=DATEVALUE("3/31/1900"),"Missing Date",IF(AND((EH16-TODAY())>30,EI16=""),"Not Due", IF(AND(EH16<TODAY(),EI16=""),"Overdue", IF(EI16="","Not Done", IF(EI16<>"","Current","")))))