IF statement for 30, 60, 90 day overdue

[CyberWrek]

Hi everyone,

I've been trying like mad to wrap my head around creating a 30, 60, 90 day overdue statement for a week. Unlike most threads I've visited, where the intent is to simply show if something is overdue, I would like to add a feature whereby, when a date is added to a column to show the task is complete, the item will no longer show as overdue. I imagine it's possible, I'm just lost. Would love your insights! Thanks!

 

[Jeffrey Mahoney]

Please try this:
=IF(TODAY()-B2>90,"Over 90 Days",IF(TODAY()-B2>60,"Over 60 Days",IF(TODAY()-B2>30,"Over 30 Days",TODAY()-B2)&" Days"))
in cell D2 if your data begins on row 2 and Days Open is in column D

 

[CyberWrek]

=IF(TODAY()-B2>90,"Over 90 Days",IF(TODAY()-B2>60,"Over 60 Days",IF(TODAY()-B2>30,"Over 30 Days",TODAY()-B2)&" Days"))

Hi Jeffrey. Thanks for your help. Unfortunately, the formula doesn't do anything when data is entered into the Date Complete. I tried modifying what you posted, but still can't get it to work. Each one shows "Over 90 Days" for something that is well under or has a completed date.

=IF(TODAY()-E11>=90,"Over 90 Days",IF(TODAY()-E11>=60,"Over 60 Days",IF(TODAY()-E11>=30,"Over 30 Days",IF(TODAY()-E11<30,"Under 30 Days",IF(E11-O11>=-1,"","")))))

In this formula, E11 is the submitted date and O11 is the completed date.

 

[Jeffrey Mahoney]

Make sure column E has actual dates and not text: Today() - E11 (zero) will always show over 90 days. I tested this om my end and was able to get varying returns

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this the sort of thing you are after?

23 06 03.xlsm
	E										O	P
10	Requested										Completed	Days Open
11	29-Jan-23											Over 90 days
12	14-Feb-23										03-Jun-23
13	02-Mar-23											Over 90 days
14	18-Mar-23										15-Apr-23
15	03-Apr-23											Over 60 days
16	19-Apr-23											Over 30 days
17	05-May-23										06-May-23
18	21-May-23											Under 30 days
Days Open

Cell Formulas
Range	Formula
P11:P18	P11	=IF(O11="",LOOKUP(TODAY()-E11,{0,30,60,90},{"Under 30","Over 30","Over 60","Over 90"})&" days","")