Calculating leave days using NETWORKDAY function.

matthagon1

New Member
Joined
Apr 22, 2024
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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 / TimeEnd TimeShould be
9/26/2024 8:00:009/27/2024 16:00:002
12/2/2024 8:00:0012/6/2024 16:00:004.33
9/27/2024 8:00:009/30/2024 16:00:0021.5
5/21/2024 8:00:005/27/2024 12:00:0053.5
Holidays
5/24/2024
5/25/2024
 
Upvote 0
Take a look at this and see if this fits your scenario. Please confirm the other scenarios I have as well.
editing
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1Start Date / TimeEnd TimeShould beFirst DayMiddle Day(s)Last DayThree Combined (1= 8 hr Shift)
2Thu 2024-09-26 08:00:00Fri 2024-09-27 16:00:0021.00001.0002.0000Holidays
3Mon 2024-12-02 08:00:00Fri 2024-12-06 16:00:004.331.00031.0005.00002024-09-26
4Fri 2024-09-27 08:00:00Mon 2024-09-30 16:00:0021.51.00001.0002.00002024-05-24
5Tue 2024-05-21 08:00:00Mon 2024-05-27 12:00:0053.51.00020.5003.50002024-05-25
6Mon 2024-09-23 08:00:00Fri 2024-09-27 16:00:001.00021.0004.0000
7Tue 2024-09-24 08:00:00Fri 2024-09-27 16:00:001.00011.0003.0000
8Wed 2024-09-25 08:00:00Fri 2024-09-27 16:00:001.00001.0002.0000
9Fri 2024-09-27 08:00:00Fri 2024-09-27 16:00:001.00000.0001.0000
10Mon 2024-09-30 08:00:00Fri 2024-09-27 16:00:001.00001.0002.0000
11Tue 2024-09-24 08:00:00Wed 2024-09-25 16:00:001.00001.0002.0000
12Tue 2024-09-24 08:00:00Fri 2024-09-27 16:00:001.00011.0003.0000
13Tue 2024-09-24 08:00:00Mon 2024-09-30 16:00:001.00021.0004.0000
14Tue 2024-09-24 08:00:00Tue 2024-10-01 16:00:001.00031.0005.0000
15Tue 2024-09-24 08:00:00Wed 2024-10-02 16:00:001.00041.0006.0000
16Tue 2024-09-24 08:00:00Wed 2024-09-25 13:00:001.00000.6251.6250
17Tue 2024-09-24 08:00:00Fri 2024-09-27 13:00:001.00010.6252.6250
18Tue 2024-09-24 08:00:00Mon 2024-09-30 13:00:001.00020.6253.6250
19Tue 2024-09-24 08:00:00Tue 2024-10-01 13:00:001.00030.6254.6250
20Tue 2024-09-24 08:00:00Wed 2024-10-02 13:00:001.00040.6255.6250
21Tue 2024-09-24 12:00:00Fri 2024-09-27 16:00:000.50011.0002.5000
22Tue 2024-09-24 12:00:00Mon 2024-09-30 16:00:000.50021.0003.5000
23Tue 2024-09-24 12:00:00Tue 2024-10-01 16:00:000.50031.0004.5000
24Tue 2024-09-24 12:00:00Wed 2024-10-02 16:00:000.50041.0005.5000
25Tue 2024-09-24 12:00:00Thu 2024-10-03 16:00:000.50051.0006.5000
Sheet1
Cell Formulas
RangeFormula
G2:G25G2=IF(INT(B2)=INT(A2),MIN(8/24,MOD(B2,1)-MOD(A2,1))*3,3*(16/24 - MOD(A2,1)))
H2:H25H2=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:I25I2=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:K25K2=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)))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top