Convert randomly dated amount to monthly

[vincemtl]

I would like to automatically calculate the revenue for each month based on the collection date. For example $13.16 per day from Jan 5th to Jan 31st calculated when I enter data for amount collected in February. Then Feb to to use $13.16 per day from the 1st to the 12th and $19.05 from the 13th till the 28th and so on. Please help me

A1	B1	C1	D1	E1	F1	G1
A2	Collection Date	Revenue amount collected	Days gap	Revenue per day	Month	Total revenue per month
A3	1/1/2022​				January
A4	1/5/2022​	$ 100.00	4​	$ 25.00	February
A5	2/12/2022​	$ 500.00	38​	$ 13.16	March
A6	3/5/2022​	$ 400.00	21​	$ 19.05	April
A7	3/31/2022​	$ 350.00	26​	$ 13.46	May
A8	4/20/2022​	$ 500.00	20​	$ 25.00
A9	5/10/2022​	$ 400.00	20​	$ 20.00

 

[StephenCrump]

Welcome to the Forum!

Try:

	A	B	C	D	E	F	G	H	I	J	K
1										Example in detail:
2		Collection Date	Revenue amount collected	Days gap	Revenue per day		Month	Total revenue per month		Feb	462.72
3		31 Dec 2021					Jan	442.16		1 Feb 2022	13.16
4		5 Jan 2022	$100.00	5	$20.00		Feb	462.72		2 Feb 2022	13.16
5		12 Feb 2022	$500.00	38	$13.16		Mar	445.21		3 Feb 2022	13.16
6		5 Mar 2022	$400.00	21	$19.05		Apr	700.00		4 Feb 2022	13.16
7		31 Mar 2022	$350.00	26	$13.46		May	200.00		5 Feb 2022	13.16
8		20 Apr 2022	$500.00	20	$25.00		Jun	0.00		6 Feb 2022	13.16
9		10 May 2022	$400.00	20	$20.00		Jul	0.00		7 Feb 2022	13.16
10							Aug	0.00		8 Feb 2022	13.16
11							Sep	0.00		9 Feb 2022	13.16
12							Oct	0.00		10 Feb 2022	13.16
13							Nov	0.00		11 Feb 2022	13.16
14							Dec	0.00		12 Feb 2022	13.16
15										13 Feb 2022	19.05
16										14 Feb 2022	19.05
17										15 Feb 2022	19.05
18										16 Feb 2022	19.05
19										17 Feb 2022	19.05
20										18 Feb 2022	19.05
21										19 Feb 2022	19.05
22										20 Feb 2022	19.05
23										21 Feb 2022	19.05
24										22 Feb 2022	19.05
25										23 Feb 2022	19.05
26										24 Feb 2022	19.05
27										25 Feb 2022	19.05
28										26 Feb 2022	19.05
29										27 Feb 2022	19.05
30										28 Feb 2022	19.05
31
Sheet1

Cell Formulas
Range	Formula
K2	K2	=SUM(IFERROR(K3#,0))
G3:G14	G3	=EDATE(DATE(2022,1,1),SEQUENCE(12,,0))
J3:J30	J3	=SEQUENCE(EDATE(J2,1)-J2,,J2)
K3:K30	K3	=INDEX(E$3:E$9,MATCH(J3#-1,B$3:B$9,1)+1)
D4:D9	D4	=B4-B3
E4:E9	E4	=ROUND(C4/D4,2)
H3:H14	H3	=SUM(IFERROR(INDEX(E$3:E$9,MATCH(SEQUENCE(EDATE(G3,1)-G3,,G3-1),B$3:B$9,1)+1),0))
Dynamic array formulas.

Note that I changed your start date on the assumption that the first period is 1st to 5th Jan inclusive, i.e. 5 days rather than 4?

 

[vincemtl]

Thank you so much. It works like magic.

 

[StephenCrump]

That's good to hear. Thanks for posting back.