Calculating leave days using NETWORKDAY function.

[matthagon1]

Hello

I am building a spreadsheet to calculate how much leave employees have taken. I have used this formula so far =NETWORKDAYS(start_date, end_date, [holidays]). However as some leave periods are for part of a day, I need a formula to work out a period, such as 1.5.
The working day is generally 8:30 until 16:00, would I have to work this into the formula somehow?

 

[matthagon1]

are they to be excluded like the holiday?

Yes they are.

 

[awoohaw]

Yes they are.

Your examples have ending dates on weekend days, though. How do you want those handled?

 

[matthagon1]

Your examples have ending dates on weekend days, though. How do you want those handled?

Mistake on my part, I have changed the dates and new figures are below.

Start Date / Time	End Time		Should be
9/26/2024 8:00:00	9/27/2024 16:00:00	2
12/2/2024 8:00:00	12/6/2024 16:00:00	4.33
9/27/2024 8:00:00	9/30/2024 16:00:00	2	1.5
5/21/2024 8:00:00	5/27/2024 12:00:00	5	3.5
			Holidays
			5/24/2024
			5/25/2024

 

[awoohaw]

Take a look at this and see if this fits your scenario. Please confirm the other scenarios I have as well.
editing

 

[awoohaw]

Sorry, I had an error in the earlier post and could not correct before the 10 minute correction window expired. I think this works. But, I think you may have had some errors in some of your scenarios. Take a look and also verify them all.... (also note I put a holiday in of 9/26, so that messes up your first scenario since it starts on a holiday date - i hope that doesn't confuse you).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Start Date / Time	End Time			Should be		First Day	Middle Day(s)	Last Day		Three Combined (1= 8 hr Shift)
2	Thu 2024-09-26 08:00:00	Fri 2024-09-27 16:00:00		2			1.000	0	1.000		2.0000						Holidays
3	Mon 2024-12-02 08:00:00	Fri 2024-12-06 16:00:00		4.33			1.000	3	1.000		5.0000						2024-09-26
4	Fri 2024-09-27 08:00:00	Mon 2024-09-30 16:00:00		2	1.5		1.000	0	1.000		2.0000						2024-05-24
5	Tue 2024-05-21 08:00:00	Mon 2024-05-27 12:00:00		5	3.5		1.000	2	0.500		3.5000						2024-05-25
6	Mon 2024-09-23 08:00:00	Fri 2024-09-27 16:00:00					1.000	2	1.000		4.0000
7	Tue 2024-09-24 08:00:00	Fri 2024-09-27 16:00:00					1.000	1	1.000		3.0000
8	Wed 2024-09-25 08:00:00	Fri 2024-09-27 16:00:00					1.000	0	1.000		2.0000
9	Fri 2024-09-27 08:00:00	Fri 2024-09-27 16:00:00					1.000	0	0.000		1.0000
10	Mon 2024-09-30 08:00:00	Fri 2024-09-27 16:00:00					1.000	0	1.000		2.0000
11	Tue 2024-09-24 08:00:00	Wed 2024-09-25 16:00:00					1.000	0	1.000		2.0000
12	Tue 2024-09-24 08:00:00	Fri 2024-09-27 16:00:00					1.000	1	1.000		3.0000
13	Tue 2024-09-24 08:00:00	Mon 2024-09-30 16:00:00					1.000	2	1.000		4.0000
14	Tue 2024-09-24 08:00:00	Tue 2024-10-01 16:00:00					1.000	3	1.000		5.0000
15	Tue 2024-09-24 08:00:00	Wed 2024-10-02 16:00:00					1.000	4	1.000		6.0000
16	Tue 2024-09-24 08:00:00	Wed 2024-09-25 13:00:00					1.000	0	0.625		1.6250
17	Tue 2024-09-24 08:00:00	Fri 2024-09-27 13:00:00					1.000	1	0.625		2.6250
18	Tue 2024-09-24 08:00:00	Mon 2024-09-30 13:00:00					1.000	2	0.625		3.6250
19	Tue 2024-09-24 08:00:00	Tue 2024-10-01 13:00:00					1.000	3	0.625		4.6250
20	Tue 2024-09-24 08:00:00	Wed 2024-10-02 13:00:00					1.000	4	0.625		5.6250
21	Tue 2024-09-24 12:00:00	Fri 2024-09-27 16:00:00					0.500	1	1.000		2.5000
22	Tue 2024-09-24 12:00:00	Mon 2024-09-30 16:00:00					0.500	2	1.000		3.5000
23	Tue 2024-09-24 12:00:00	Tue 2024-10-01 16:00:00					0.500	3	1.000		4.5000
24	Tue 2024-09-24 12:00:00	Wed 2024-10-02 16:00:00					0.500	4	1.000		5.5000
25	Tue 2024-09-24 12:00:00	Thu 2024-10-03 16:00:00					0.500	5	1.000		6.5000
Sheet1

Cell Formulas
Range	Formula
G2:G25	G2	=IF(INT(B2)=INT(A2),MIN(8/24,MOD(B2,1)-MOD(A2,1))*3,3*(16/24 - MOD(A2,1)))
H2:H25	H2	=IF(INT(B2)<=WORKDAY.INTL(INT(A2),1,1,$Q$3:$Q$5),0,NETWORKDAYS.INTL(WORKDAY.INTL(INT(A2),1,1,$Q$3:$Q$5),INT(B2),1,$Q$3:$Q$5)-1)
I2:I25	I2	=IF(WORKDAY.INTL(INT(B2)-1,1,1,$Q$3:$Q$5)-INT(A2)=0,0,3*(MIN(MOD(B2,1),16/24)-(8/24)))
K2:K25	K2	=IF(INT(B2)=INT(A2),MIN(8/24,MOD(B2,1)-MOD(A2,1))*3,3*(16/24 - MOD(A2,1))) + IF(INT(B2)<=WORKDAY.INTL(INT(A2),1,1,$Q$3:$Q$5),0,NETWORKDAYS.INTL(WORKDAY.INTL(INT(A2),1,1,$Q$3:$Q$5),INT(B2),1,$Q$3:$Q$5)-1)+ IF(WORKDAY.INTL(INT(B2)-1,1,1,$Q$3:$Q$5)-INT(A2)=0,0,3*(MIN(MOD(B2,1),16/24)-(8/24)))