Use of MS Excel for Customer Delivery Tracking

[emmanuel10]

Hi everybody,
I try to find an excel file to follow our On Time Delivery for my company (AS9120). We've some data in an excel file like :
Date expected by the customer
Date we sent the part and a lot of different datas

I'm looking for a formula to know for, per example 80 shipment, how many are on time and how many are late.... and the result by month with a percentage...
I've spend a LOT of time to try to find or to build it, with a bad result....

If someone can be kind to help me

[Jennifer Kirley]

Welcome to the Cove! I am sorry you didn't find what you need.

I don't remember seeing an Excel file attached here for your specific purpose. But we can apply something that was made for audits; I have attached tracking logs for nonconformances that track time to respond, days late x, and time open overall. They can also track results by group, if you're interested in performing this function over multiple shippers.

The headers are set for audit related activities, and can be changed to suit your needs. Unneeded columns can be deleted.

Would this help? There are a number of tracking logs in the Post Attachments - I trust you did searches using the green button in the header of this page?

I hope this helps!

[Bev D]

welcome emmanuel10

If you post some of your data we can help you

[Jennifer Kirley]

Bev is correct, as unfortunately my tracking logs aren't set up to note percentages of times late for this-or-that group, though they could be adapted. What you are interested in uses the TODAY() time counting function in Excel.

[emmanuel10]

In Reply to Parent Post by Bev D

welcome emmanuel10

If you post some of your data we can help you

Thanks all for your reply , I've prepared our excel file without internal datas, I see a problem when I export it on xls format, because we're on mac, the checkboxes disappear ..... anyway, consider "true" and "false" are checkboxes...

I just want to know "easily" the percent of OTD per month......
The system, now is to add a "1" or "2" in the end of each arrow when we're in time with the customer requirements.... (-5 days; +3 days), each month, we count the total of delivery on time and late and with a calculator, we've a percent....
Nor easy, waste of time ..... if it can be automatic......

[pkost]

I'm not entirely sure which dates you want to compare - I used H-"effective delivery date" and E-"delivery date requested", but from what I have done you can modify the equations in column P to update the pivot table in the "monthly report" sheet. I also added sheet "x number of days" which allows you to calculate for the past x number of days by modifying cell b1

You'll need to tidy up the appearance somewhat, but it's functional

Hope it helps

[emmanuel10]

In Reply to Parent Post by pkost

I'm not entirely sure which dates you want to compare - I used H-"effective delivery date" and E-"delivery date requested", but from what I have done you can modify the equations in column P to update the pivot table in the "monthly report" sheet. I also added sheet "x number of days" which allows you to calculate for the past x number of days by modifying cell b1

You'll need to tidy up the appearance somewhat, but it's functional

Hope it helps

oohhhh thank you a lot , I'll now to try to "reformate" it in Apple's application "numbers"....
I'll come back soon, but thank you at this moment