Need help with a formula that can prorate

[Sean15]

Hi,

I am trying to calculate prorate amounts where applicable in column L. So, I’m looking for a formula that looks at Date in H1, if Date in range H3:H8 is less than H1, return zero, if Date in range H3:H8 falls with MONTH (H1), return prorate of amount in column J, all else return value in column J.

Book1
	H	I	J	K	L	M
1	5/1/2022				Required
2					value
3	07/05/22		1,575.00		1,575.00
4	08/16/22		1,628.00		1,628.00
5	09/25/23		4,582.00		4,582.00
6	05/12/22		3,575.00		2,191.13	date in H6 in within current month, so prorate 19 days
7	05/30/22		2,572.00		165.94	date in H7 in within current month, so prorate 2 days
8	04/15/22		1,452.00		N/A	date is less than H1, return zero
9
10
Sheet1

Cell Formulas
Range	Formula
L3:L5	L3	=J3
L6	L6	=19/31*J6
L7	L7	=2/31*J7

Thanks for your help.

Regards,
Sean

 

[Jon Peltier]

Do a search for Excel Interpolate (which is the mathematical term for "prorate").

 

[Sean15]

Hi, I've searched Excel Interpolate but I can't find an example that is related to mine. Anyone else?

Sean

 

[Jon Peltier]

Okay, here's another look.

First, you either want to prorate 5/12 and 5/30 by 19 and 1 days or 20 and 2 days. I'll assume 19 and 1 days.

Put this formula in L3:

=IFS(H3<$H$1,0,H3>EOMONTH($H$1,0),J3,TRUE,(EOMONTH($H$1,0)-H3)/(EOMONTH($H$1,0)+1-$H$1)*J3)

If you want to prorate 20 and 2 days, use this formula instead:

=IFS(H3<$H$1,0,H3>EOMONTH($H$1,0),J3,TRUE,(EOMONTH($H$1,0)+1-H3)/(EOMONTH($H$1,0)+1-$H$1)*J3)