# Date formula with multiple conditions and nested ifs

#### Vbanoob98

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP, Moderator
Test for C3>today before today-C3

#### Special-K99

##### Well-known Member
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

##### Board Regular
Your formula works great K9, thanks a lot!!

Last edited:

#### Special-K99

##### Well-known Member
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

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