Splitting costs based on inconsistent weeks

[ljohnson88]

Good afternoon!

I am having serious trouble creating a formula that can split a cost dependent on how many weeks are in an accounting period.

My workplace has accounting periods 1 through to 12 and runs October through to September; so October is P1, November is P2, December is P3 etc. Periods 1,2,4,5,7,8,10 and 11 are 4 weeks long. Every third period 3,6,9,12 is 5 weeks long. So If I have A1 Start Date, B1 End Date, C1 Net Cost, D1 - O1 as P1 - P12, I would like a formula in D2 - O2 that calculates the split of the cost in cell C2 based on the dates in A2 and B2. Is this even achievable?

The working week is Thursday to Wednesday and I was told that in a newer version of Excel I could use a WEEKNUM formula? But because it is an older version, the Thursday - Wednesday working week would throw the formulas out as soon as you get to the New Year?

I have tried to set up a long list of separate formulas where by it divides the cost into the total amount of weeks and then a separate formula to multiply it again individually by the amount of weeks in that period, but this is a long process and does not take the dates into consideration. We are going to be entering anywhere between 100-300 rows of data per period, so having a date and cost manual entry only would be ideal!

Any help on this would be greatly appreciated.

Thank you.

 

[Toadstool]

Hi LJohnson88,

A few questions:

1. So on 10th October every year the week resets to 1 and the period resets to 1?
2. If so then does week 2 start on the following Thursday? (e.g. for 2020 the 1st October is a Thursday so week 2 starts 8th October, but for 2022 the 1st October is a Saturday so week 2 would start Thursday, 6th October?)
3. What is the earliest date you would ever enter?

 

[ljohnson88]

If I go by the new financial year coming, this starts on Thursday 30th September 2021. Then this follows the 4,4,5 pattern of weeks until Wednesday 28th September 2022. Then the new year would start Thursday 29th September 2022.

In the date from and to columns we would simply enter the beginning Thursday of that period and the final Wednesday of that period respectively.

Also, if it makes another difference, I have now been told we have access to a 2019 version of Excel in the building which this sheet can be used on.

Many thanks

 

[Toadstool]

It's still not clear how the start of year is calculated so I'll assume it's a week 53 avoidance, but however it's calculated I'd just use a table for your calculation.

So here I've a Calendar sheet to show the period for each date (I have up to 2032, hence 4128 rows). You'll need to adjust after comparing with your schedule.

LJohnson88.xlsx
	A	B	C	D	E	F
1	Date	Week	Period		Week	Period
2	9/30/2020	1	1		1	1
3	10/1/2020	1	1		2	1
4	10/2/2020	1	1		3	1
5	10/3/2020	1	1		4	1
6	10/4/2020	1	1		5	2
7	10/5/2020	1	1		6	2
8	10/6/2020	1	1		7	2
9	10/7/2020	2	1		8	2
10	10/8/2020	2	1		9	3
11	10/9/2020	2	1		10	3
Calendar

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2=B1,C1,INDEX($F$2:$F$53,MATCH(B2,$E$2:$E$53,0)))
E3:E11,A3:A11	A3	=A2+1
B3:B11	B3	=IF(MOD(ROW()-2,7)=0,IF(B2+1=53,1,B2+1),B2)

The calculation then becomes COUNTIFS and I added the Days column to simplify the calculation.

LJohnson88.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				PERIOD
2	Start Date	End Date	Net Cost	1	2	3	4	5	6	7	8	9	10	11	12	Days
3	29-Sep-21	10-Oct-21	$ 1,000.00	$ 1,000.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	12
4	29-Sep-21	28-Nov-21	$ 1,000.00	$ 459.02	$ 459.02	$ 81.97	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	61
5	29-Sep-21	28-Sep-22	$ 1,000.00	$ 79.45	$ 76.71	$ 95.89	$ 76.71	$ 76.71	$ 95.89	$ 76.71	$ 76.71	$ 95.89	$ 76.71	$ 76.71	$ 95.89	365
6	06-Jun-21	07-Jun-21	$ 1,000.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 1,000.00	$ -	$ -	$ -	2
Entry

Cell Formulas
Range	Formula
D3:O6	D3	=COUNTIFS(Calendar!$A$2:$A$4218,">="&$A3,Calendar!$A$2:$A$4218,"<="&$B3,Calendar!$C$2:$C$4218,D$2)/$P3*$C3
P3:P6	P3	=COUNTIFS(Calendar!$A$2:$A$4218,">="&$A3,Calendar!$A$2:$A$4218,"<="&$B3)

 

[ljohnson88]

Wow... thank you very much for this. I would not have even known where to start. I think the only problem is that it needs to be exact divisions. So even if the date to date only goes 1 day into another period, it still needs an equal split.

So for example if the cost of £1,000 was all of P1 all of P2 and 'x' days of P3, it would still need to return P1 £307.69, P2 £307.69, P3 £384.62. Would this be achievable?

Thank you so much.

 

[Toadstool]

I'm not sure what you mean by "'x' days of P3". I can't see how a portion of P3 would be so much greater than the whole of P1 or P2?

The calculation is done by number of days in each period. You can check that by removing the "/$P3*$C3" from the end and instead you'll get the number of days in each period for the given dates.
e.g. If your Start Date was 25-Sep-2021 and your End Date was 10-Oct-2021 then, because you said the new year starts 29-Sep-2021, it would report 4 days in P12 (25 Sep to 28 Sep) and 12 days in P1 (29 Sep to 10 Oct). NOTE: This also demonstrates that if dates go over a financial year the periods in D2 to O2 will include all values as no Financial Year is specified for the headings.

 

[ljohnson88]

If the £1,000 cost is to be split over a quarter and the cost landed on the 15th Jan, then the cost on paper would need to be split 15th Jan - 15th March, the return value however does not need to split it by number of days in a period; instead it just needs to recognise what periods that cost has travelled through and split it equally by either 4 weeks or 5. So in this example it has gone Jan - March.
Jan and Feb in our company has 4 weeks in them, so they need 4 weeks allocated to them (£307.69 each). March has 5 weeks so that needs 5 weeks allocated to it (£384.62).

I'm not 100% sure why they want it doing this way, but this is the way it has been requested by the higher ups. And as usual in my company, they have not provided any assistance with how this is actually going to be done! They seem to think that manually dividing a cost by the total number of weeks and then multiplying it by that periods weeks is a suitable solution for hundreds of rows of data.

A friend of mine has just provided me with the below. This seemingly works fine until it splits the cost across four, or more than four months; then it starts totaling the values incorrectly. In between each Period are hidden columns with weeks 1-52 and that same formula. He said he used WEEKNUM return 14 so that it recognises a Thursday start date.

I really appreciate the help.

 

[Toadstool]

It does seem an unusual approach but have a go... OK, I believe I have a beautifully ugly solution still using the daily calendar approach.

Using a work area (here I'm just using some rows below but I can move to another sheet once you've confirmed the calculation) I can check if dates in the range are in a certain period and store that period/weeks number (4 or 5). Then take the 4 or 5 for that period, divide into the total period/weeks for that row and multiply by the cost.

Please run some sample dates and costs to verify.

LJohnson88-v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				PERIOD
2	Start Date	End Date	Net Cost	1	2	3	4	5	6	7	8	9	10	11	12
3	30-Sep-21	27-Oct-21	$ 1,000.00	$ 1,000.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
4	30-Sep-21	28-Nov-21	$ 1,000.00	$ 307.69	$ 307.69	$ 384.62	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
5	30-Sep-21	28-Sep-22	$ 1,000.00	$ 76.92	$ 76.92	$ 96.15	$ 76.92	$ 76.92	$ 96.15	$ 76.92	$ 76.92	$ 96.15	$ 76.92	$ 76.92	$ 96.15
6	30-Sep-21	03-Mar-22	$ 1,000.00	$ 153.85	$ 153.85	$ 192.31	$ 153.85	$ 153.85	$ 192.31	$ -	$ -	$ -	$ -	$ -	$ -
7	25-Sep-21	10-Oct-21	$ 1,000.00	$ 444.44	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 555.56
8
9
10
11	Work	Work	Work	4	4	5	4	4	5	4	4	5	4	4	5	Total
12	30-Sep-21	27-Oct-21	$ 1,000.00	4	0	0	0	0	0	0	0	0	0	0	0	4
13	30-Sep-21	28-Nov-21	$ 1,000.00	4	4	5	0	0	0	0	0	0	0	0	0	13
14	30-Sep-21	28-Sep-22	$ 1,000.00	4	4	5	4	4	5	4	4	5	4	4	5	52
15	30-Sep-21	03-Mar-22	$ 1,000.00	4	4	5	4	4	5	0	0	0	0	0	0	26
16	25-Sep-21	10-Oct-21	$ 1,000.00	4	0	0	0	0	0	0	0	0	0	0	5	9
17
Entry

Cell Formulas
Range	Formula
D3:O7	D3	=D12/$P12*$C3
A12:C16	A12	=A3
D12:O16	D12	=IF(COUNTIFS(Calendar!$A$2:$A$4218,">="&$A3,Calendar!$A$2:$A$4218,"<="&$B3,Calendar!$C$2:$C$4218,D$2)>0,D$11,0)
P12:P16	P12	=SUM(D12:O12)

Here's the calendar sheet again (which I've verified for 2021 and 2022 start). Just copy A56:C56 down to row 4218.

LJohnson88-v2.xlsx
	A	B	C	D	E	F
1	Date	Week	Period		Week	Period
2	10/1/2020	1	1		1	1
3	10/2/2020	1	1		2	1
4	10/3/2020	1	1		3	1
5	10/4/2020	1	1		4	1
6	10/5/2020	1	1		5	2
7	10/6/2020	1	1		6	2
8	10/7/2020	1	1		7	2
9	10/8/2020	2	1		8	2
10	10/9/2020	2	1		9	3
11	10/10/2020	2	1		10	3
12	10/11/2020	2	1		11	3
13	10/12/2020	2	1		12	3
14	10/13/2020	2	1		13	3
15	10/14/2020	2	1		14	4
16	10/15/2020	3	1		15	4
17	10/16/2020	3	1		16	4
18	10/17/2020	3	1		17	4
19	10/18/2020	3	1		18	5
20	10/19/2020	3	1		19	5
21	10/20/2020	3	1		20	5
22	10/21/2020	3	1		21	5
23	10/22/2020	4	1		22	6
24	10/23/2020	4	1		23	6
25	10/24/2020	4	1		24	6
26	10/25/2020	4	1		25	6
27	10/26/2020	4	1		26	6
28	10/27/2020	4	1		27	7
29	10/28/2020	4	1		28	7
30	10/29/2020	5	2		29	7
31	10/30/2020	5	2		30	7
32	10/31/2020	5	2		31	8
33	11/1/2020	5	2		32	8
34	11/2/2020	5	2		33	8
35	11/3/2020	5	2		34	8
36	11/4/2020	5	2		35	9
37	11/5/2020	6	2		36	9
38	11/6/2020	6	2		37	9
39	11/7/2020	6	2		38	9
40	11/8/2020	6	2		39	9
41	11/9/2020	6	2		40	10
42	11/10/2020	6	2		41	10
43	11/11/2020	6	2		42	10
44	11/12/2020	7	2		43	10
45	11/13/2020	7	2		44	11
46	11/14/2020	7	2		45	11
47	11/15/2020	7	2		46	11
48	11/16/2020	7	2		47	11
49	11/17/2020	7	2		48	12
50	11/18/2020	7	2		49	12
51	11/19/2020	8	2		50	12
52	11/20/2020	8	2		51	12
53	11/21/2020	8	2		52	12
54	11/22/2020	8	2		53	12
55	11/23/2020	8	2
56	11/24/2020	8	2
Calendar

Cell Formulas
Range	Formula
C2:C56	C2	=IF(B2=B1,C1,INDEX($F$2:$F$53,MATCH(B2,$E$2:$E$53,0)))
A3:A56	A3	=A2+1
B3:B56	B3	=IF(MOD(ROW()-2,7)=0,IF(B2+1=53,1,B2+1),B2)

 

[ljohnson88]

You absolutely beautiful human being...

That works perfectly. I am guessing that I could just extend the period 1-12 on the Entry tab by another P1-12 using the same formulas if a cost was say to go over two/three years?

This is absolute genius... Thank you so much.

 

[Toadstool]

You're welcome!

...but if you may go over years (as I mentioned earlier) then you'll need the FY as a part of the calculation.... so here's the Calendar sheet again but with a column for the FY

LJohnson88-v3.xlsx
	A	B	C	D	E	F	G	H
1	Date	Week	Period	FY			Week	Period
2	10/1/2020	1	1	2020			1	1
3	10/2/2020	1	1	2020			2	1
4	10/3/2020	1	1	2020			3	1
5	10/4/2020	1	1	2020			4	1
6	10/5/2020	1	1	2020			5	2
7	10/6/2020	1	1	2020			6	2
8	10/7/2020	1	1	2020			7	2
9	10/8/2020	2	1	2020			8	2
10	10/9/2020	2	1	2020			9	3
11	10/10/2020	2	1	2020			10	3
12	10/11/2020	2	1	2020			11	3
13	10/12/2020	2	1	2020			12	3
14	10/13/2020	2	1	2020			13	3
15	10/14/2020	2	1	2020			14	4
16	10/15/2020	3	1	2020			15	4
17	10/16/2020	3	1	2020			16	4
18	10/17/2020	3	1	2020			17	4
19	10/18/2020	3	1	2020			18	5
20	10/19/2020	3	1	2020			19	5
21	10/20/2020	3	1	2020			20	5
22	10/21/2020	3	1	2020			21	5
23	10/22/2020	4	1	2020			22	6
24	10/23/2020	4	1	2020			23	6
25	10/24/2020	4	1	2020			24	6
26	10/25/2020	4	1	2020			25	6
27	10/26/2020	4	1	2020			26	6
28	10/27/2020	4	1	2020			27	7
29	10/28/2020	4	1	2020			28	7
30	10/29/2020	5	2	2020			29	7
31	10/30/2020	5	2	2020			30	7
32	10/31/2020	5	2	2020			31	8
33	11/1/2020	5	2	2020			32	8
34	11/2/2020	5	2	2020			33	8
35	11/3/2020	5	2	2020			34	8
36	11/4/2020	5	2	2020			35	9
37	11/5/2020	6	2	2020			36	9
38	11/6/2020	6	2	2020			37	9
39	11/7/2020	6	2	2020			38	9
40	11/8/2020	6	2	2020			39	9
41	11/9/2020	6	2	2020			40	10
42	11/10/2020	6	2	2020			41	10
43	11/11/2020	6	2	2020			42	10
44	11/12/2020	7	2	2020			43	10
45	11/13/2020	7	2	2020			44	11
46	11/14/2020	7	2	2020			45	11
47	11/15/2020	7	2	2020			46	11
48	11/16/2020	7	2	2020			47	11
49	11/17/2020	7	2	2020			48	12
50	11/18/2020	7	2	2020			49	12
51	11/19/2020	8	2	2020			50	12
52	11/20/2020	8	2	2020			51	12
53	11/21/2020	8	2	2020			52	12
54	11/22/2020	8	2	2020			53	12
55	11/23/2020	8	2	2020
56	11/24/2020	8	2	2020
Calendar

Cell Formulas
Range	Formula
C2:C56	C2	=IF(B2=B1,C1,INDEX($H$2:$H$53,MATCH(B2,$G$2:$G$53,0)))
D2:D56	D2	=IF(AND(B2=1,C2=1),YEAR(A2),D1)
A3:A56	A3	=A2+1
B3:B56	B3	=IF(MOD(ROW()-2,7)=0,IF(B2+1=53,1,B2+1),B2)