How to check if a date range passes through a month

[Intexcel]

Dear Excel Community,

How can I check if a date range passes through a month and give out the number 1 if it does.

Im an intern at a company and every month i have to give a report for the workers council. In this report I have to count the number of interns and bachelor/Master thesis students for any given month. Any intern that is active in a month gets counted.

I already tried everything including chat gpt but its not working. The biggest problem is:

The trigger point for the count. Of course you can check if a date lies in a certain zone. but not if it passes through. Somebody who starts his internship in september and ends in november also gets counted in my report but excel doesnt notice it since neiter his start date nor his end date are in october.

It doesnt matter if somebody passes through the whole month or just "touches" it on its first or last day. They have to be counted.

Please help

I get a report that looks like this. I already showed the results for you on how it should look for the month of october in the end in the column named "Count towards total":

abbreviation of the corporate branch​	Name Manager​	Name Intern​	Start of Internship​	End of Internship​	​	Count towards total​
​	​	​	​	​	​	​
ASD​	​	​	​	​	​	(2/3)​
​	Manager A​	Intern a​	15.10.23​	20.02.24​	​	1​
​	​	Intern b​	01.02.23​	15.10.24​	​	1​
​	​	intern a (thesis)​	15.10.23​	20.02.24​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
DSF​	​	​	​	​	​	(1/3)​
​	Manager B​	Intern h​	01.04.23​	01.09.23​	​	​
​	​	Intern l​	01.04.23​	01.08.23​	​	​
​	​	intern k​	01.04.23​	01.04.24​	​	1​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
GFD​	​	​	​	​	​	(2/1)​
​	Manager C​	Intern b​	01.06.23​	01.05.24​	​	1​
​	​	Intern d​	01.04.23​	01.01.24​	​	1​

 

[Intexcel]

I forgot to mention that the same student cant be counted twice even if he doesn an internship and also does his thesis in the same time...but thats not the biggest problem

 

[Skybluekid]

Try the below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		ASD						(2/3)				01-Oct	31-Oct
3			Manager A	Intern a	15.10.23	20.02.24		1	1
4				Intern b	01.02.23	15.10.24		1	1
5				intern a (thesis)	15.10.23	20.02.24			1
6									1
7
8
9		DSF						(1/3)
10			Manager B	Intern h	01.04.23	01.09.23			1
11				Intern l	01.04.23	01.08.23			1
12				intern k	01.04.23	01.04.24		1	1
13
14
15
16		GFD						(2/1)
17			Manager C	Intern b	01.06.23	01.05.24		1
18				Intern d	01.04.23	01.01.24		1	1
19
Sheet1

Cell Formulas
Range	Formula
I3:I18	I3	=IF(F3="","",IF(AND(OR(E3>=$L$2,E3<=$M$2,F3>=$L$2,F3<=$M$2),COUNTIF($D$3:D3,D3)<=1),1,""))

 

[Intexcel]

THANK YOU SO MUCH!!!

It works. Thats genius. Im learning new things just by understanding your formular :D, youve helped me a lot!