Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates for on time delivery calc.
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates for on time delivery calc.
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 a =sumif(c:c,"<0") =sumif(c:c,"0") Regards, Fred "Xfree" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates for on time delivery calc.
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" wrote: 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 a =sumif(c:c,"<0") =sumif(c:c,"0") Regards, Fred "Xfree" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates for on time delivery calc.
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. "Xfree" wrote in message ... 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" wrote: 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 a =sumif(c:c,"<0") =sumif(c:c,"0") Regards, Fred "Xfree" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overdue Delivery Dates | New Users to Excel | |||
NETWORKDAYS calc with 3 columns of dates | Excel Worksheet Functions | |||
Formula for calc diff between two julian dates | Excel Discussion (Misc queries) | |||
How do I calc interest when using multiple dates within a given ye | Excel Worksheet Functions | |||
Delivery note or delivery order sheet | Excel Discussion (Misc queries) |