How to calculate net work hours between two dates excluding weekends or holidays in Excel?

In many companies, staff are paid by working hours. To calculate the net work hours in a day is easy, but how about calculating net hours in a date range? For that, this article, introduces the formulas on calculating the net work hours between two dates excluding weekends and holidays in Excel.

Calculate workday exclude weekends

Calculate working hours exclude weekends/holidays

Calculate workday exclude weekends

In this part, I introduce the formula to calculate workday between two date times excluding weekends.

1. Select two cells which you will input the start date time and end date time, and right click to select Format Cells form the context menu. See screenshot:

2. In the Format Cells dialog, click Number tab, and select Custom form the Category list, and enter m/d/yyyy h:mm into the Type textbox in right section. See screenshot:

3. Click OK. And enter the start date time and end date time into the two cells separately. See screenshot:

4. In the cell next to these two cells, C13, for instance, enter this formula =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1), and press Enter key, and you will get the result with custom format, select the result cell, and click Home tab, and go to the Number Format list to select General to format it as the correct format. See screenshot:

Calculate working hours exclude weekends/holidays

If you want to calculate the net working hours excluding weekends or holidays, you can do as bellow:

Calculate net working hours exclude weekends

1. Select two cells and format them as custom format m/d/yyyy h:mm, and enter the start date time and end date time. See screenshot:

2. And in the next to cell, C2 for instance, enter this formula,
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),
press Enter key, then you get a number string. See screenshot:

3. Right click at the number string, and click Format Cells from context menu, and in Format Cells dialog, select Custom form Category list under Number tab, and enter this [h]:mm into Type textbox. See screenshot:

4. Click OK. Now the net working hours between two dates excluding weekends are counted.

Tip: In the formula, A2 is the start date time, B2 is the end date time, 8:30 and 17:30 are the general start time and end time in each day, you can change them as you need.

Calculate net working hours excluding weekend and holidays

1. As the same as above, select two cells and format them as custom format m/d/yyyy h:mm, and enter the start date time and end date time.

2. Select a blank cell, and enter the holiday date into it, here I have 3 holidays and I type them separately in H1:H3. See screenshot:

3. Select a blank cell which will place the counted result, C2 for instance,
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
and press Enter key, you will get a number string, and format it as custom format [h]:mm. See screenshot:

Tip: In the formula, A2 is the start date time, B2 is the end date time, 8:30 and 17:30 are the general start time and end time in each day, H1:H3 is the holiday cells, you can change them as you need.

Easily add days/years/month/hours/minutes/seconds to a datetime in Excel

Supposing you have a date time format data in a cell, and now you need to add a number of days, years, months, hours, minutes, or seconds to this date. Normally, using formula is the first method for all Excel users, but it’s hard to remember all formulas. With Kutools for Excel’s Date & Time Helper utility, you can easily add days, years, months, or hours, minutes or seconds to a date time, moreover, you can caculate the date difference, or the age based on a given birthday without remembering the formula at all. Click for full-featured free trial in 30 days!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

43 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Gagan · 3 months ago
can anyone help, i want to extract resolution time excluding night hours (e.g., 6PM to 6AM or 05PM to 8AM) including holidays (i.e., this industry works 24*7 hence does not want to exclude holidays/weekends).
- Reply
To post as a guest, your comment is unpublished.
Fiona · 3 months ago
Whenever I do both calculations, my "work hours excluding weekends and holidays" comes out a lot higher than my "work hours excluding weekends" calculation. Anyone know why this might be?

Start date: 1/9/2020 9:00
End date: 28/5/2021 17:00
*Work hours ex weekends:* 1552:00
Formula:

=(NETWORKDAYS(A4,B4)-1)*("17:00"-"9:00")+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),"17:00","9:00"),"17:00")-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),"17:00","9:00")

Start date: 1/9/2020 9:00
End date: 28/5/2021 17:00
*Work hours ex weekends and holidays:* 1808:00

Holidays:

25/12/2020
28/12/2020
01/01/2021
02/04/2021
05/04/2021
03/05/2021
31/05/2021

Formula:

=(NETWORKDAYS.INTL(A7,B7,11,B$13:B$19)-1)*("17:00"-"9:00")+IF(NETWORKDAYS.INTL(B7,B7,11,B$13:B$19),MEDIAN(MOD(B7,1),"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(A7,A7,11,B$13:B$19)*MOD(A7,1),"9:00","17:00")

Thoughts?
- Reply
To post as a guest, your comment is unpublished.
david udemezue · 7 months ago
hello,
if i have my start date, start time, end date and end time, how do i calculate for net hours used taking into consideration that work hours is 7hrs 30mins, and also weekends.
- Reply
To post as a guest, your comment is unpublished.
CL · 1 years ago
PLEASE NOTE that the work day and time formula does NOT WORK if the time of the start value (i.e. 11am) is greater than the time in the second value (i.e. 9am). It will give a negative value which will deduct these hours from the work time. To capture that properly, you need to add another condition to the formula. IF(IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30") <0, TIME("17:30"-"8:30",0,0)+(IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"))),IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-(MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"))))
- Reply
To post as a guest, your comment is unpublished.
Chad · 1 years ago
I am getting incorrect values when I enter in this equation, trying to look at open and close times, same as work hours.
- Reply
To post as a guest, your comment is unpublished.
karthik · 1 years ago
Ho to calculate time difference between date and time from below date and time excluding weekends.
Start date 12/31/2022 9:04 End Date 1/3/2023 8:07 in HH:MM:SS format.
- Reply
To post as a guest, your comment is unpublished.
George · 1 years ago
Works greet in excel.. trying to replicated the same logic in Power BI but having issues. Any body has an idea?
- Reply
To post as a guest, your comment is unpublished.
BasilBase8 · 1 years ago
I have edited your formula for work hours being 8am to 8pm. In my example, A2 is the start date time, B2 is the end date time, 8:00 and 20:00 are the start and end times each day, H$1:H$8 is the holiday cells.

When the date range does not include a weekend, the formula works perfectly, but when the date range includes a weekend day, the result is always 12:00 greater than it should be

The formula I'm using is =(NETWORKDAYS.INTL(A2,B2,11,H$1:H$8)-1)*("20:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$8),MEDIAN(MOD(B2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$8)*MOD(A2,1),"8:00","20:00")

Here are 2 examples (there aren't any holidays (H$1:H$8) in these date ranges)
Example 1
Start - 2/6/2019 8:46
End - 2/11/2019 8:40
Value Using Formula - 47:54:00
Correct Value - 35:54:00
Difference - 12:00
note date range has weekend date in it

Example 2
Start - 2/6/2019 19:26
End - 2/8/2019 16:15
Value Using Formula - 20:49
Correct Value - 20:49
Difference - 00:00
note date range has no weekend dates in it

Thanks for any help you can give!
- Reply
- To post as a guest, your comment is unpublished.
  RB · 1 years ago
  
  Had same issue, but with help of feedback 2 months ok of AydinB was able to fix it.
  
  Formula includes Saturday. Number 11 in
  Means Mon-Sat. Replace 11 with 1 for Mon-Fri :-)
  
  Reply
To post as a guest, your comment is unpublished.
blessing · 1 years ago
hello i need help in calculating working hours in a day within some period of time. take for instance:

work hours is 8am to 5pm. and i need to calculate this for 10 days. what formula can i use
- Reply
To post as a guest, your comment is unpublished.
AydinB · 1 years ago
Formula includes Saturday. Number 11 in

NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)

means Mon-Sat. Replace 11 with 1 for Mon-Fri :-)

=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
- Reply
To post as a guest, your comment is unpublished.
J.M. · 1 years ago
Hello

I used your formula "=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30")" works great

But i get with this data negative Errors even using "1904 date system".

The sample data is: 01.10.2018 15:10 / 03.10.2018 11:15 (date format t.m.jjjj hh:mm)

The error is: data and times that are negative or too large show as #######

Do you have an idea.

Juan
- Reply
To post as a guest, your comment is unpublished.
Pedro · 1 years ago
Hi everyone,

There is an error on this formula:

=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),

If the end time is greater than the start time, the formula give us the wrong value. We have to pay attention to that.

But the formula was very helpful! Thanks
- Reply
- To post as a guest, your comment is unpublished.
  ranjith.deva@gmail.com · 1 years ago
  
  Hello Sir,
  Even I am finding the same error as you mentioned above, could you please share the answer if you have
  
  Deva
  
  Reply
- To post as a guest, your comment is unpublished.
  Alex Hart · 1 years ago
  
  then it is not an end time :)
  
  Reply
To post as a guest, your comment is unpublished.
Cherry · 1 years ago
what is "11" in the formula --> (NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
- Reply
To post as a guest, your comment is unpublished.
Jack · 2 years ago
Morning,

I4 = Date Start MFG (9/14/18 8:00)
J4:M4 = Estimated Hours of Work (28)
N4 = Sum(J4:M4)/8 with the 8 representing the hours of work to calculate the # of days required
O4 = MFG Lead-Time.. this is where I'm having an issue..

What I want the spreadsheet to do is to tell me when the job is going to finish; more specifically, the time. However, I'm not sure how to write the formula so it only counts 7:00-17:00 and excludes 17:00-7:00.

Right now, I have a 3.5 day LT beginning at 9/14/18 8:00 and the output is giving me 09/17/18 20:00. But I can't have a 20:00 because it's outside the standard hours of work. The desired result should be 09/17/18 12:00.

Start Date: 09/14/18 8:00AM - 17:00PM is 1, 9/15/18 8:00AM - 17:00PM is 2, 9/16/18 8:00AM - 17:00PM is 3, 9/17/18 8:00AM - 12:00PM is 3.5.. any ideas?
- Reply
To post as a guest, your comment is unpublished.
narender kumar · 2 years ago
what if weekend is only sunday
- Reply
- To post as a guest, your comment is unpublished.
  mahadev · 2 years ago
  
  use the formula =NETWORKDAYS.INTL you will get all the syntax over there for your query
  
  Reply
To post as a guest, your comment is unpublished.
Sebastian · 2 years ago
Hi, I'm trying to find the formula that include the break time from 12pm to 1:30pm base on your working hours of 8am to 5pm. is it posted? thank you so much for your help, this Blog has helped me a lot!
- Reply
- To post as a guest, your comment is unpublished.
  Sandra · 2 years ago
  
  Any luck with this? I am also looking for a formula to subtract one hour per day, but not necessarily a set time. In my setting, the work day is 8-5, but only 8 hours are counted. Thanks!
  
  Reply
To post as a guest, your comment is unpublished.
Nawab · 2 years ago
hi, i need to compare a login time if its in between a range of time in case if its fall time of range 1 then authorization time of same login is within specific time of range 1 and we have 3 to 4 range of different timings and its authorization respectively?
can any one help?
- Reply
To post as a guest, your comment is unpublished.
kara m · 2 years ago
This formula works to return the net working hours and minutes. How can I convert hours to days, based on an eight hour work day? For example, the result "0 day, 18 hours, 45 minutes" should be "2 days, 2 hours, 45 minutes"
- Reply
- To post as a guest, your comment is unpublished.
  deb · 2 years ago
  
  divide the hours by 8 instead of 24 as working hours is 8hrs for your team. so 18 hrs 45 mins/ 8 hrs= 2 days 2 hrs 45 mins (2 days = 2*8 = 16hrs)
  
  Reply
To post as a guest, your comment is unpublished.
Yesu · 2 years ago
Hi, for me some values for start/end time falls on weekends or time outside the defined window. For these cells, the value is showing as 00:00:00. Is there a way to correct it?
- Reply
To post as a guest, your comment is unpublished.
jennycruz0711@gmail.com · 2 years ago
Hi there, Thank you very much your formula it really helps me a lot with my work. but my challenge is how can you removed the break time from 12pm to 1:30pm base on your working hours of 8am to 5pm. It really means a lot to me if you will solved my problem. please help..
- Reply
- To post as a guest, your comment is unpublished.
  Sebastian · 2 years ago
  
  Hi, do you know where could I find the formula that includes the break you are mentioning? Thank you !
  
  Reply
To post as a guest, your comment is unpublished.
vishnu k · 3 years ago
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),

could you please explain how this works.
- Reply
To post as a guest, your comment is unpublished.
Rahul · 3 years ago
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),

What is number 11 in the above formula.??

And also, How to write the holiday in formula if I have only one holiday in the month??
- Reply
- To post as a guest, your comment is unpublished.
  manohar · 3 years ago
  
  Number 11 (Sunday as weekend) refers to weekend number
  Weekend number Weekend days
  1 or omitted Saturday, Sunday
  2 Sunday, Monday
  3 Monday, Tuesday
  4 Tuesday, Wednesday
  5 Wednesday, Thursday
  6 Thursday, Friday
  7 Friday, Saturday
  11 Sunday only
  12 Monday only
  13 Tuesday only
  14 Wednesday only
  15 Thursday only
  16 Friday only
  17 Saturday only
  
  Reply
  
  To post as a guest, your comment is unpublished.
  appa · 2 years ago
  
  Hi,
  
  pls post how to calculate the time b/w two dates which includes weekends also.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  appa · 2 years ago
  
  hi ,
  
  Could you please write the formula for calculating time b/w days which includes weekends ( sat n sun )
  
  Reply
  
  To post as a guest, your comment is unpublished.
  appa · 2 years ago
  
  Hi ,
  
  Can you please share the formula which calculates time including weekends(sat & Sun) also.
  
  Reply
To post as a guest, your comment is unpublished.
KMYounis · 3 years ago
Hi,
I have been trying to use this function to get minutes between two days excluding holidays and weekends. No matter what I always get 0 minutes in my answer. I will be more than happy to share my excel file if needed.

Your help will be greatly appreciated.
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 3 years ago
  
  Thanks for ur message. For get minutes between two dates excluding holidays and weekends, you just need to change [h]:mm to [mm] in the last step
  
  1.png
  
  Reply
To post as a guest, your comment is unpublished.
Claire · 3 years ago
Thanks, in general this formula is working great for me thank you but how do I do it for time periods which exceed a month?

My formula is this:

=(NETWORKDAYS(L22,M22,BankHols17to21)-1)*("18:00"-"8:00")+IF(NETWORKDAYS(M22,M22,BankHols17to21),MEDIAN(MOD(M22,1),"18:00","8:00"),"18:00")-MEDIAN(NETWORKDAYS(L22,L22)*MOD(L22,1),"18:00","8:00")

So these two dates show correctly as 11 working hours:

18/05/2017 08:00 AM 19/05/17 09:00 AM

But this one which is over a year difference between the dates shows ups 17 days 8 hours:

18/05/17 00:00 28/05/18 09:00

Thank you.
- Reply
- To post as a guest, your comment is unpublished.
  Z. · 2 years ago
  
  I have the same problem.
  Did you get it solved?
  
  Reply
To post as a guest, your comment is unpublished.
Ankit · 3 years ago
Hi,

Does this formula work in excel 2007. Because i tried to use the formula for calculating net working hours excluding weekend and holidays, but its not working. I am getting "#NAME?" as output. I am using it to calculate from 9 AM to 6 PM(18:00). Please help.
- Reply
To post as a guest, your comment is unpublished.
Ankit · 3 years ago
Hi,

Does this formula work in excel 2007. Because i tried to use the formula for calculating net working hours excluding weekend and holidays, but its not working. I am getting "#NAME?" as output. I am using it to calculate from 9 AM to 6 PM(18:00).
- Reply
To post as a guest, your comment is unpublished.
Adis Samardzic · 3 years ago
Hello I need help on same topic

In my case I have defined starting date and time (dd.mm.yy and hh.mm) and I have hours needed for some material to be produced (ex total 17 hours)

My problem is how to subtract non working hours from total time needed third shift is not working from 0:00 AM - 7:00AM

Please help
- Reply
To post as a guest, your comment is unpublished.
Steve · 3 years ago
Really good info but can it be converted into minutes?

Thanks
Steve
- Reply
- To post as a guest, your comment is unpublished.
  Melinda · 3 years ago
  
  yes, format the result cell with custom format [mm] instead of [h]:mm
  
  Reply
To post as a guest, your comment is unpublished.
Deepinderpal · 3 years ago
I have multiple dates I need to use to calculate the hrs spent on the iteration
A - request received : 1/14/17 3:43
B - request ended : 1/16/17 23:03
C - clarification sent : 1/16/17 20:41
D - clarification received : 1/16/17 22:38
I need to find the difference B-A = E and then D-C = F
now E-F should give me no. of hrs spend on this work which needs to be below 24 hrs
- Reply