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]

Here's a new "Work" sheet for the interim calculation (NOTE: You must have the Calendar cover all possible dates, such as the row 9 Christmas 2020 through November 2023)

LJohnson88-v3.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	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1			FY	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022	2022	2022	2022	2022	2022	2022	2022	2022	2022	2022	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023
2			Period	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12
3	Wrk Start	Wrk End	Wrk Total	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5
4	30-Sep-21	27-Oct-21	4	0	0	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	30-Sep-21	28-Nov-21	13	0	0	0	0	0	0	0	0	0	0	0	0	4	4	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	30-Sep-21	28-Sep-22	52	0	0	0	0	0	0	0	0	0	0	0	0	4	4	5	4	4	5	4	4	5	4	4	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	30-Sep-21	03-Mar-22	26	0	0	0	0	0	0	0	0	0	0	0	0	4	4	5	4	4	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	25-Sep-21	10-Oct-21	9	0	0	0	0	0	0	0	0	0	0	0	5	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	25-Dec-20	11-Nov-23	156	0	0	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	5	4	4	0	0	0	0	0	0	0	0	0	0
10	00-Jan-00	00-Jan-00	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Work

Cell Formulas
Range	Formula
D1,AN1,AB1,P1	D1	=Entry!I1
E1,AO1,AC1,Q1	E1	=Entry!I1
F1,AP1,AD1,R1	F1	=Entry!I1
G1,AQ1,AE1,S1	G1	=Entry!I1
H1,AR1,AF1,T1	H1	=Entry!I1
I1,AS1,AG1,U1	I1	=Entry!I1
J1,AT1,AH1,V1	J1	=Entry!I1
K1,AU1,AI1,W1	K1	=Entry!I1
L1,AV1,AJ1,X1	L1	=Entry!I1
M1,AW1,AK1,Y1	M1	=Entry!I1
N1,AX1,AL1,Z1	N1	=Entry!I1
O1,AY1,AM1,AA1	O1	=Entry!I1
A4:B10	A4	=Entry!A3
C4:C10	C4	=SUM(D4:BK4)
D4:AY10	D4	=IF(COUNTIFS(Calendar!$A$2:$A$4218,">="&Entry!$A3,Calendar!$A$2:$A$4218,"<="&Entry!$B3,Calendar!$C$2:$C$4218,Entry!D$2,Calendar!$D$2:$D$4218,Work!D$1)>0,D$3,0)

 

[Toadstool]

Here's your entry sheet with the results:

LJohnson88-v3.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	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1							PERIOD for FY		2020										PERIOD for FY		2021										PERIOD for FY		2022										PERIOD for FY		2023
2	Start Date	End Date	Net Cost	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	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	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 555.56	$ 444.44	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
8	25-Dec-20	11-Nov-23	$ 10,000.00	$ -	$ -	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ 320.51	$ 256.41	$ 256.41	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
9
Entry

Cell Formulas
Range	Formula
U1,AS1,AG1	U1	=I1+1
D3:AY9	D3	=IF(Work!$C4=0,"",Work!D4/Work!$C4*$C3)