Day per month between 2 dates

[Dcata]

Hi everyone,

I was wondering if you could help.

Is there a formula I can put in cells D3-G4 that looks at the start and end dates in columns A & B, counts the number of days that used in the month (E2-G2) and then multiplies the number of days by the cost in column C?

If it helps, the value in D2 is currently 31/01/2020 but Formatted to "Mmm"

Thanks a lot

	A	B	C	D	E	F	G
1				Spend per month	Spend per month	Spend per month	Spend per month
2	Start	End	Cost per Day	Jan	Feb	Mar	Apr
3	20/01/2020	04/02/2020	15	165	60
4	27/02/2020	07/04/2020	20		40	620	140

 

[jasonb75]

It is more common to use the first day of the month formatted as mmm than the last day, but either can be made to work.

25.08.20.xlsm
	A	B	C	D	E	F	G
1				Spend per month	Spend per month	Spend per month	Spend per month
2	Start	End	Cost per Day	Jan	Feb	Mar	Apr
3	20/01/2020	04/02/2020	15	165	60	0	0
4	27/02/2020	07/04/2020	20	0	40	620	140
Sheet22

Cell Formulas
Range	Formula
D3:G4	D3	=(MEDIAN(EOMONTH(D$2,0),$A3,$B3)-MEDIAN(EOMONTH(D$2,-1),$A3,$B3))*$C3

 

[mse330]

@jasonb75 excellent utilization of the median function, I have encountered similar case before & had a much, much longer formula to get the same results. Thank you, I have learned something new today

 

[Dcata]

Nice one @jasonb75 !!