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

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


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
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.
    BasilBase · 3 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 · 2 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 · 3 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 · 5 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. · 5 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 · 6 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