Amounts distributed by months based on two dates.

[KasperSSI]

Hi,

Working an a budget template. And i want to make a formula that splites the amount into the correct months based on dates.
Currently i have following that works if the two dates are in 2025. but marked line with end date in 2026 is giving me problems.
Should give me 50.000 from july to December.

=IF(AND(Q$36>=MONTH($G39);$J$35>=YEAR($G39);Q$36<=MONTH($H39);$J$35<=YEAR($H39));$J39/(DATEDIF($G39;$H39;"M")+1);"")

Looking forward to your solution suggestions

 

[felixstraube]

How about:

=LET(b, J39,
m, VALUE(UNIQUE(TEXT(SEQUENCE(H39-G39+1, ,G39), "mm-yyyy"))),
fm, TAKE(m, 1),
lm, TAKE(m,-1),
amountPerMonth, b/ROWS(m),
monthSeq, DATE($J$35, SEQUENCE(, 12), 1),
IFERROR(IF((monthSeq>=fm)*(monthSeq<=lm), amountPerMonth, ""), "")
)

I count 11 month for row 39, so the distributed budged would be 45,454.55 per month not 50.000. Is that correct?

 

[DanteAmor]

Instead of entering the year and month, enter the full date and then change the cell format:
In cells K34, K35 and K36 I have the same date 1/Jan/2025 but the cell format is different, in the first cell the custom format is "M", for the second cell it is "MMM" and the third is "DD/MM/YYYY"

varios 25oct2024.xlsm
	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
34						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
35					2025	ene	feb	mar	abr	may	jun	jul	ago	sep	oct	nov	dic	ene	feb	mar	abr	may	jun	jul	ago	sep	oct	nov	dic	ene
36	Marg	Start Date	End Date	Cost	Bud	01/01/2025	01/02/2025	01/03/2025	01/04/2025	01/05/2025	01/06/2025	01/07/2025	01/08/2025	01/09/2025	01/10/2025	01/11/2025	01/12/2025	01/01/2026	01/02/2026	01/03/2026	01/04/2026	01/05/2026	01/06/2026	01/07/2026	01/08/2026	01/09/2026	01/10/2026	01/11/2026	01/12/2026	01/01/2027
37		01/05/2025	31/10/2025		50,000.00					8,333.33	8,333.33	8,333.33	8,333.33	8,333.33	8,333.33
38		01/04/2025	30/06/2025		5,000.00				1,666.67	1,666.67	1,666.67
39		05/07/2025	25/05/2026		500,000.00							45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55
40		06/01/2025	30/06/2025		5,000,000.00	833,333.33	833,333.33	833,333.33	833,333.33	833,333.33	833,333.33
41		07/01/2025	01/01/2027		48,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00
Hoja2

Cell Formulas
Range	Formula
K37:AI41	K37	=IF(AND(K$36>=EOMONTH($G37,-1)+1,K$36<=$H37),$J37/(DATEDIF($G37,$H37,"M")+1),"")

 

[KasperSSI]

Instead of entering the year and month, enter the full date and then change the cell format:
In cells K34, K35 and K36 I have the same date 1/Jan/2025 but the cell format is different, in the first cell the custom format is "M", for the second cell it is "MMM" and the third is "DD/MM/YYYY"

varios 25oct2024.xlsm
	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
34						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
35					2025	ene	feb	mar	abr	may	jun	jul	ago	sep	oct	nov	dic	ene	feb	mar	abr	may	jun	jul	ago	sep	oct	nov	dic	ene
36	Marg	Start Date	End Date	Cost	Bud	01/01/2025	01/02/2025	01/03/2025	01/04/2025	01/05/2025	01/06/2025	01/07/2025	01/08/2025	01/09/2025	01/10/2025	01/11/2025	01/12/2025	01/01/2026	01/02/2026	01/03/2026	01/04/2026	01/05/2026	01/06/2026	01/07/2026	01/08/2026	01/09/2026	01/10/2026	01/11/2026	01/12/2026	01/01/2027
37		01/05/2025	31/10/2025		50,000.00					8,333.33	8,333.33	8,333.33	8,333.33	8,333.33	8,333.33
38		01/04/2025	30/06/2025		5,000.00				1,666.67	1,666.67	1,666.67
39		05/07/2025	25/05/2026		500,000.00							45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55	45,454.55
40		06/01/2025	30/06/2025		5,000,000.00	833,333.33	833,333.33	833,333.33	833,333.33	833,333.33	833,333.33
41		07/01/2025	01/01/2027		48,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00
Hoja2

Cell Formulas
Range	Formula
K37:AI41	K37	=IF(AND(K$36>=EOMONTH($G37,-1)+1,K$36<=$H37),$J37/(DATEDIF($G37,$H37,"M")+1),"")

Thanks - Got it by changing the date format

 

[Peter_SSs]

Thanks - Got it by changing the date format

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.