Comparing due dates with completion dates

[CodePest]

Hi there, first time poster here.

I'm sure there is a simple solution here but I hope someone in the group can help me.

I built an application that tracks requests made by clients. When we receive a request, a due date is generated. When our agents submit their work, the date of completion is pasted in an adjacent column next to the due date corresponding with the client request.

I need to count how many times the work is submitted late.

I'm sure I can do it in VBA, but I'd rather it was a native excel equation that refreshed each time the data updates.

How would you create an equation that counts how many times work was completed on time and how many times it was late? Using the following sample should return 3 on time and 3 late:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Due Dates[/TD]
[TD]Work Complete[/TD]
[/TR]
[TR]
[TD]1/10/2018[/TD]
[TD]1/9/2018[/TD]
[/TR]
[TR]
[TD]1/10/2018[/TD]
[TD]1/9/2018[/TD]
[/TR]
[TR]
[TD]1/13/2018[/TD]
[TD]1/9/2018[/TD]
[/TR]
[TR]
[TD]1/13/2018[/TD]
[TD]1/16/2018[/TD]
[/TR]
[TR]
[TD]1/14/2018[/TD]
[TD]1/16/2018[/TD]
[/TR]
[TR]
[TD]1/14/2018[/TD]
[TD]1/16/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Enter this formula with CTRL-SHIFT-ENTER (instead of just enter):

{=SUM(IF(A3:A8>B3:B8,1,0))}

Note: Don't really type the squiggly brackets, those just indicate that it is an array formula entered with CTRL-SHIFT-ENTER (you will see them if you enter it correctly).

 

[CodePest]

Thank you for the reply!

Do you know how to modify this equation to ignore empty cells in B. I have equations in each cell but a date only generates if one is available.

 

[jtakw]

Hi,

Try these for ON TIME and LATE:

Book1
	A	B	C	D	E
1	Due Dates	Work Complete		On Time	Late
2	1/10/2018	1/9/2018		4	4
3	1/10/2018	1/9/2018
4	1/13/2018	1/9/2018
5	1/13/2018	1/16/2018
6	1/14/2018	1/16/2018
7	1/14/2018	1/16/2018
8	1/14/2018
9	1/16/2018
10	1/16/2018	1/16/2018
Sheet2

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(A2:A10>=B2:B10)*(B2:B10<>""))
E2	=SUMPRODUCT(--(A2:A10))+COUNTIFS(B2:B10,"",A2:A10,"<"&TODAY())

 

[Joe4]

Try this:

=SUMPRODUCT(--(A3:A8>B3:B8),--(B3:B8<>""))

EDIT: Look like jtakw already posted the SUMPRODUCT solution while I was testing!

 

[CodePest]

Joe4, Thank you! I was able to make this work.