When it comes to giving the daily payment for any job or work, we need to calculate the working hours. There are many companies that pay their workers on a working-hour basis. In this article, we are going to show you how to calculate hours between two dates and times excluding weekends in **Excel**. To calculate hours between two dates and times excluding weekends in **Excel**, we have to use several functions including the **NETWORKDAYS**, **IF**, **MEDIAN**, and **MOD **functions. Initially, it may seem difficult to you but gradually you will understand the whole procedure as well.

Let, **James **was an employee of a renowned industry. He joined the industry in **2013 **and left the job in **2017**. We will calculate his working hours between these two dates and times excluding weekends in **Excel**. So. letโs get started.

**Contents**hide

## ๐ย Download Excel File

Download free Excel workbook from here:

## Learn to Calculate Hours Between Two Dates and Times Excluding Weekends in Excel

While writing this article, โCalculate Hours Between Two Dates and Times Excluding Weekends in **Excel**โ, weโve considered a dataset that contains approximately 5 columns and 16 rows. In this dataset, we added **Name**, **Entering Date**, **Leaving Date**, and **Working Hours (Excluding Weekends)** to make you better understand. With the help of these data, we perform our required operation. But if you want, you can change the entities and put your own values.

To calculate hours between two dates and times excluding weekends in **Excel**, we will utilize the **Excel **functions here. The **NETWORKDAYS**, **IF**, **MEDIAN**, and **MOD **functions will give the output, and then we will change the output to a custom format. You need to be very careful while performing this operation because the formula contains several functions. You can follow the given instructions to achieve the approach.

Basically, there are two steps to calculate hours between two dates and times excluding weekends. We are going to discuss every step elaborately.

**STEP**

### Step 1: Create Formula to Calculate Hours

In this step, you need to do the following things.

- Initially, you need to go to the
**Excelย**worksheet. - Secondly, select cell
**E6**. - Thirdly, write the following formula on that cell

**=(NETWORKDAYS(C6,D6)-1)*(โ17:30โ-โ8:20โณ)+IF(NETWORKDAYS(D6,D6),MEDIAN(MOD(D6,1),โ17:30โณ,โ8:20โณ),โ17:30โณ)-MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),โ17:30โณ,โ8:20โ)**

**๐จ Formula Breakdown**

Here:

๐ย **MOD(C6,1) **gives a remainder for **C6** following a division by **1**.

**Result**: 0

๐ย **NETWORKDAYS(C6,C6) **refers The quantity of complete working days among **C6** and **C6**.

**Result**: 0

๐ย **MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),โ17:30โณ,โ8:20โณ) **refers that The average or the value in the midpoint of the range of values returned by the **MEDIAN** function is **NETWORKDAYS(C6,C6)*MOD(C6,1),โ17:30,โ,โ8:20โณ**.

**Result**: 17:30

๐ย **MEDIAN(MOD(D6,1),โ17:30โณ,โ8:20โณ) **gives

**Result**: 17:30

๐ย **NETWORKDAYS(D6,D6) **provides

**Result**: 0

๐ย **NETWORKDAYS(C6,D6)-1 **gives

**Result**: 07/02/1903 0:00

๐ย **(NETWORKDAYS(C6,D6)-1)*(โ17:30โ-โ8:20โ) **provides

**Result**: 08/03/1901 3:00

๐ย **IF(NETWORKDAYS(D6,D6),MEDIAN(MOD(D6,1),โ17:30โณ,โ8:20โณ),โ17:30โณ) **refers that the **IF** function determines whether a criteria is satisfied and outputs two values: one if **TRUE** as well as another if **FALSE**, **IF(0,โ17:30โณ,โ17:30โณ)** follows.

**Result**: โ17:30โ (In an **Excel** analytical test, 0 indicates **FALSE** and 1 indicates **TRUE**.)

**๐ย (NETWORKDAYS(C6,D6)-1)*(โ17:30โ-โ8:20โ) + IF(NETWORKDAYS(D6,D6), MEDIAN(MOD(D6,1), โ17:30โณ,โ8:20โณ),โ17:30โณ)-MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),โ17:30โณ,โ8:20โ) **provides

**Result**: 403.5069444

- Fourthly, press the
**Enterย**key. - This format is
**General**So, we need to use the**Custom**format to get the appropriate output as well.

**๐ Read More: ****How to Calculate Turnaround Time in Excel Excluding Weekends**

**STEP**

### Step 2: Apply Custom Format to Get Hour, Minute and Second Value

For the desired output, you need to maintain this step properly.

- Select cell
**E6**. - Secondly, go to the
**Homeย**tab. - Thirdly, choose the
**Number**feature and belatedly, click on the**Number Format**.

- Now you will get a dialogue box name
**Format Cells**. - Thirdly, click on the
**Custom**option and choose the**Type**:

**[h]:mm:ss**

- Afterward, tap on the
**OKย**button.

- Finally, you will find the desired output that you wanted to have.

Undoubtedly, this approach may look slightly difficult but we hope you can do this easily if you follow the above instructions properly.

**๐ Read More: ****9 Tricks to Calculate Time Difference Between AM and PM in Excel**

## ๐ Important Notes

You should be aware of the following things while performing the processes mentioned above:

๐๏ธย Initially, you need to be careful while using the **Excel **functions.

๐๏ธย You should be conscious when you intend to use the **Custom **format as well.

## ๐ Takeaways from This Article

If we summarize the whole article, we have got some points.

๐ย Firstly, we used the **NETWORKDAYS**, **IF**, **MEDIAN**, and **MOD **functions.

๐ย Secondly, we applied the **Custom **format to calculate hours between two dates and times excluding weekends.

## Conclusion

We wish that you would be able to calculate hours between two dates and times excluding weekends in **Excel** using this article. You can easily follow the given approach without facing any problems we hope. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about **Excel** tactics, we recommend you to visit our website **www.ExcelDen.com**.

## Related Articles

**4 ways to Create Excel Formula for Overtime Over 8 Hours****7 Examples to Calculate Duration of Time in Excel****How to Calculate Overtime and Double Time with Formula in Excel****5 Easy Ways to Fix When SUM Time Is Not Working in Excel****4 Easy Methods to Calculate Production per Hour in Excel****5 Ways to Calculate Total Hours Worked in a Week in Excel****4 Examples of Timesheet Formula with Lunch Break in Excel**