"If" formula assistance

[WebDeznr]

I have a spreadsheet with multiple columns, a "submitted date" and a "predicted date" (which is 7 days after submitted date) also 3 status columns, "accepted", "accepted as noted" and "not accepted". I am trying to find a formula that will say if one of the 3 columns is not filled in by the predicted date it is ??? days late and then the next column will say yes/no to overdue. Can you please help me? I appreciate it.

 

[Joe4]

Welcome to the Board!

Let's say that your values are in the following cells:
Submitted Date: A2
Predicted Date: B2
Accepted: C2
Accepted as Noted: D2
Not Accepted: E2

The place your overdue formula below in cell E2:

=IF(AND(MIN(C2:E2)>0,MIN(C2:E2)<=B2),"No","Yes")

 

[MrKowz]

Welcome to the forums!

Try:

Excel 2013/2016
	A	B	C	D	E	F	G
1	Submitted Date	Predicted Date	Accepted	Accepted as Noted	Not Accepted	Days Late	Overdue
2	7/1/2017	7/8/2017		x			No
3	7/5/2017	7/12/2017				16	Yes
4	7/25/2017	8/1/2017			x		No
5	7/26/2017	8/2/2017					No
Sheet1

Cell Formulas
Range	Formula
F2	=IF(AND(TODAY()>B2,COUNTBLANK(C2:E2)=3),TODAY()-B2,"")
G2	=IF(F2<>"","Yes","No")
B2	=A2+7

 

[WebDeznr]

Thank you so much for this quick response, I am struggling to get this spreadsheet working:

Actually the second part worked by saying Yes it was overdue, but the first part did not work

here is my columns:
so it is actual dates that I add to the columns, and the date that it was received with the status

	N	O	P	Q	R	S	T
Predicted Date	Submitted Date	Accepted	Accepted as Noted	Not Accepted	Days Late	Overdue
7-May-15		Yes

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10-Apr-17[/TD]
[TD="align: right"]9-Apr-17[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

[TABLE="width: 822"]
<tbody>[TR]
[TD]Your help is appreciated[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

Welcome to the forums!

Try:

Excel 2013/2016

	A	B	C	D	E	F	G
Submitted Date	Predicted Date	Accepted	Accepted as Noted	Not Accepted	Days Late	Overdue
x		No
Yes
x		No
	No

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]7/8/2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/5/2017[/TD]
[TD="align: right"]7/12/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7/25/2017[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/26/2017[/TD]
[TD="align: right"]8/2/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(AND(TODAY()>B2,COUNTBLANK(C2:E2)=3),TODAY()-B2,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(F2<>"","Yes","No")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=A2+7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Did you give my suggestion a try?
It requires no extra columns.

 

[WebDeznr]

Hi Joe; yes I did try but it gives me a yes or no answer instead of the amount of days its overdue. I need both.

also it is giving me incorrect answers I have one sent June 26th with a due date of July 3rd we actually received the response on July 24th and the overdue says no when it was.

If you can help further I would appreciate it.

Thank you

 

[Joe4]

here is my columns:
so it is actual dates that I add to the columns, and the date that it was received with the status

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"][/TH]
[TH="align: center"]N[/TH]
[TH="align: center"]O[/TH]
[TH="align: center"]P[/TH]
[TH="align: center"]Q[/TH]
[TH="align: center"]R[/TH]
[TH="align: center"]S[/TH]
[TH="align: center"]T[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Predicted Date[/TD]
[TD]Submitted Date[/TD]
[TD]Accepted[/TD]
[TD]Accepted as Noted[/TD]
[TD]Not Accepted[/TD]
[TD]Days Late[/TD]
[TD]Overdue[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]10-Apr-17[/TD]
[TD="align: right"]9-Apr-17[/TD]
[TD="align: right"][/TD]
[TD]7-May-15[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This does not seem to follow your rules, in which you said:

a "submitted date" and a "predicted date" (which is 7 days after submitted date)

If the example, you show the Predicted Date as 1 day past your Submitted Date.

 

[Joe4]

also it is giving me incorrect answers I have one sent June 26th with a due date of July 3rd we actually received the response on July 24th and the overdue says no when it was.

It does, in fact, work. I think you may have messed up trying to adjust my formula, as you are using different columns, and the order of the first two fields is different.
If I edit it to the layout you showed in your second post, it would be (in cell T2):

=IF(AND(MIN(P2:R2)>0,MIN(P2:R2)<=N2),"No","Yes")

And the days late formula in cell S2 would be:

=IF(T2="Yes",IF(MIN(P2:R2)=0,TODAY()-N2,MIN(P2:R2)-N2),0)

However, knowing that you want both Days Late and Overdue, I think I would make the following changes:
In S2:

=MAX(IF(MIN(P2:R2)=0,TODAY()-N2,MIN(P2:R2)-N2),0)

In T2:

=IF(S2>0,"Yes","No")

A little simpler.

 

[WebDeznr]

Hi Joe,
I am not entirely sure what I am doing wrong, but its not working, I copied and pasted your script and I am getting wrong results.
Here is a sample of my spreadsheet... as you can see the predicted date was may 24 but it came in on June 26 which means it should be approx. 30+ days late. but the formula says 0

column
N O P Q R S T U V W X Y Z AA AB
row 3 predicted Date Accepted Not Accepted Action Supplier Comments blank blank Name Transmittal Status Days Overdue
date Submitted as noted Accepted on #

24-May-17 17-May-17 26-Jun-17 Complete 0 Yes



This was using in AA3 =IF(AA3="Yes",IF(MIN(P3:R3)=0,TODAY()-N3,MIN(P3:R3)-N3),0)
and AB3 =IF(AND(MIN(P3:R3)>0,MIN(P3:R3)<=N3),"No","Yes")

Sorry I cant add the table itself...

So yes it is overdue but the number of days overdue is incorrect.

I think I am going crazy, I can read script but I cant seem to make this work even with you detailed explanation. I really appreciate your help.

Kelly

 

[WebDeznr]

omg that did not post the way I wrote it, how do I send you a screenshot of the spreadsheet?