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
=NETWORKDAYS(start_date, end_date, [holidays]) * 8
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.
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.
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.