This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi Eduardo,
I'm not exactly sure what you are trying to achieve. You want the dates to match the weeks? I see here that you have added columns with the dates. Are you trying to fill in all the dates between 11/18/2019 and 12/2 for example, and then count the number of weeks between both?
Thanks,
Deborah
Hi @DeborahA ,
Correct, and divide the difference of weeks between both by the quantities (not considering columns names). The date always starts on Mondays. Please see below input and desired output:
Input:
RecordID | DATE WK 1 | DATE WK 2 | DATE WKS 3-5 | DATE WKS 6-10 | DATE WKS 11-14 | DATE WK 15 | Qty Week 1 | Qty Week 2 | Qty Weeks 3-5 | Qty Weeks 6-10 | Qty Weeks 11-14 | Qty Week 15 |
1 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 5600 | 5600 | 16800 | 16800 | 16800 | 5600 |
2 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 5400 | 5400 | 0 | 5400 |
3 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 3786 | 0 | 0 |
4 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 3360 | 3840 | 13440 | 14400 | 10080 | 3840 |
5 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 96 |
6 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 6880 | 6880 | 6880 | 0 | 6880 |
7 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 6880 | 13760 | 34400 | 34400 | 34400 | 13760 |
8 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 6400 | 6400 | 12800 | 19200 | 12800 | 6400 |
9 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 36000 | 0 |
10 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 11200 | 0 | 11200 | 11200 | 11200 | 0 |
Desired Output:
RecordID | DATE WK 1 | DATE WK 2 | DATE WK 3 | DATE WK 4 | DATE WK 5 | DATE WK 6 | DATE WK 7 | DATE WK 8 | DATE WK 9 | DATE WK 10 | Qty Week 1 | Qty Week 2 | Qty Week 3 | Qty Week 4 | Qty Week 5 | Qty Week 6 | Qty Week 7 | Qty Week 8 | Qty Week 9 | Qty Week 10 |
1 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 5600 | 5600 | 8400 | 8400 | 4200 | 4200 | 4200 | 4200 | 16800 | 5600 |
2 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 2700 | 2700 | 1350 | 1350 | 1350 | 1350 | 0 | 5400 |
3 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 946.5 | 946.5 | 946.5 | 946.5 | 0 | 0 |
4 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 3360 | 3840 | 6720 | 6720 | 3600 | 3600 | 3600 | 3600 | 10080 | 3840 |
5 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 96 |
6 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 6880 | 3440 | 3440 | 1720 | 1720 | 1720 | 1720 | 0 | 6880 |
7 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 6880 | 13760 | 17200 | 17200 | 8600 | 8600 | 8600 | 8600 | 34400 | 13760 |
8 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 6400 | 6400 | 6400 | 6400 | 4800 | 4800 | 4800 | 4800 | 12800 | 6400 |
9 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 36000 | 0 |
10 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 11200 | 0 | 5600 | 5600 | 2800 | 2800 | 2800 | 2800 | 11200 | 0 |
Thank you!! 🙂
Eduardo
Hi @DeborahA ,
Correct, and then divide the quantities by the number of weeks between both dates (consider dates instead of columns names). Please see new input and desired output:
Input:
RecordID | DATE WK 1 | DATE WK 2 | DATE WKS 3-5 | DATE WKS 6-10 | DATE WKS 11-14 | DATE WK 15 | Qty Week 1 | Qty Week 2 | Qty Weeks 3-5 | Qty Weeks 6-10 | Qty Weeks 11-14 | Qty Week 15 |
1 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 5600 | 5600 | 16800 | 16800 | 16800 | 5600 |
2 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 5400 | 5400 | 0 | 5400 |
3 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 3786 | 0 | 0 |
4 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 3360 | 3840 | 13440 | 14400 | 10080 | 3840 |
5 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 96 |
6 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 6880 | 6880 | 6880 | 0 | 6880 |
7 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 6880 | 13760 | 34400 | 34400 | 34400 | 13760 |
8 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 6400 | 6400 | 12800 | 19200 | 12800 | 6400 |
9 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 36000 | 0 |
10 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 11200 | 0 | 11200 | 11200 | 11200 | 0 |
Desired Output:
DATE WK 1 | DATE WK 2 | DATE WK 3 | DATE WK 4 | DATE WK 5 | DATE WK 6 | DATE WK 7 | DATE WK 8 | DATE WK 9 | DATE WK 10 | Qty Week 1 | Qty Week 2 | Qty Week 3 | Qty Week 4 | Qty Week 5 | Qty Week 6 | Qty Week 7 | Qty Week 8 | Qty Week 9 | Qty Week 10 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 5600 | 5600 | 8400 | 8400 | 4200 | 4200 | 4200 | 4200 | 16800 | 5600 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 2700 | 2700 | 1350 | 1350 | 1350 | 1350 | 0 | 5400 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 946.5 | 946.5 | 946.5 | 946.5 | 0 | 0 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 3360 | 3840 | 6720 | 6720 | 3600 | 3600 | 3600 | 3600 | 10080 | 3840 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 96 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 6880 | 3440 | 3440 | 1720 | 1720 | 1720 | 1720 | 0 | 6880 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 6880 | 13760 | 17200 | 17200 | 8600 | 8600 | 8600 | 8600 | 34400 | 13760 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 6400 | 6400 | 6400 | 6400 | 4800 | 4800 | 4800 | 4800 | 12800 | 6400 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 36000 | 0 |
11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 11200 | 0 | 5600 | 5600 | 2800 | 2800 | 2800 | 2800 | 11200 | 0 |
Thank you!! 🙂
Eduardo
Hi @DeborahA ,
Correct, and then divided the quantities of this column by the number of weeks (not considering the column names to do the calculations, but the results of this division). Please see below another input and desired output:
Input:
RecordID | DATE WK 1 | DATE WK 2 | DATE WKS 3-5 | DATE WKS 6-10 | DATE WKS 11-14 | DATE WK 15 | Qty Week 1 | Qty Week 2 | Qty Weeks 3-5 | Qty Weeks 6-10 | Qty Weeks 11-14 | Qty Week 15 |
1 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 5600 | 5600 | 16800 | 16800 | 16800 | 5600 |
2 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 5400 | 5400 | 0 | 5400 |
3 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 3786 | 0 | 0 |
4 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 3360 | 3840 | 13440 | 14400 | 10080 | 3840 |
5 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 96 |
6 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 6880 | 6880 | 6880 | 0 | 6880 |
7 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 6880 | 13760 | 34400 | 34400 | 34400 | 13760 |
8 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 6400 | 6400 | 12800 | 19200 | 12800 | 6400 |
9 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 36000 | 0 |
10 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 12/2/2019 | 12/30/2019 | 1/6/2020 | 11200 | 0 | 11200 | 11200 | 11200 | 0 |
Desired Output:
RecordID | DATE WK 1 | DATE WK 2 | DATE WK 3 | DATE WK 4 | DATE WK 5 | DATE WK 6 | DATE WK 7 | DATE WK 8 | DATE WK 9 | DATE WK 10 | Qty Week 1 | Qty Week 2 | Qty Week 3 | Qty Week 4 | Qty Week 5 | Qty Week 6 | Qty Week 7 | Qty Week 8 | Qty Week 9 | Qty Week 10 |
1 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 5600 | 5600 | 8400 | 8400 | 4200 | 4200 | 4200 | 4200 | 16800 | 5600 |
2 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 2700 | 2700 | 1350 | 1350 | 1350 | 1350 | 0 | 5400 |
3 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 946.5 | 946.5 | 946.5 | 946.5 | 0 | 0 |
4 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 3360 | 3840 | 6720 | 6720 | 3600 | 3600 | 3600 | 3600 | 10080 | 3840 |
5 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 96 |
6 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 6880 | 3440 | 3440 | 1720 | 1720 | 1720 | 1720 | 0 | 6880 |
7 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 6880 | 13760 | 17200 | 17200 | 8600 | 8600 | 8600 | 8600 | 34400 | 13760 |
8 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 6400 | 6400 | 6400 | 6400 | 4800 | 4800 | 4800 | 4800 | 12800 | 6400 |
9 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 36000 | 0 |
10 | 11/4/2019 | 11/11/2019 | 11/18/2019 | 11/25/2019 | 12/2/2019 | 12/9/2019 | 12/16/2019 | 12/23/2019 | 12/30/2019 | 1/6/2020 | 11200 | 0 | 5600 | 5600 | 2800 | 2800 | 2800 | 2800 | 11200 | 0 |
Thank you!
Best regards,
Eduardo