Date formula with multiple conditions and nested ifs

Vbanoob98

Board Regular
Joined
Sep 13, 2019
Messages
94
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
Joined
Sep 13, 2019
Messages
94
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
Joined
Jun 12, 2014
Messages
33,742
Office Version
365
Platform
Windows
Test for C3>today before today-C3
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
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
Joined
Sep 13, 2019
Messages
94
Your formula works great K9, thanks a lot!!
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
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.
 

Forum statistics

Threads
1,085,586
Messages
5,384,598
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top