Count Negative Hours (Late Deliveries)

ssvv

New Member
Joined
May 12, 2011
Messages
22
I am trying to find out how many deliveries were late.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Ex:<o:p></o:p>
G4:G50 - Lists the scheduled delivery time <o:p></o:p>
H4:H50 - Lists delivery arrival time (when the shipment actually arrived)<o:p></o:p>
<o:p></o:p>
I have calculated the time variance between the scheduled time and actual delivery time in X4:X50<o:p></o:p>
<o:p></o:p>
Now some of the deliveries arrived later than the scheduled time. For example, a delivery may have been scheduled to arrive and 8:00am but arrived at 9:30am. This appears in excel as:<o:p></o:p>
<o:p></o:p>
G4: 8:00 AM<o:p></o:p>
H4: 9:30 AM<o:p></o:p>
<o:p></o:p>
My formula in X4:<o:p></o:p>
=IF(G4 < H4,TEXT(MAX(G4:H4)-MIN(G4:H4),"-h:mm"),TEXT(MAX(G4:H4)-MIN(G4:H4),"h:mm"))<o:p></o:p>
<o:p></o:p>
This generates a -1:30. Indicating that the delivery was 1 hour 30 minutes late. <o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Now I'm trying to use a CountIf formula to count the number of deliveries that were an hour or more late. <o:p></o:p>
<o:p></o:p>
I'm currently using the following formula and it is not generating the right count. <o:p></o:p>
<o:p></o:p>
=COUNTIFS(X3:X50,"<-01:00")<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Please advise. Thank you.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Awesome...Thank you!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 
Upvote 0
One more question.

Now if I want to add another condition to the formula, how do I go about doing that?

I want to keep the present conditions and add a shipper to the list. So, I would like the formula to count the number of deliveries more than an hour late by a given shipper. Assuming the 'Shipper' is listed in column B.

I appreciate the help.
 
Upvote 0
Control+shift+enter, not just enter:

=SUM(IF(G4:G100 < H4:H100, IF(H4:H100-G4:G100 < "01:00"+0, 1)))
With this data that formula returns an incorrect result:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 77px"><COL style="WIDTH: 77px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Scheduled</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Arrived</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Difference</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">10:00 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">10:15 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-0:15</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">9:15 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">11:25 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">-2:10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8:30 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8:30 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0:00</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12:10 PM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">11:45 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0:25</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">7:00 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">9:00 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">-2:00</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8:15 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8:20 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-0:05</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">7:45 AM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">12:00 PM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">-4:15</TD></TR></TBODY></TABLE>


Array entered:

<?XML:NAMESPACE PREFIX = H4 /><H4:H10,IF(H4:H10-G4:G10<"01:00"+0,1)))< p>=SUM(IF(G4:G10 < H4:H10,IF(H4:H10-G4:G10 < "01:00"+0,1)))

Returns 2 while the correct result should be 3.

Try this:

=SUMPRODUCT(--(H4:H10-G4:G10>=TIME(1,0,0)))
</H4:H10,IF(H4:H10-G4:G10<"01:00"+0,1)))<>
 
Upvote 0
One more question.

Now if I want to add another condition to the formula, how do I go about doing that?

I want to keep the present conditions and add a shipper to the list. So, I would like the formula to count the number of deliveries more than an hour late by a given shipper. Assuming the 'Shipper' is listed in column B.

I appreciate the help.
Try this...

Book1
FGHI
3ShipperScheduledArrivedDifference
4A10:00 AM10:15 AM-0:15
5A9:15 AM11:25 AM-2:10
6A8:30 AM8:30 AM0:00
7B12:10 PM11:45 AM0:25
8B7:00 AM9:00 AM-2:00
9B8:15 AM8:20 AM-0:05
10B7:45 AM12:00 PM-4:15
Sheet1

Count of shipments for shipper B that are later than 1 hour or greater.

=SUMPRODUCT(--(F4:F10="B"),--(H4:H10-G4:G10>=TIME(1,0,0)))
 
Upvote 0
One more question.

Now if I want to add another condition to the formula, how do I go about doing that?

I want to keep the present conditions and add a shipper to the list. So, I would like the formula to count the number of deliveries more than an hour late by a given shipper. Assuming the 'Shipper' is listed in column B.

I appreciate the help.

Try
Code:
=SUM(
    IF(B4:B100 = J2,
    IF(G4:G100 < H4:H100, 
    IF(H4:H100-G4:G100 < "01:00"+0, 1))))

where J2 houses a shipper of interest.

Adjust the formula to suit, in particular regarding the last relational operator.
 
Upvote 0

Forum statistics

Threads
1,224,583
Messages
6,179,671
Members
452,937
Latest member
Bhg1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top