dates for on time delivery calc.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Xfree - 15 May 2008 21:10 GMT

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?

Reply to this Message

Fred Smith - 15 May 2008 22:57 GMT

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

>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.
[quoted text clipped - 17 lines]
>
> What is the best way to do this?

Reply to this Message

Xfree - 19 May 2008 20:34 GMT

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

> Assume the due date is in column A, and the receipt date is in column B.
>
[quoted text clipped - 35 lines]
> >
> > What is the best way to do this?

Reply to this Message

Fred Smith - 20 May 2008 05:47 GMT

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.

> Fred,
> Thanks for the help but now I have a new problem:
[quoted text clipped - 59 lines]
>> >
>> > What is the best way to do this?

Reply to this Message