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
• Calculate work hours between two dates exclude both weekends and holidays
• Calculate work hours between two dates exclude custom weekends
• Calculate work hours between two dates exclude custom weekends and holidays

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:

• 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:

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:

• 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:

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:

• 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:

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

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:

• 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:

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

Relative functions used:

Relative work hours / work day articles:

---

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
	Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
	Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
	Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

 Read MoreFree Download

---

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.

 Read More Free Download