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]

See if these work. I've include blank rows below the data.

Excel Workbook
	A	B	C	D	E	F	G	H
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	9/25/2013	9/11/2013	10/26/2013	9/26/2013	9/27/2013	10/4/2013	10/24/2013
3	9/25/2013	9/11/2013	10/26/2013	9/26/2013	9/27/2013	10/4/2013	10/29/2013
4	9/25/2013	9/11/2013	10/26/2013	9/26/2013	10/7/2013	10/15/2013	10/25/2013
5	9/30/2013	9/25/2013	11/9/2013	9/30/2013	10/15/2013	NO HH 10/14/2013	11/8/2013
6	11/11/2013	10/31/2013	12/15/2013	9/27/2013
7	10/28/2013	10/9/2013	11/14/2013				11/15/2013
8	10/28/2013	10/15/2013	11/14/2013	10/28/2013		11/6/2013
9
10
11
12	Total	3
13	Last 14 Days	2
14	Last 30 Days	3
15
Sheet

 

[dlj2007]

See if these work. I've include blank rows below the data.

*	A	B	C	D	E	F	G	H
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	9/25/2013	9/11/2013	10/26/2013	9/26/2013	9/27/2013	10/4/2013	10/24/2013	*
3	9/25/2013	9/11/2013	10/26/2013	9/26/2013	9/27/2013	10/4/2013	10/29/2013	*
4	9/25/2013	9/11/2013	10/26/2013	9/26/2013	10/7/2013	10/15/2013	10/25/2013	*
5	9/30/2013	9/25/2013	11/9/2013	9/30/2013	10/15/2013	NO HH 10/14/2013	11/8/2013	*
6	11/11/2013	10/31/2013	12/15/2013	9/27/2013	*	*	*	*
7	10/28/2013	10/9/2013	11/14/2013	*	*	*	11/15/2013	*
8	10/28/2013	10/15/2013	11/14/2013	10/28/2013	*	11/6/2013	*	*
9	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*
12	Total	3	*	*	*	*	*	*
13	Last 14 Days	2	*	*	*	*	*	*
14	Last 30 Days	3	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 80px;"><col style="width: 91px;"><col style="width: 136px;"><col style="width: 105px;"><col style="width: 130px;"><col style="width: 125px;"><col style="width: 110px;"><col style="width: 48px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B12 =SUMPRODUCT(--($C$2:$C$9<$G$2:$G$9))+SUMPRODUCT(--($G$2:$G$9=""),--<span>($C$2:$C$9<today<span style="color: rgb(0, 0, 255);">()</today),--($C$2:$C$9<>"")) B13 =SUMPRODUCT(--($C$2:$C$9<$G$2:$G$9),--(TODAY()-$G$2:$G$9<=14))+SUMPRODUCT(--($G$2:$G$9=""),--(TODAY()-$C$2:$C$9<=14),--(TODAY()-$C$2:$C$9>0)) B14 =SUMPRODUCT(--($C$2:$C$9<$G$2:$G$9),--(TODAY()-$G$2:$G$9<=30))+SUMPRODUCT(--($G$2:$G$9=""),--(TODAY()-$C$2:$C$9<=30),--(TODAY()-$C$2:$C$9>0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you, yet again, that worked perfectly!

 

[AhoyNC]

You're welcome. Glad we got it working.