how to compute days between dates excel with per month

[ahmedshehta]

Please help me to find an exact formula to extract how many days per month

Start Date	End Date	Total Days	Jan-25	Feb-25	Mar-25
2024/11/22	2025/01/05	45
2025/01/28	2025/01/28	1
2025/01/05	2025/01/09	5
2025/01/23	2025/01/23	1
2025/01/13	2025/01/13	1
2025/01/08	2025/01/09	2
2025/01/16	2025/01/16	1
2024/12/15	2025/01/15	32
2025/01/11	2025/02/10	31
2024/12/11	2025/01/10	31
2025/01/01	2025/01/22	22

 

[FormR]

Hi, this assumes that the months along the top are dates and are the first of each month, formatted as mmm-yy

Book1
	A	B	C	D	E	F
1	Start Date	End Date	Total Days	Jan-25	Feb-25	Mar-25
2	22/11/2024	05/01/2025	45	5	0	0
3	28/01/2025	28/01/2025	1	1	0	0
4	05/01/2025	09/01/2025	5	5	0	0
5	23/01/2025	23/01/2025	1	1	0	0
6	13/01/2025	13/01/2025	1	1	0	0
7	08/01/2025	09/01/2025	2	2	0	0
8	16/01/2025	16/01/2025	1	1	0	0
9	15/12/2024	15/01/2025	32	15	0	0
10	11/01/2025	10/02/2025	31	21	10	0
11	11/12/2024	10/01/2025	31	10	0	0
12	01/01/2025	22/01/2025	22	22	0	0
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=B2-A2+1
D2:F12	D2	=MAX(0,MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1)+1)

 

[ahmedshehta]

Thank you so much Sir I really appreciate it

Please another help employees number 5437 has two rows how to count this in( Total , Jan 25 , Feb 25 )

serial	Employess Number	name	sort	period	Total	Jan-25	Feb-25
From	To
1	5437	saber	أجازة مرضيه مرض مزمن	11/01/2025	10/02/2025	31	21	10
2	5437	saber	أجازة مرضيه مرض مزمن	11/12/2024	10/01/2025	31	10	0
15	205729	ahmed	مرضي	29/01/2025	30/01/2025	2	2	0
16	206136	ahmed	مرضي	02/02/2025	02/02/2025	1	0	1
17	206136	ahmed	مرضي	16/01/2025	16/01/2025	1	1	0

 

[FormR]

Hi, I'm not really following. Is that table you've shown your expected results? Is it columns Jan-25 and Feb-25 you're trying to calculate? If it is, I think you'll need to add a description of how you arrive at those totals.

 

[ahmedshehta]

I'm very sorry

i have attached what i want

How to make it easier

New Microsoft Excel Worksheet (2).xlsx is available for download

 

[FormR]

Hi, something like this.

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E	F	G	H
1	Serial	Employee Number	Name	فترة الاجـــــازة		Total Days	Jan-25	Feb-25
2				From	To
3	1	5437	Ahmed Mohamed	11/01/2025	10/02/2025	31	21	10
4	2	5437	Ahmed Mohamed	11/12/2024	10/01/2025	31	10	0
5	Total					62	31	10
6	3	205548	Adel Mousa	01/01/2025	22/01/2025	22	22	0
7	4	205548	Adel Mousa	23/01/2025	31/01/2025	9	9	0
8	Total					31	31	0
9	5	5446	Mohamed Aly	02/01/2025	01/02/2025	31	30	1
10						31	30	1
ورقة1

Cell Formulas
Range	Formula
F3:F4,F6:F7,F9	F3	=E3-D3+1
G3:H4,G6:H7,G9:H9	G3	=MAX(0,MIN($E3,EOMONTH(G$1,0))-MAX($D3,G$1)+1)
F5:H5,F8:H8	F5	=SUM(F3:F4)
F10:H10	F10	=SUM(F9)

 

[ahmedshehta]

Hi, something like this.

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E	F	G	H
1	Serial	Employee Number	Name	فترة الاجـــــازة		Total Days	Jan-25	Feb-25
2				From	To
3	1	5437	Ahmed Mohamed	11/01/2025	10/02/2025	31	21	10
4	2	5437	Ahmed Mohamed	11/12/2024	10/01/2025	31	10	0
5	Total					62	31	10
6	3	205548	Adel Mousa	01/01/2025	22/01/2025	22	22	0
7	4	205548	Adel Mousa	23/01/2025	31/01/2025	9	9	0
8	Total					31	31	0
9	5	5446	Mohamed Aly	02/01/2025	01/02/2025	31	30	1
10						31	30	1
ورقة1

Cell Formulas
Range	Formula
F3:F4,F6:F7,F9	F3	=E3-D3+1
G3:H4,G6:H7,G9:H9	G3	=MAX(0,MIN($E3,EOMONTH(G$1,0))-MAX($D3,G$1)+1)
F5:H5,F8:H8	F5	=SUM(F3:F4)
F10:H10	F10	=SUM(F9)

Thank you for you kind reply
I don't want a formula here I want to make a quick subtotal
As you see you , Similar employees number may be repeated two times or three times or may be an unique
With that reference I want make subtotal for days in January and Feb

 

[FormR]

Hi, so you're looking for code to insert the sub totals? That seems to be a very different question to the OP and probably deserves its own thread.

 

[ahmedshehta]

Yes I want to insert subtotal with reference Employee Code
Shall I ask for that in another thread ?

Hi, so you're looking for code to insert the sub totals? That seems to be a very different question to the OP and probably deserves its own thread.

 

[FormR]

Shall I ask for that in another thread ?

Yes, new questions should be in new threads