lkndllsgrl
New Member
- Joined
- Feb 7, 2013
- Messages
- 21
I am trying to use a countif formula to determine how many dates were on time, 7 days late, 14 days late and 30 days or more late. You can see in the table below the columns I am working with. [TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Date Recieved[/TD]
[TD]Date Due[/TD]
[TD]Date Submitted[/TD]
[/TR]
[TR]
[TD]10/27/17[/TD]
[TD]11/26/17[/TD]
[TD]12/27/17[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]11/17/17[/TD]
[TD]11/13/17[/TD]
[/TR]
[TR]
[TD]10/17/17[/TD]
[TD]10/23/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]On Time[/TD]
[TD]7 Days Late[/TD]
[/TR]
[TR]
[TD]10/1/17[/TD]
[TD]10/31/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/17[/TD]
[TD]11/30/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The current formula I have is
=countifs(C:C,>="&A1,C:C,"<="&B1,E:E,<=&D:D)
The first part of the formula brings back the correct count of number of instances within the date frame, howver I cannot get the second part to return count of on time instances. \
Please help
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Date Recieved[/TD]
[TD]Date Due[/TD]
[TD]Date Submitted[/TD]
[/TR]
[TR]
[TD]10/27/17[/TD]
[TD]11/26/17[/TD]
[TD]12/27/17[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]11/17/17[/TD]
[TD]11/13/17[/TD]
[/TR]
[TR]
[TD]10/17/17[/TD]
[TD]10/23/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]On Time[/TD]
[TD]7 Days Late[/TD]
[/TR]
[TR]
[TD]10/1/17[/TD]
[TD]10/31/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/17[/TD]
[TD]11/30/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The current formula I have is
=countifs(C:C,>="&A1,C:C,"<="&B1,E:E,<=&D:D)
The first part of the formula brings back the correct count of number of instances within the date frame, howver I cannot get the second part to return count of on time instances. \
Please help