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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
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
104
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
64,110
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
104
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
64,110
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
104
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
64,110
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,144,610
Messages
5,725,297
Members
422,607
Latest member
joce

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