Average Days per month

[VADER73]

Column A is a start date. Column B is an end date. Column C is the number of days between the date in Column A and Column B. Working out the overall average days is easy enough. I am having difficulty working out the average days by month especially since the start and end dates cross over months.

Any help would be much appreciated.

 

[Marcelo Branco]

See if this example helps (a small data sample)
Dates as dd/mm/yyyy

	A​	B​	C​	D​	E​	F​	G​	H​
1​	StartDate​	EndDate​	Days​		Month​	Days​	Months (count)​	Avg​
2​	03/01/2019​	04/01/2019​	1​		1​	51​	3​	17​
3​	06/01/2019​	15/02/2019​	40​		2​	42​	2​	21​
4​	08/01/2019​	28/03/2019​	79​		3​	27​	1​	27​
5​					4​	0​	0​	0​

<tbody>
</tbody>

Formula in C2 copied down
=B2-A2

Months of interest in column E

Array formula in F2 copied down
=SUM(IF(1-(($A$2:$A$4>EOMONTH(DATE(2019,E2,1),0))+(DATE(2019,E2,1)>$B$2:$B$4)),IF(B$2:B$4>EOMONTH(DATE(2019,E2,1),0),1+EOMONTH(DATE(2019,E2,1),0),$B$2:$B$4)-IF($A$2:$A$4>DATE(2019,E2,1),$A$2:$A$4,DATE(2019,E2,1))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in G2 copied down
=SUM(IF(1-(($A$2:$A$4>EOMONTH(DATE(2019,E2,1),0))+(DATE(2019,E2,1)>$B$2:$B$4)),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Regular formula in H2 copied down
=F2/MAX(1,G2)

Hope this helps

M.

 

[VADER73]

Thank you very much! That was perfect.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.