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

[SaraWitch]

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

 

[Fluff]

How about

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

 

[SaraWitch]

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]

Yes you can extend it to do that, it's well within the limits of nested functions.

 

[SaraWitch]

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]

You can do it like

=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]

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

 

[Fluff]

You're welcome & thanks for the feedback.