Tip: Other languages are Google-Translated. You can visit the English version of this link.

get work hours between two dates in Excel

Normally, you may need to calculate the salary income for the workers, in this case, you should get the work hours between the two given dates which exclude the weekends and holidays firstly. The NETWORKDAYS function in Excel can help you to get the net workdays between two dates, and then multiply the number of working hours per workday to get the total work hours.


Calculate work hours between two dates exclude default weekends

If you want to get the work hours between two dates which exclude the default weekends (Saturday and Sunday), you can use a formula based on the NETWORKDAYS function as the following generic syntax:

=NETWORKDAYS(start_date,end_date) * working_hours
  • start_date, end_date: The first and last dates to calculate the workdays between.
  • working_hours: The number of work hours in each workday. (Normally, the work hour is 8 for each workday, you can change it to your need.)

1. Please apply the below formula to calculate the work hours:

=NETWORKDAYS(A2,B2 )*8

And then, drag the fill handle to the cells you want to paste this formula, and you will get a list of dates result, see screenshot:

2. Then, you should format the date cells to general format cell, please select the calculated cells, and then click General from the Number Format drop down under the Home tab, then you will get the working hours as following screenshot shown:


Calculate work hours between two dates exclude both weekends and holidays

If there are holiday dates within the two dates, to exclude these holidays at the same time, how could you solve this task in Excel?

Generic syntax:

=NETWORKDAYS(start_date, end_date, [holidays]) * working_hours
  • start_date, end_date: The first and last dates to calculate the workdays between.
  • holidays: A range of date cells that you want to exclude from the two dates.
  • working_hours: The number of work hours in each workday. (Normally, the work hour is 8 for each workday, you can change it to your need.)

1. Please use the following formula into a blank cell where you want to output the result:

=NETWORKDAYS(A4,B4,$E$4:$E$6 )*8

And then drag the fill handle to fill the formula to the cells you need, a list of date format is displayed, see screenshot:

2. Then, you should format the date cells to general format, and you will get the following result:


Calculate work hours between two dates exclude custom weekends

Sometime, you would like to calculate the work hours between two dates excluding some custom weekends, such as only Sunday or Sunday and Monday without taking holidays into consideration. In this case, you should use the NETWORKDAYS.INTL function in Excel. The formula syntax is:

=NETWORKDAYS.INTL(start_date, end_date, [weekend]) * working_hours
  • start_date, end_date: The first and last dates to calculate the workdays between.
  • weekend: The specific days of the week that you want to set as weekends instead of the default weekends. It can be a weekend number or string.
  • working_hours: The number of work hours in each workday. (Normally, the work hour is 8 for each workday, you can change it to your need.)

Weekend number values indicate the following weekend days:

Weekend Number Weekend Days
1 or omitted Saturday and Sunday
2 Sunday and Monday
3 Monday and Tuesday
4 Tuesday and Wednesday
5 Wednesday and Thursday
6 Thursday and Friday
7 Friday and Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

This example, I will calculate the work hours between two dates and exclude the custom weekend Sunday only, please use the below formula:

=NETWORKDAYS.INTL(A4,B4,11)*8

Then, drag the fill handle down to the cells you want to apply this formula, and then, you should change the calculated date results to general format, and you will get the following result as you need:


Calculate work hours between two dates exclude custom weekends and holidays

You can also use the below formula syntax to get the work hours between two dates exclude both the custom weekends and holidays:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) * working_hours
  • start_date, end_date: The first and last dates to calculate the workdays between.
  • weekend: The specific days of the week that you want to set as weekends instead of the default weekends. It can be a weekend number or string.
  • holidays: A range of date cells that you want to exclude from the two dates.
  • working_hours: The number of work hours in each workday. (Normally, the work hour is 8 for each workday, you can change it to your need.)

For instance, we can use the below formula to get the work hours exclude custom weekend (Sunday and Monday) and holidays:

=NETWORKDAYS.INTL(A4,B4,2,$E$4:$E$6)*8

Then, you should convert the calculated date results to general format, and you will get the following result as you need:


Relative functions used:

  • NETWORKDAYS:
  • This function is used to calculate the number of working days between two given dates.
  • NETWORKDAYS.INTL:
  • It returns the number of working days by excluding default weekend days (Saturday and Sunday) or any days that are specified as weekends.

Relative work hours / work day articles:

  • Calculate Working Hours Per Month In Excel:
  • Normally, most of us may work 8 hours a day, 5 days a week. Here, I want to get the total working hours per month for calculating the salary. How to calculate the total working hours per month in Excel?
  • Find First / Last Day Or Working Day Of A Month In Excel:
  • Do you know how to find the first or last day and the first or last working day of a given month date in a worksheet? For example, I have a date 2014/5/21, and now I want to know the first and last day of this month, that is 2014/5/1 and 2014/5/31, to get the first business day is 2014/5/1 and the last business day is 2014/5/30.

Excel Productivity Tools

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 70% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

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

  • Office Tab 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.
  • 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.

Be the first to comment.