Average number of days between start and end dates that are within a specific month

[ppalmer1]

Hello,

I am trying to write a formula that will calculate the average number of days between start and end dates in a dataset, only including end dates that are within a specific month.

To clarify, if the data is based on the table below (sheet name "Data"), and I only want the average of jobs completed in January, how would I write the formula?

Keep in mind, column C is only for reference in this question and is not in the spreadsheet. I have a separate sheet with this formula to show only the result, without needing to input sub-calculations in each row.

A​	B​	*This column will not be on the spreadsheet. I have a separate sheet with this formula to show only the result, without needing to input sub-calculations in each row.​
Start Date	End Date	Average
1/4/2023	1/6/2023	2
12/28/2022	1/4/2023	6
12/29/2022	12/30/2022	1
		4 (2+6)/2Row 3 should not be included because it ended in December.

The sheet with the formula is using months, formatted as 'mmmm' to reference the criteria.

Here is a table showing what I have tried so far:

A​	B​	Formula Used​
	January​	Call value 1/1/2023, formatted as date (mmmm)
Jobs completed this month​	2​	=COUNTIFS(Data!B:B,">="&$B2,Data!B:B,"<"&EOMONTH($B2,0)+1)
Average time to completion​	Need Formula​	These are the formulas I have attempted, but all have errors: =if((Data!B:B">="&$B2,Data!B:B,"<"&EOMONTH($B2,0)+1),AVERAGE(IF(ISNUMBER(Data!B:B),Data!B:B-Data!A:A)),0) =average(if(and(Data!B:B">="&$B2,Data!B:B"<"&EOMONTH($B2,0)+1),Data!B:B-Data!A:A) =averageif(Data!A:B,(Data!CB:B,">="&$B2,Data!B:B,"<"&EOMONTH($B2,0)+1),Data!A:A-Data!B:B)

I am still learning and I know these formula attempts are terrible. Please forgive my ignorance. Any help would be greatly appreciated.

 

[etaf]

why is row 2 = 6 ?
a sumproduct may help here
But just need to make sure i understand why you get 6 days ........
something like
=SUMPRODUCT((B2:B6>=E1)*(B2:B6<=EOMONTH(E1,0))*((B2:B6)-(A2:A6)))/COUNTIFS(B2:B6,">="&E1,B2:B6,"<="&EOMONTH(E1,0))
Where E1 has the date ie 1/1/23

the other alternative is to have a helper column in the data set and b2-a2 will give the number of days
and then you can use SUMIFS() / countifs()

or as you are version 365 , you have averageifs()

 

[Fluff]

Hi & welcome to MrExcel.
Why is the 2nd row 6 rather than 7?
Maybe

=AVERAGE(IF((Data!B2:B10<=EOMONTH(B2,0))*(Data!B2:B10>=B2),DAYS(Data!B2:B10,Data!A2:A10),""))

 

[ppalmer1]

why is row 2 = 6 ?
a sumproduct may help here
But just need to make sure i understand why you get 6 days ........
something like
=SUMPRODUCT((B2:B6>=E1)*(B2:B6<=EOMONTH(E1,0))*((B2:B6)-(A2:A6)))/COUNTIFS(B2:B6,">="&E1,B2:B6,"<="&EOMONTH(E1,0))
Where E1 has the date ie 1/1/23

the other alternative is to have a helper column in the data set and b2-a2 will give the number of days
and then you can use SUMIFS() / countifs()

or as you are version 365 , you have averageifs()

Thank you. This worked great.
btw, I got 6 because I was just counting the days manually when I typed up this forum question. my mistake.

 

[ppalmer1]

Hi & welcome to MrExcel.
Why is the 2nd row 6 rather than 7?
Maybe

=AVERAGE(IF((Data!B2:B10<=EOMONTH(B2,0))*(Data!B2:B10>=B2),DAYS(Data!B2:B10,Data!A2:A10),""))

Thank you. This worked great.
btw, I got 6 because I was just counting the days manually when I typed up this forum question. my mistake.

 

[Fluff]

Glad we could help & thanks for the feedback.