Hello!
I have a set of columns for Start / Finish / Weekly Amount, and the goal is to distribute the weekly amount into each month.
I attached a sample sheet with what the results should be.
Thanks for your help.
I have a set of columns for Start / Finish / Weekly Amount, and the goal is to distribute the weekly amount into each month.
I attached a sample sheet with what the results should be.
Thanks for your help.
Monthly Distribution.xlsx | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | Start | Finish | Duration d | Duration w | Weekly Amount | 7/17/2023 | 7/24/2023 | 7/31/2023 | 8/7/2023 | 8/14/2023 | 8/21/2023 | 8/28/2023 | 9/4/2023 | 9/11/2023 | 9/18/2023 | 9/25/2023 | 10/2/2023 | 10/9/2023 | 10/16/2023 | 10/23/2023 | 10/30/2023 | |||
2 | 7/19/2023 | 8/3/2023 | 15.00 | 2.14 | 1 | 1 | 1 | 1 | ||||||||||||||||
3 | 8/7/2023 | 10/26/2023 | 80.00 | 11.43 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||
4 | ||||||||||||||||||||||||
5 | ||||||||||||||||||||||||
6 | ||||||||||||||||||||||||
7 | ||||||||||||||||||||||||
8 | ||||||||||||||||||||||||
9 | ||||||||||||||||||||||||
10 | ||||||||||||||||||||||||
11 | Start | Finish | Duration d | Duration w | Weekly Amount | 7/31/2023 | 8/31/2023 | 9/30/2023 | 10/31/2023 | |||||||||||||||
12 | 7/19/2023 | 8/3/2023 | 15.00 | 2.14 | 1 | 3 | ||||||||||||||||||
13 | 8/7/2023 | 10/26/2023 | 80.00 | 11.43 | 1 | 4 | 4 | 5 | ||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G1 | G1 | =MIN($A$2:$A$3)-WEEKDAY(MIN($A$2:$A$3,1)+1) |
H1:V1 | H1 | =IF(G$1<(MAX($B$2:$B$3,1)+1),(G$1+7),"") |
C2:C3,C12:C13 | C2 | =B2-A2 |
D2:D3,D12:D13 | D2 | =(B2-A2)/7 |
G11 | G11 | =EOMONTH(MIN($A$12:$A$13),0) |
H11:J11 | H11 | =IF(G$11<EOMONTH(MAX($B$12:$B$13),0),EOMONTH(G$11,1),"") |