Hello guys!
I have to compare the date of the delivery inhouse with shipment date(outbound) and therefore evaluate the delivery accuracy between those two types of date. In the beginning I tought that a pivot table would only be needed in order to make such kind of analysis, but I further in the process I got a bit lost...
Sorry for the simple question, but I'm not an expert in excel!
Your help would be much appeciated!
I have to compare the date of the delivery inhouse with shipment date(outbound) and therefore evaluate the delivery accuracy between those two types of date. In the beginning I tought that a pivot table would only be needed in order to make such kind of analysis, but I further in the process I got a bit lost...
Sorry for the simple question, but I'm not an expert in excel!
Your help would be much appeciated!
Gasa Data Consolidated new!.xlsx | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
1 | Request ID | Date for Delivery Inhouse | Year | Month | Week | Weekday | Quarter | Shipment date | Year 2 | Month 2 | Week 2 | Weekday 2 | Vendor No. | Item No. | Item category No. | Description | Sell-to Customer | Ship-to Code | Ship-to Country | Sales Quantity | Pcs/colli | Sales Quantity (Base) | Quantity Shipped | Exception lines | ||
2 | 743675 | 11/10/2020 | 2020 | 11 | 46 | 3 | 2020,4 | 11/10/2020 | 2020 | 11 | 46 | 3 | 19961 | 100055436 | 22 | Monstera deliciosa | 103697 | SE | 40 | 65 | -2600 | 40 | No | |||
3 | 743679 | 10/6/2020 | 2020 | 10 | 41 | 3 | 2020,4 | 10/6/2020 | 2020 | 10 | 41 | 3 | 15600 | 100083962 | 22 | Monstera deliciosa | 103697 | SE | 40 | 62 | -2480 | 40 | No | |||
4 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 19953 | 100091580 | 18 | Catalogue | 103810 | NO | 5 | 1 | -5 | 5 | No | |||
5 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 15571 | 100091552 | 18 | Catalogue | 103810 | NO | 5 | 1 | -5 | 5 | No | |||
6 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 19953 | 100091580 | 18 | Catalogue | 103810 | NO | 10 | 1 | -10 | 10 | No | |||
7 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 19523 | 100091583 | 18 | Catalogue | 103810 | NO | 1 | 1 | -1 | 1 | No | |||
8 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 17499 | 100091588 | 18 | Catalogue | 103810 | NO | 3 | 1 | -3 | 3 | No | |||
9 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 19049 | 100091605 | 18 | Catalogue | 103810 | NO | 1 | 1 | -1 | 1 | No | |||
10 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 19961 | 100091550 | 18 | Catalogue | 103810 | NO | 1 | 1 | -1 | 1 | No | |||
11 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 19961 | 100091550 | 18 | Catalogue | 103810 | NO | 5 | 1 | -5 | 5 | No | |||
12 | 752931 | 12/28/2019 | 2019 | 12 | 52 | 7 | 2019,4 | 12/15/2020 | 2020 | 12 | 51 | 3 | 99990 | 100091613 | 18 | Catalogue | 103810 | NO | 8 | 1 | -8 | 8 | No | |||
13 | 756004 | 11/2/2020 | 2020 | 11 | 45 | 2 | 2020,4 | 11/2/2020 | 2020 | 11 | 45 | 2 | 17884 | 100050488 | 05 | Cedrus Deodara | 104349 | 0001 | DK | 25 | 1 | -25 | 25 | No | ||
14 | 766463 | 9/16/2020 | 2020 | 9 | 38 | 4 | 2020,3 | 9/16/2020 | 2020 | 9 | 38 | 4 | 19876 | 100077225 | 23 | Dionaea muscipula | 104142 | DK | 212 | 103 | -21836 | 212 | No | |||
15 | 766464 | 10/2/2020 | 2020 | 10 | 40 | 6 | 2020,4 | 10/2/2020 | 2020 | 10 | 40 | 6 | 19876 | 100077225 | 23 | Dionaea muscipula | 104142 | DK | 350 | 103 | -36050 | 350 | No | |||
16 | 766466 | 11/26/2020 | 2020 | 11 | 48 | 5 | 2020,4 | 11/26/2020 | 2020 | 11 | 48 | 5 | 19876 | 100077225 | 23 | Dionaea muscipula | 104142 | DK | 350 | 103 | -36050 | 350 | No | |||
17 | 768193 | 2/18/2021 | 2021 | 2 | 8 | 5 | 2021,1 | 2/18/2021 | 2021 | 2 | 8 | 5 | 19536 | 100110275 | 41 | Hydrangea macrophylla | 104188 | FI | 5 | 40 | -200 | 5 | No | |||
18 | 768193 | 2/18/2021 | 2021 | 2 | 8 | 5 | 2021,1 | 2/18/2021 | 2021 | 2 | 8 | 5 | 19536 | 100110276 | 41 | Hydrangea macrophylla | 104188 | FI | 6 | 40 | -240 | 6 | No | |||
19 | 768193 | 2/18/2021 | 2021 | 2 | 8 | 5 | 2021,1 | 2/18/2021 | 2021 | 2 | 8 | 5 | 19536 | 100104011 | 41 | Hydrangea paniculata | 104188 | FI | 4 | 40 | -160 | 4 | No | |||
20 | 768193 | 2/18/2021 | 2021 | 2 | 8 | 5 | 2021,1 | 2/18/2021 | 2021 | 2 | 8 | 5 | 19536 | 100101875 | 41 | Hydrangea macrophylla | 104188 | FI | 3 | 40 | -120 | 3 | No | |||
21 | 768193 | 2/18/2021 | 2021 | 2 | 8 | 5 | 2021,1 | 2/18/2021 | 2021 | 2 | 8 | 5 | 19536 | 100095098 | 41 | Hydrangea macrophylla | 104188 | FI | 1 | 40 | -40 | 1 | No | |||
22 | 768193 | 2/18/2021 | 2021 | 2 | 8 | 5 | 2021,1 | 2/18/2021 | 2021 | 2 | 8 | 5 | 19536 | 100072474 | 41 | Hydrangea macrophylla | 104188 | FI | 4 | 40 | -160 | 4 | No | |||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C22 | C2 | =YEAR([@[Date for Delivery Inhouse]]) |
D2:D22 | D2 | =MONTH([@[Date for Delivery Inhouse]]) |
E2:E22 | E2 | =WEEKNUM([@[Date for Delivery Inhouse]],1) |
F2:F22 | F2 | =WEEKDAY([@[Date for Delivery Inhouse]],1) |
G2:G22 | G2 | =CONCATENATE(YEAR([@[Date for Delivery Inhouse]]),",",ROUNDUP(MONTH([@[Date for Delivery Inhouse]])/3,0)) |
I2:I22 | I2 | =YEAR([@[Shipment date]]) |
J2:J22 | J2 | =MONTH([@[Shipment date]]) |
K2:K22 | K2 | =WEEKNUM([@[Shipment date]],1) |
L2:L22 | L2 | =WEEKDAY([@[Shipment date]],1) |