count days between dates for each month by drop list

[john Smith72]

Hello,
Please I need help in this.
for example:

if john cell (C7) took a vacation for 30 days from 06/04/2024 till 05/05/2024
cell (H7) will show the number of days between those dates for the month shown in cell (C9) by drop list.
I used this formula:
=IF(AND(C$9<MIN($E7;$F7);MONTH($E7)>MONTH(C$9));0;IF(C$9>MAX($E7;$F7); 0;IF(C$9>=MIN(C$9;$E7);MIN(EOMONTH(C$9;0);$F7)-MAX($E7;C$9)+1;-MIN($E7;C$9)+$F7+1)))

But if he cut short the vacation and joined his work in 30/04/2024 cell (G7),
then cell (H7) will only show the number of days between dates that are in cells (E7) and (G7) for the month shown in cell (C9) by drop list,
and don't show number of days for the date in cell (f7) for month 5 (may).

Is this possible and How it will be the formula?
And i want a VBA to auto filter column (H)
Thank You so much

EXAMPLE.xlsm
	A	B	C	D	E	F	G	H	I
1	No.	ID	NAME	DAYS	FROM	TILL	Cut Short	DAYS PER MONTH
2	1	101	SAMI	10	01/04/2024	10/04/2024		10
3	2	102	CAROL	20	02/04/2024	21/04/2024		20
4	3	103	HENRY	40	03/04/2024	12/05/2024		28
5	4	104	DANIEL	90	04/04/2024	02/07/2024		27
6	5	105	WILLIAM	60	05/04/2024	03/06/2024		26
7	6	106	JOHN	30	06/04/2024	05/05/2024	30/04/2024	25
8
9		MONTH	4			Months Drop List
10		LIST
11		1
12		2
13		3
14		4
15		5
16		6
17		7
18		8
19		9
20		10
21		11
22		12
2024

Cell Formulas
Range	Formula
A2:A7	A2	=SUBTOTAL(3,$C$2:C2)
F2:F7	F2	=IFERROR(DATE(YEAR(E2),MONTH(E2),DAY(E2)+D2-1),"-")
H2:H7	H2	=IF(AND(C$9<MIN($E2,$F2),MONTH($E2)>MONTH(C$9)),0,IF(C$9>MAX($E2,$F2), 0,IF(C$9>=MIN(C$9,$E2),MIN(EOMONTH(C$9,0),$F2)-MAX($E2,C$9)+1,-MIN($E2,C$9)+$F2+1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H8	Cell Value	>0	text	NO
H2:H8	Cell Value	<=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C9	List	=$B$11:$B$22

 

[Barney bull]

Try this formula
Sum(EOmonth(E2,0)-D10)
not sure if this will help but give it a try,

 

[Barney bull]

Sorry misunderstood your question

 

[john Smith72]

Try this formula
Sum(EOmonth(E2,0)-D10)
not sure if this will help but give it a try,​

Sorry Barney this don't work.
thank you for trying help.

 

[Tetra201]

See if the following formula works for you:

=MAX(0,MIN(F2,IF(G2="",99999,G2),EOMONTH($C$9,0))-MAX(E2,$C$9)+1)

 

[john Smith72]

See if the following formula works for you:

=MAX(0,MIN(F2,IF(G2="",99999,G2),EOMONTH($C$9,0))-MAX(E2,$C$9)+1)

 

[john Smith72]

Thank you so much Tetra201, it works prfectly, I appreciate you.
But please I still need your help to stop cell H2 from counting days if cell E2 is without date.

See if the following formula works for you:

=MAX(0,MIN(F2,IF(G2="",99999,G2),EOMONTH($C$9,0))-MAX(E2,$C$9)+1)

 

[Tetra201]

Try

=MAX(0,MIN(F2,IF(G2="",99999,G2),EOMONTH($C$9,0))-MAX(IF(COUNT(E2),E2,999999),$C$9)+1)

 

[john Smith72]

Try

=MAX(0,MIN(F2,IF(G2="",99999,G2),EOMONTH($C$9,0))-MAX(IF(COUNT(E2),E2,999999),$C$9)+1)

Thank you very very very much for you, it works, you are fantastic

 

[Tetra201]

Glad to have helped