Function Problems

How to Calculate Working Hours Between Dates Excluding Fridays and Saturdays

Partner Editor Sep 28, 2026 868 views

Question details

Calculate the net working hours between a start date-time and an end date-time, specifically excluding Fridays and Saturdays, and restricting the calculation to an 8:00 AM to 4:00 PM workday.

How to Calculate Working Hours Between Dates Excluding Fridays and Saturdays

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking project durations, employee timesheets, or SLA ticket resolution times in regions or companies where the standard weekend falls on Friday and Saturday.
Observed behavior: Extracting the exact valid working time without manually counting days, avoiding `#NAME?` errors caused by unsupported functions in older versions, and returning a precise decimal hour value.

Before you start

Ensure that the start and end values in your cells (e.g., B3 and AQ3) are formatted as valid Date and Time values, rather than plain text, so the formulas can process them accurately.

Solution 1Recommended

Use the NETWORKDAYS.INTL Function (Recommended for Modern Versions)

This solution uses the built-in NETWORKDAYS.INTL function combined with time extraction to handle custom weekends (Friday/Saturday) and specific shift hours.

The NETWORKDAYS.INTL function allows you to specify exactly which days of the week are considered weekends by using a specific parameter code. For Friday and Saturday, the code is 7.

By combining this with standard mathematical time boundaries (8/24 for 8:00 AM and 16/24 for 4:00 PM), you can accurately calculate the net working hours.

Select the target cell

Click on cell AR3 (or your desired output cell) where you want the calculated hours to appear.

Enter the NETWORKDAYS.INTL formula

Input the following formula: `=NETWORKDAYS.INTL(B3,AQ3,7)*8+MIN(MAX(AQ3,WORKDAY(B3,1,8/24)),WORKDAY(AQ3,-1,16/24))-B3-(MOD(B3,1)<8/24)`. (Note: Make sure to adjust the references if your start date is not in B3 and end date is not in AQ3).

Format the output as a Number

Since this formula structure returns the total hours as a standard decimal (e.g., 17.260), right-click the cell, select 'Format Cells', choose 'Number', and set your desired decimal places.

Use the NETWORKDAYS.INTL Function (Recommended for Modern Versions)

Customizing Shift Hours: If your workday runs from 9:00 AM to 5:00 PM instead, change the `8/24` to `9/24` and the `16/24` to `17/24` in the formula.

Solution 2

Use the WEEKDAY and MOD Functions (For Legacy Compatibility)

Use this alternative formula if the NETWORKDAYS.INTL function returns a `#NAME?` error, which typically occurs in older spreadsheet versions.

Efficient Time Tracking

Calculate Complex Timesheets Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced date and time functions, including NETWORKDAYS.INTL. You can seamlessly calculate complex working hours, manage timesheets, and track SLA metrics without worrying about compatibility issues.

1. Open your timesheet: Launch WPS Spreadsheet and open the document containing your start and end date-time logs.
2. Apply the time formula: Select the blank cell for your total hours and paste the `NETWORKDAYS.INTL` formula tailored to your custom weekends.
3. Drag to fill: Hover over the bottom-right corner of the cell until the fill handle appears, then drag it down to calculate hours for all rows instantly.

Fully compatible with Microsoft Excel formulas and date-time logicNative support for advanced functions like NETWORKDAYS.INTLFree, lightweight, and incredibly fast spreadsheet processingFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I exclude public holidays from this calculation?

To exclude holidays, you can add a range of holiday dates as the fourth argument in the NETWORKDAYS.INTL function. For example: `=NETWORKDAYS.INTL(Start, End, 7, HolidaysRange)`. The function will automatically deduct those specific dates from your total.

Why does my formula return a #NAME? error?

A `#NAME?` error usually means the software version you are using does not recognize the function name. If you are using an older version that lacks `NETWORKDAYS.INTL`, use the alternative `WEEKDAY` and `MOD` formula provided in the secondary solution.

How do I change the working days to exclude Sunday and Monday instead?

The third argument in the `NETWORKDAYS.INTL` function dictates the weekend. To exclude Sunday and Monday, change the weekend parameter from `7` to `2`.

Why is my result displaying as a weird date instead of hours?

This happens when the output cell is formatted as 'Date' instead of 'Number'. Right-click the cell, select 'Format Cells', and change the category to 'Number' or 'General' to see the hours correctly.

Excel & Data Problems