Apportion a value by day rate across months from a start and end date?

[mountainman88]

Hi folks,

What is a formula I can put in cell D2 and drag across to cell O2 which will take the value in cell C1 and apportion by day (start date minus end date, inclusive of both) to the appropriate month?

Thanks!

 

[KillerOfGiants]

You might try this formula. I think it is what you seek. There may be better ways, but this seems to work (and accounts for years as well as months so if the start and end dates weren't in the same year).

=IF(AND(EOMONTH($A2,0)<=D1,EOMONTH($B2,0)>=D1),$C2/(1+YEAR($B2)*100+MONTH($B2)-YEAR($A2)*100-MONTH($A2)),0)

 

[KillerOfGiants]

Sorry. My previous formula went by month. I think this is more what you seek. It calculates a daily value and then multiplies by the number of days within the months range (so 24 days for much, all of April and May, and 19 days for June).

=IF(OR(EOMONTH($A2,0)>D1,EOMONTH($B2,0)<D1),0,($C2/DAYS($B2,$A2-1))*IF(EOMONTH($A2,0)=D1,DAY(D1)-DAY($A2)+1,IF(EOMONTH($B2,0)=D1,DAY($B2),DAY(D1))))

 

[FormR]

Hi, here's another option that you can also try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				01/31/2023	02/28/2023	03/31/2023	04/30/2023	05/31/2023	06/30/2023	07/31/2023	08/31/2023	09/30/2023	10/31/2023	11/30/2023	12/31/2023	Check
2	03/08/2023	06/19/2023	1000000	0	0	230769.23	288461.54	298076.92	182692.31	0	0	0	0	0	0	1000000
3	01/01/2023	12/31/2023	365	31	28	31	30	31	30	31	31	30	31	30	31	365
Sheet1

Cell Formulas
Range	Formula
E1:O1	E1	=EOMONTH(D1,1)
D2:O3	D2	=$C2/($B2-$A2+1)*MAX(MIN(D$1,$B2)-MAX(EOMONTH(D$1,-1),$A2-1),0)
P2:P3	P2	=SUM(D2:O2)