Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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


Count Weekdays/Weekends/Specific Weekday between two dates 

Supposing you have a two given dates, one is start date and another is end date, how can you quickly count the workday, weekend or a specific weekday such as Monday between thses date range? If you are interesed, you can try on Kutools for Excel's Count weekdays/weekends/specific weekday utilities.
doc count weekday weekend
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

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.

doc download 1


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.
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
Say something here...
symbols left.
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.
    CL · 11 days 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"))))
  • To post as a guest, your comment is unpublished.
    Chad · 2 months ago
    I am getting incorrect values when I enter in this equation, trying to look at open and close times, same as work hours.
  • To post as a guest, your comment is unpublished.
    karthik · 2 months 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.
  • To post as a guest, your comment is unpublished.
    George · 3 months ago
    Works greet in excel.. trying to replicated the same logic in Power BI but having issues. Any body has an idea?
  • To post as a guest, your comment is unpublished.
    BasilBase · 7 months 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!
    • To post as a guest, your comment is unpublished.
      RB · 6 months 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 :-)
  • To post as a guest, your comment is unpublished.
    blessing · 7 months 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
  • To post as a guest, your comment is unpublished.
    AydinB · 9 months 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"),
  • To post as a guest, your comment is unpublished.
    J.M. · 10 months 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
  • To post as a guest, your comment is unpublished.
    Pedro · 10 months 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
    • To post as a guest, your comment is unpublished.
      Ranjith Kumar Reddy · 4 months ago
      Hello Sir,
      Even I am finding the same error as you mentioned above, could you please share the answer if you have

      Deva
    • To post as a guest, your comment is unpublished.
      Alex Hart · 7 months ago
      then it is not an end time :)
  • To post as a guest, your comment is unpublished.
    Cherry · 10 months 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"),
  • To post as a guest, your comment is unpublished.
    Jack · 1 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?
  • To post as a guest, your comment is unpublished.
    narender kumar · 1 years ago
    what if weekend is only sunday
    • To post as a guest, your comment is unpublished.
      mahadev · 1 years ago
      use the formula =NETWORKDAYS.INTL you will get all the syntax over there for your query
  • To post as a guest, your comment is unpublished.
    Sebastian · 1 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!
    • To post as a guest, your comment is unpublished.
      Sandra · 1 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!
  • To post as a guest, your comment is unpublished.
    Nawab · 1 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?
  • To post as a guest, your comment is unpublished.
    kara m · 1 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"
    • To post as a guest, your comment is unpublished.
      deb · 1 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)
  • To post as a guest, your comment is unpublished.
    Yesu · 1 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?
  • To post as a guest, your comment is unpublished.
    Jenny Cruz · 1 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..
    • To post as a guest, your comment is unpublished.
      Sebastian · 1 years ago
      Hi, do you know where could I find the formula that includes the break you are mentioning? Thank you !
  • To post as a guest, your comment is unpublished.
    vishnu k · 2 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.
  • To post as a guest, your comment is unpublished.
    Rahul · 2 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??
    • To post as a guest, your comment is unpublished.
      manohar · 2 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
      • To post as a guest, your comment is unpublished.
        appa · 1 years ago
        Hi,

        pls post how to calculate the time b/w two dates which includes weekends also.
      • To post as a guest, your comment is unpublished.
        appa · 1 years ago
        hi ,

        Could you please write the formula for calculating time b/w days which includes weekends ( sat n sun )
      • To post as a guest, your comment is unpublished.
        appa · 1 years ago
        Hi ,

        Can you please share the formula which calculates time including weekends(sat & Sun) also.
  • To post as a guest, your comment is unpublished.
    KMYounis · 2 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.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 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
  • To post as a guest, your comment is unpublished.
    Claire · 2 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.
    • To post as a guest, your comment is unpublished.
      Z. · 1 years ago
      I have the same problem.
      Did you get it solved?
  • To post as a guest, your comment is unpublished.
    Ankit · 2 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.
  • To post as a guest, your comment is unpublished.
    Ankit · 2 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).
  • To post as a guest, your comment is unpublished.
    Adis Samardzic · 2 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
  • To post as a guest, your comment is unpublished.
    Steve · 2 years ago
    Really good info but can it be converted into minutes?

    Thanks
    Steve
    • To post as a guest, your comment is unpublished.
      Melinda · 2 years ago
      yes, format the result cell with custom format [mm] instead of [h]:mm
  • To post as a guest, your comment is unpublished.
    Deepinderpal · 2 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