Calculating a Missed Deadline

[dlj2007]

I need to know how to count how many rows have missed a deadline. So I have 800 rows. Each row contains a Due Date(Column F) and a Date Sent (Column AH). I need to know the count of how many of those rows have missed a deadline. Is this even possible to do without having to create some type of separate column?

 

[AhoyNC]

Try formula below:
You will need to change sheet and cell references to match your data.

Excel Workbook
	C	D	E	F	G	AG	AH	AI
1				ADR Due			ADR Sent
2	Total Late	4		11/3/2013			11/3/2013
3	Late past 14 days	3		10/31/2013			11/1/2013
4				11/5/2013			11/7/2013
5				12/1/2013			12/1/2013
6				9/4/2013			10/1/2013
7				11/11/2013			11/13/2013
8
Sheet

 

[dlj2007]

That worked! I'm finally done, whew! Again, thanks for your help!

 

[AhoyNC]

You're welcome. Glad you got it to work. Thanks for the feedback.

 

[dlj2007]

Try formula below:
You will need to change sheet and cell references to match your data.

*	C	D	E	F	G	AG	AH	AI
1	*	*	*	ADR Due	*	*	ADR Sent	*
2	Total Late	4	*	11/3/2013	*	*	11/3/2013	*
3	Late past 14 days	3	*	10/31/2013	*	*	11/1/2013	*
4	*	*	*	11/5/2013	*	*	11/7/2013	*
5	*	*	*	12/1/2013	*	*	12/1/2013	*
6	*	*	*	9/4/2013	*	*	10/1/2013	*
7	*	*	*	11/11/2013	*	*	11/13/2013	*
8	*	*	*	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 117px;"><col style="width: 32px;"><col style="width: 64px;"><col style="width: 83px;"><col style="width: 36px;"><col style="width: 41px;"><col style="width: 134px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =SUMPRODUCT(--($F$2:$F$7<$AH$2:$AH$7)) D3 =SUMPRODUCT(--($F$2:$F$7<$AH$2:$AH$7),--(TODAY()-$AH$2:$AH$7<=14)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

So apparently, I didn't take into consideration when a due date has been entered, the date has passed, and the Date sent is still blank. I gotta fix this.
Right now my formula reads:
=SUMPRODUCT(--('ADR Tracking'!AH2:AH800>'ADR Tracking'!D2:D800),--(TODAY()-'ADR Tracking'!D2:D800<=14))

....and this is right, except, whenever my client adds someone with a date in column D2:D800(Due Date), the formula doesn’t account for the date that has passed and no date has been entered into AH2:AH800….only if the date that ‘has’ been entered in AH is after the D column date.
Hope this makes a little sense. Can you help? I can’t figure out how to correct this. So assuming we are pulling the below information today. My formula that I have now says there is only 1 deadline missed(D due 11/10/13). But I need it to say 2 have missed the deadline bc Column D has the 11/10/13 being missed, and the 11/11/2013 being missed since no date has been entered in.

D E F G AH
11/13/13 11/13/13
11/12/13 11/10/13
11/11/13 Blank
11/10/13 11/11/13
11/14/13 Blank

 

[AhoyNC]

Try:
Again change to match your cell references.

Excel Workbook
	C	D	E	F	G	AG	AH
1				ADR Due			ADR Sent
2		Total Late	2	11/13/2013			11/13/2013
3		Late last 14 days	2	11/12/2013			11/10/2013
4				11/11/2013
5				11/10/2013			11/11/2013
6				11/14/2013
7
Sheet

 

[dlj2007]

Try:
Again change to match your cell references.

*	C	D	E	F	G	AG	AH
1	*	*	*	ADR Due	*	*	ADR Sent
2	*	Total Late	2	11/13/2013	*	*	11/13/2013
3	*	Late last 14 days	2	11/12/2013	*	*	11/10/2013
4	*	*	*	11/11/2013	*	*	*
5	*	*	*	11/10/2013	*	*	11/11/2013
6	*	*	*	11/14/2013	*	*	*
7	*	*	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 128px;"><col style="width: 64px;"><col style="width: 90px;"><col style="width: 36px;"><col style="width: 64px;"><col style="width: 98px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =SUMPRODUCT(--($F$2:$F$6<$AH$2:$AH$6))+SUMPRODUCT(--($AH$2:$AH$6=""),--<span>($F$2:$F$6<today<span style="color: rgb(0, 0, 255);">()</today)) E3 =SUMPRODUCT(--($F$2:$F$6<$AH$2:$AH$6),--(TODAY()-$AH$2:$AH$6<=14))+SUMPRODUCT(--($AH$2:$AH$6=""),--<span>($F$2:$F$6<today<span style="color: rgb(0, 0, 255);">()</today)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Now I think it's picking up all the blank cells that I have my range for, that I have to include for when additions are made. So I'm assuming I need to add that if a date has been entered in column A.

 

[AhoyNC]

So can the ADR Due column have blanks?
What were the dates in column A of your post #9 and do they even come into play when looking for past due?

It would help if you could post a small sample of the different issues like in your post #9 and also what results you expect to see.

Are these the requirements?
-Count all cells where ADR Sent is past the ADR Due Date.
-Count those that are late in the last 14 days.
-Count if ADR Sent is blank, but today's date is past the the Due date.
-Don't count if ADR Sent is blank, but is not past the due date.

 

[AhoyNC]

I made some changes to the formulas. See if these work (as always change cell references for your data). If not please answer the questions in the above post.

Excel Workbook
	A	B	C	D	E	F
1				ADR Due		ADR Sent
2				9/4/2013		9/3/2013
3				9/11/2013		9/20/2013
4				10/1/2013		10/1/2013
5				10/2/2013
6				11/1/2013		11/2/2013
7				11/5/2013		11/4/2013
8				12/1/2013
9				11/11/2013		11/14/2013
10
11
12	Late	4
13	last 14 days	2
14
Sheet

 

[dlj2007]

I made some changes to the formulas. See if these work (as always change cell references for your data). If not please answer the questions in the above post.

	A	B	C	D	E	F
1				ADR Due		ADR Sent
2				9/4/2013		9/3/2013
3				9/11/2013		9/20/2013
4				10/1/2013		10/1/2013
5				10/2/2013
6				11/1/2013		11/2/2013
7				11/5/2013		11/4/2013
8				12/1/2013
9				11/11/2013		11/14/2013
10
11
12	Late	4
13	last 14 days	2
14

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B12 =SUMPRODUCT(--($D$2:$D$9<$F$2:$F$9))+SUMPRODUCT(--($F$2:$F$9=""),--<span>($D$2:$D$9<today<span style="color: rgb(0, 0, 255);">()</today B13 =SUMPRODUCT(--($D$2:$D$9<$F$2:$F$9),--(TODAY()-$F$2:$F$9<14))+SUMPRODUCT(--(F2:F9=""),--(TODAY()-$D$2:$D$9<14),--(TODAY()-$D$2:$D$9>0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Not a problem Ahoy, I can explain a little better. Sorry for the confusion. So here’s what I have:

	A	B	C	D	E	F	G
1	Date Letter Received	ADR Letter Date	Date ADR Response Due	Date Record Requested	Date MD Records Received	Date HHA Records Received	ADR Response Sent
2	09/25/2013	09/11/2013	10/26/2013	09/26/2013	09/27/2013	10/04/2013	10/24/2013
3	09/25/2013	09/11/2013	10/26/2013	09/26/2013	09/27/2013	10/04/2013	10/29/2013
4	09/25/2013	9/11/2013	10/26/2013	09/26/2013	10/07/2013	10/15/2013	10/25/2013
5	09/30/2013	09/25/2013	11/09/2013	09/30/2013	10/15/2013	NO HH 10/14/2013	11/08/2013
6	11/11/2013	10/31/2013	12/15/2013	09/27/2013
7	10/28/2013	10/09/2013	11/14/2013				11/15/2013
8	10/28/2013	10/15/2013	11/14/2013	10/28/2013		11/06/2013
9
10
11

<tbody>
</tbody>

Calculation needed:
A. Count how many ADRs missed the deadline, as of today, based on when they were due
-In the last 14 days (How many missed deadlines)
-In the last 30 days (How many missed deadline)

B. Count how many are pending transmission. So out of the ones that have been received, what is waiting to be sent out.
-this number will include any that are past due

**The following blank rows will need to be included in the calculation since new information is being added everyday.

 

[dlj2007]

	A	B	C	D	E	F	G
1	Date Letter Received	ADR Letter Date	Date ADR Response Due	Date Record Requested	Date MD Records Received	Date HHA Records Received	ADR Response Sent
2	09/25/2013	09/11/2013	10/26/2013	09/26/2013	09/27/2013	10/04/2013	10/24/2013
3	09/25/2013	09/11/2013	10/26/2013	09/26/2013	09/27/2013	10/04/2013	10/29/2013
4	09/25/2013	9/11/2013	10/26/2013	09/26/2013	10/07/2013	10/15/2013	10/25/2013
5	09/30/2013	09/25/2013	11/09/2013	09/30/2013	10/15/2013	NO HH 10/14/2013	11/08/2013
6	11/11/2013	10/31/2013	12/15/2013	09/27/2013
7	10/28/2013	10/09/2013	11/14/2013				11/15/2013
8	10/28/2013	10/15/2013	11/14/2013	10/28/2013		11/06/2013
9
10
11

<tbody>
</tbody>

Made just a few enhancements to eliminate any confusion, if needed