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

#### SaraWitch

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

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
Excel Formula:
``=IF(A1="","",IF(\$B1<>"","Complete",IF(TODAY()<\$A1,"Not Due",IF(TODAY()=\$A1,"Due",IF(TODAY()>\$A1,"Overdue","")))))``

#### SaraWitch

##### Board Regular
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
Yes you can extend it to do that, it's well within the limits of nested functions.

#### SaraWitch

##### Board Regular

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
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",""))))))``

#### SaraWitch

##### Board Regular
You are amazing! I bow down to you and am grateful for the teachings! Thank you, @Fluff.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
6
Views
815
Replies
7
Views
117
Replies
7
Views
806
Replies
1
Views
174
Replies
11
Views
2K

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.

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.

### Which adblocker are you using?

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

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