dates for on time delivery calc.

[Xfree]

I am trying to take all the orders for different parts from a vendor for a
year and calculate their on-time or late delivery times.

I am having troubles when a vendor delivers early it causes a negative
number. So I need to do two things 1 find the total early deliveries and the
total numbers of days early then I need to find the total late deliveries and
the total numbers of days late.

I have used NETWORKDAYS and that works well if the shipment is on-time or
late.

Example:

Part X due date is 06/29/2007 actual receipt date 06/20/2007 = -8 or 8 days
early
Part Z due date is 06/20/2007 actual receipt date 06/28/2007 = 8 days late

What is the best way to do this?

 

[Fred Smith]

Assume the due date is in column A, and the receipt date is in column B.

In column C, calculate the day difference, using:

=b2-a2

and copy down.

Now the total number of days early is the sum of colum C where the number is
negative, and the total number of days late is the sum of column C where the
number is positive. These formula are:

=sumif(c:c,"<0")
=sumif(c:c,">0")

Regards,
Fred

 

[Xfree]

Fred,
Thanks for the help but now I have a new problem:

Pono Itemkey RequestDate Days Early or Late Total
18663 T446-C 1/3/2008 5 5
8 8
9 9
15 15
Our vendors do not ship complete orders, in the example you can see we
receved parts on the same PO 4 times. Now the problem is that the counts
above show the days late on each receipt but really all I want to know is the
total days late to ship complete which is the 15 days. How do I filter out
the rest of the days?

Mark Pierce

 

[Fred Smith]

How do you tell when an order is complete?

Whatever identifies a complete order, test for that before calculating the
day difference. If the order is incomplete, leave it blank. If the order is
complete, then do the calculation. As in:

=if(pono<>"",b2-a2,"")

Regards,
Fred.