Date formula with multiple conditions and nested ifs

[Vbanoob98]

Hi,

I need a formula that tells me 4 options based on a date in C3:

Overdue= the date has passed
Approaching= date is 5 days away
Pending= normal status
Complete: if A1 is = Yes then Complete

What I currently have is this :

=If(TODAY()-C3<=5, "Aproaching","Pending")

I'd be really grateful for any help thanks!

 

[Vbanoob98]

The new formula I have is:

=If(A1=1,"complete",if(today()-c3<=5,"approaching",if(c3>today(),"Overdue","Pending")))

Everything works except for the Overdue part, it gets stuck at approaching

 

[Fluff]

Test for C3>today before today-C3

 

[Special-K99]

Contradiction here, so you need to sort it out

if A1 is = Yes then Complete
If(A1=1,"complete"


Try (untested)
=IF(A1=1,"Complete",IF(AND(C3>0,C3<=TODAY()),"Overdue",IF(AND(C3>0,C3<=TODAY()+5),"Approaching","Pending")))

 

[Vbanoob98]

Your formula works great K9, thanks a lot!!

 

[Special-K99]

I entered C3 > 0 into the formula so the check is only performed if a value is present in C3, which presumably would be a date and nothing else.
If C3 is left blank and not checked then the wrong error may result.

 

[Vbanoob98]

If theres no value it just says "Pending" which is good! Thanks