Go Back

Get work hours between dates

Read time: 34 minutes

In this tutorial, you will learn how to get work hours between dates. This is possible by using Excel NETWORKDAYS function. Also, you will get familiar with two different ways to get work hours between dates. The first one excludes only weekends, while the second one excludes defined holidays’ dates beside weekends. For working days, we will consider Monday-Friday, while one working day has 8 hours.

Get work hours between dates

Formula

=NETWORKDAYS(start_date, end_date, [holidays]) * 8

Explanation

Let’s see how the NETWORKDAY function works. This function returns a number of working days between two selected days. The function can take in count specified holidays, but it is not mandatory. We will use NETWORKDAY function to get work hours between dates.

The parameters of the function are: start_date – a beginning date of a period for which we want to get workdays between dates; end_date – an ending date of a period for which we want to get workdays between dates; holidays is a non-obligatory parameter which enables the function to take in counts holiday dates and skip them. To enable this, we also need to create a table with all holiday’s dates. Also, we need to define a name for range containing holidays’ dates.

After we find a number of working days between selected dates, we multiply this number with 8, which is the number of working hours in one working day.

Example 1

In the first example, we will see how to get work hours between dates start date and end date, without taking holidays into account. Let’s see how the formula looks like:

=NETWORKDAYS(B3,C3) * 8

 

 

Figure 1. Get work hours between dates excluding weekends

 

As you can see in the picture, we want to get work hours between dates in cells B3 and C3 and put it in the cell D3. Therefore, our start date will be 24-Feb-18 and end date will be 28-Feb-18. As 24-Feb-18 and 25-Feb-18 are Saturday and Sunday, the formula will count 26-Feb-18, 27-Feb-18 and 28-Feb-18 and return 3 days in the cell D3. Finally, we will multiply the result of the formula with 8 hours and get 24 work hours between 24-Feb-18 and 28-Feb-18.

Example 2

Now we will see how to include holidays when counting work hours between dates. We created a new table which has the list of holidays and their dates and named the range F3:F6 “public_holidays”.

Here is the formula used for this example to get work hours between dates:

=NETWORKDAYS(B3,C3,public_holidays) * 8

 

 

Figure 2. Get work hours between dates excluding weekends and holidays

The only difference between this and the first example is filling the parameter Holidays with named range “public_holidays”. Therefore, the number of working days between 14-Feb-18 (B3) and 16-Feb-18 (C3) will be 2 days. 14-Feb-18 was not taken into account, although it is Wednesday, because we put it in our table with holidays. As a result, we multiple 2 days with 8 work hours and get 16 work hours between 14-Feb-18 and 16-Feb-18.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to calculate work hours between two date-times. The work start and end time are both configurable. The calculation must also exclude weekends and a list of configurable dates.
Solved by F. D. in 28 mins
On a spreadsheet i have job roles and there average hours per shift. Different roles work different lengths of shift and these lengths are graded, IE less than 5 hours scores 0. Between 5-10 scores 1. Anything over 10 scores 0. Then another role hours logged between 5-11 will score 1. I've tried several IF functions that work for one role but can not get the formula to work for either role in the same cell
Solved by O. J. in 15 mins
i need a formula for number of meals depending on work hours, if work hrs are less than 4, than meals is 1, if hours are between 4 and 8, there are 4 meals, and if hours are more than 8, meals are 3. also if hours are 0, meals are 0
Solved by F. F. in 18 mins

Leave a Comment

avatar