Others

How to Calculate Available Working Hours Excluding Weekends in Teams Lists

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to formulate a calculated column in a Microsoft Teams List to determine available working hours between a start date and an end date, ensuring weekends are excluded and the result is multiplied by an allocation percentage.

Product: Microsoft Teams Lists
Device & OS: not provided
Scenario: Tracking project hours and team member resource allocation natively within a Microsoft Teams or SharePoint List.
Observed behavior: The user needs a customized calculation method because Teams Lists do not offer a straightforward built-in function to exclude weekends or factor in percentages for date ranges automatically.

Before you start

Verify that your Start Date and End Date columns are configured using the 'Date and Time' data type in your List settings, and determine the standard daily working hours (e.g., 8 hours) used by your organization.

Solution 1Recommended

Use a Mathematical Calculated Column Formula

Since Teams Lists do not support the standard NETWORKDAYS function found in spreadsheet software, you must use a mathematical formula to extract weekends from the total day count.

Microsoft Teams Lists and SharePoint Lists utilize a legacy formula engine that does not include modern date-exclusion functions like NETWORKDAYS. To achieve your goal, you have to create a calculated column that calculates the total days, subtracts the weekend days based on the start and end weekdays, multiplies by your daily working hours, and finally multiplies by your allocation percentage.

Create a new calculated column

Navigate to your Teams List, click on 'Add column', and select 'Calculated (calculation based on other columns)' from the menu.

Input the mathematical formula

In the formula box, construct your calculation. A foundational formula for business days is: =((DATEDIF(StartDate,EndDate,"d"))-INT(DATEDIF(StartDate,EndDate,"d")/7)*2-IF(WEEKDAY(EndDate)<WEEKDAY(StartDate),2,0)). Multiply this entire block by your daily hours (e.g., *8) and then by your Allocation column (e.g., *[Allocation]).

Format the output

Under 'The data type returned from this formula is', select 'Number'. Set the number of decimal places to your preference (usually 1 or 2 for tracking partial hours).

Save and verify

Click 'Save' to apply the new column to your List. Create a test entry with a known date range bridging a weekend to verify the calculation yields the correct net hours.

Allocation Percentage Formatting: Ensure your Allocation column is set as a Percentage or Decimal type. In the calculation, 100% acts as a multiplier of 1, while 50% acts as a multiplier of 0.5.

Effortless Resource Tracking

Calculate Working Hours Easily with WPS Spreadsheet

Building complex mathematical formulas in Teams Lists just to exclude weekends can be frustrating and prone to errors. WPS Spreadsheet simplifies resource and project management with built-in date functions, allowing you to track available hours accurately in seconds.

1. Set up your project data: Open WPS Spreadsheet and organize your columns: Start Date in column A, End Date in column B, and Allocation Percentage in column C.
2. Apply the NETWORKDAYS function: Select the cell where you want the total available hours displayed and type =NETWORKDAYS(A2, B2) to instantly get the total business days between the two dates.
3. Calculate allocated working hours: Expand the formula to multiply the business days by your standard daily hours (e.g., 8) and the allocation percentage: =(NETWORKDAYS(A2, B2) * 8) * C2.
4. Drag to apply: Press Enter to calculate the hours for the first row. Click and drag the fill handle at the bottom right of the cell down your list to apply the calculation to all team members.

Use the built-in NETWORKDAYS formula to instantly exclude weekends and holidaysFull format compatibility with Microsoft Excel (.xlsx) for easy data sharingLightweight, fast, and features a familiar interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the NETWORKDAYS function work in Teams Lists?

Microsoft Teams and SharePoint Lists use an older formula structure for their calculated columns that does not support the modern Excel NETWORKDAYS function. Users must rely on alternative mathematical equations involving DATEDIF and WEEKDAY functions to exclude weekends manually.

Can I exclude custom public holidays in a Teams List calculated column?

Excluding specific holidays natively using standard List formulas is highly complex and generally impractical because Lists lack a holiday array function. For holiday exclusions, it is recommended to manage the data in a spreadsheet application or use a Microsoft Power Automate flow to calculate and update the list item.

How do I handle time values if my dates include hours and minutes?

If your Start and End dates include specific times, you will need to extract the time differences using the HOUR and MINUTE functions in your calculated column, then appropriately add or subtract those decimal values from your daily hour totals.

Why is my formula returning an error in the List view?

Errors in calculated columns usually occur due to syntax typos, missing parentheses, or referencing a column name that does not exist. Ensure that your column names are wrapped in brackets (e.g., [Start Date]) if they contain spaces, and that you have closed all opened parentheses in your mathematical formula.

Office Quick Solutions