Revenue allocation by month based on start and end date

[alexbarrow]

Hello All!

Thanks for all of the wonderful posts here, got pretty far with this but got stuck on a few things. Trying to create a formula to allocated revenue over months based on the start and end date of a project. The goal would be to distribute revenue appropriately based on these dates.

Everything was working great with my formula until things entered into a new year where my formula stopped working and is pulling inaccurate results. The formula also doesn't work if the project time extends from one year to the next.

Anyone know a workaround? Open to different solutions to accomplish the same basic thing which is allocating revenue based on the start and end dates across multiple months as a portion of the total fee.

Studio Tracker - Sample Data.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
1
2		FEE	WEEKS	START	END	DAILY	12/31/21	1/31/22	2/28/22	3/31/22	4/30/22	5/31/22	6/30/22	7/31/22	8/31/22	9/30/22	10/31/22	11/30/22	12/31/22	1/31/23	2/28/23	3/31/23	4/30/23	5/31/23	6/30/23	7/31/23	8/31/23	9/30/23	10/31/23	11/30/23	12/31/23	1/31/24	2/1/24
3		$ 100,000	20	5/5/22	9/22/22	$ 714.29						$ 18,571.43	$ 21,428.57	$ 22,142.86	###########	###########									##########	##########	##########
Sheet1

Cell Formulas
Range	Formula
E3	E3	=D3+C3*7
F3	F3	=IFERROR(B3/C3/7,"")
H3:AG3	H3	=IF(AND(MONTH($D3)=MONTH(H$2),YEAR($D3)=YEAR(H$2),MONTH($E3)=MONTH(H$2),YEAR($E3)=YEAR(H$2)),$B3,IF(AND(MONTH($D3)=MONTH(H$2),YEAR($D3)=YEAR(H$2),MONTH($E3)<>MONTH(H$2)),(H$2-$D3)*$F3,IF(AND(MONTH($D3)<MONTH(H$2),MONTH($E3)>MONTH(H$2)),(H$2-G$2)*$F3,IF(AND(MONTH($E3)=MONTH(H$2),YEAR($E3)=YEAR(H$2),MONTH($D3)<>MONTH(H$2)),($E3-G$2)*$F3,""))))

 

[alexbarrow]

Here are some more examples of some of the different conditions that I have created with the formula. Notice the mistakes its making on Row 3 for values in 2023 that should not be calculating and the way the formula ceases to function at all correctly in Row 6 when extended beyond one year but does function on Row 7 when the timeline extends from one year to the next.

Studio Tracker - Sample Data.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
1
2		FEE	WEEKS	START	END	DAILY	12/31/21	1/31/22	2/28/22	3/31/22	4/30/22	5/31/22	6/30/22	7/31/22	8/31/22	9/30/22	10/31/22	11/30/22	12/31/22	1/31/23	2/28/23	3/31/23	4/30/23	5/31/23	6/30/23	7/31/23	8/31/23	9/30/23	10/31/23	11/30/23	12/31/23	1/31/24	2/1/24
3		$ 100,000	20	5/5/22	9/22/22	$ 714.29						$ 18,571.43	$ 21,428.57	$ 22,142.86	$ 22,142.86	$ 15,714.29									$ 21,428.57	$ 22,142.86	$ 22,142.86
4		$ 50,000	2	3/1/22	3/15/22	$ 3,571.43				$ 50,000.00
5		$ 150,000	5	1/1/22	2/5/22	$ 4,285.71		$ 128,571.43	$ 21,428.57
6		$ 200,000	52	2/2/22	2/1/23	$ 549.45
7		$ 200,000	4	12/25/22	1/22/23	$ 7,142.86													$ 42,857.14	$ 157,142.86
Sheet1

Cell Formulas
Range	Formula
E3:E7	E3	=D3+C3*7
F3:F7	F3	=IFERROR(B3/C3/7,"")
H3:AG7	H3	=IF(AND(MONTH($D3)=MONTH(H$2),YEAR($D3)=YEAR(H$2),MONTH($E3)=MONTH(H$2),YEAR($E3)=YEAR(H$2)),$B3,IF(AND(MONTH($D3)=MONTH(H$2),YEAR($D3)=YEAR(H$2),MONTH($E3)<>MONTH(H$2)),(H$2-$D3)*$F3,IF(AND(MONTH($D3)<MONTH(H$2),MONTH($E3)>MONTH(H$2)),(H$2-G$2)*$F3,IF(AND(MONTH($E3)=MONTH(H$2),YEAR($E3)=YEAR(H$2),MONTH($D3)<>MONTH(H$2)),($E3-G$2)*$F3,""))))