Note: The other languages of the website are Google-translated. Back to English

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


arrow blue right bubble 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:
doc net work hours 1

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:
doc net work hours 2

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

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:
doc net work hours 4


arrow blue right bubble 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:
doc net work hours 5

doc net work hours 6

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:
doc net work hours 7

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:
doc net work hours 8

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

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.
doc net work hours 10

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:
doc net work hours 11

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:
doc net work hours 12

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!
doc add hour minute second
 
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.
kte tab 201905

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!
officetab bottom
Comments (51)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Really good info but can it be converted into minutes? Thanks Steve
This comment was minimized by the moderator on the site
yes, format the result cell with custom format [mm] instead of [h]:mm
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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).
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
I have the same problem.
Did you get it solved?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
=(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??
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi ,

Can you please share the formula which calculates time including weekends(sat & Sun) also.
This comment was minimized by the moderator on the site
hi ,

Could you please write the formula for calculating time b/w days which includes weekends ( sat n sun )
This comment was minimized by the moderator on the site
Hi,

pls post how to calculate the time b/w two dates which includes weekends also.
This comment was minimized by the moderator on the site
=(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.
This comment was minimized by the moderator on the site
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..
This comment was minimized by the moderator on the site
Hi, do you know where could I find the formula that includes the break you are mentioning? Thank you !
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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"
This comment was minimized by the moderator on the site
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)
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
what if weekend is only sunday
This comment was minimized by the moderator on the site
use the formula =NETWORKDAYS.INTL you will get all the syntax over there for your query
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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"),
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
then it is not an end time :)
This comment was minimized by the moderator on the site
Hello Sir,
Even I am finding the same error as you mentioned above, could you please share the answer if you have

Deva
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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"),
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations