Formula for Status - Not Due, Due, Complete, Overdue

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
137
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello peeps,

I have a formula to return whether something is not due, due, complete or overdue:

Where B1 is "Completion Date", and A1 is "Due Date":

=IF($B1<>"","Complete",IF(ISBLANK($B1),IF(TODAY()<$A1,"Not Due",IF(TODAY()=$A1,"Due",IF(TODAY()>$A1,"Overdue","")))))

This is working perfectly when a date is entered in either A or B, but is returning 'Overdue' if both cells blank. What am I doing wrong? :unsure:

And can I extend the formula to show if it's "0-3 months overdue", "4-6 months overdue" and "7+ months overdue" (I may have to add another column or conditionally format to show this?)?

Ta muchly!
Sara
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,982
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(A1="","",IF($B1<>"","Complete",IF(TODAY()<$A1,"Not Due",IF(TODAY()=$A1,"Due",IF(TODAY()>$A1,"Overdue","")))))
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
137
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perfect! Thanks, @Fluff!

Can I extend the formula to include if it's "0-3 months overdue", "4-6 months overdue" and "7+ months overdue"? May be too many arguments...?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,982
Office Version
  1. 365
Platform
  1. Windows
Yes you can extend it to do that, it's well within the limits of nested functions.
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
137
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Sorry, @Fluff - what would the formula look like? I keep tying myself up in knots with TODAY() plus the timescale - it's not as easy as TODAY()+0-3 MONTH>A1,"Overdue 0-3 months", is it (I don't think it is!)?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,982
Office Version
  1. 365
Platform
  1. Windows
You can do it like
VBA Code:
=IF(A1="","",IF($B1<>"","Complete",IF(TODAY()<$A1,"Not Due",IF(TODAY()=$A1,"Due",IF(EDATE(TODAY(),-3)<$A1,"0-3 months overdue",IF(EDATE(TODAY(),-6)<$A1,"4-6 months overdue",""))))))
 
Solution

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
137
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You are amazing! I bow down to you and am grateful for the teachings! Thank you, @Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,982
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,463
Messages
5,831,783
Members
430,088
Latest member
meagerd

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