Calculation Issues

How to Create an Excel Progress Bar Formula That Excludes Weekends

Algirdas JasaitisAlgirdas Jasaitis Nov 11, 2026 869 views

Question details

The user wants to calculate the completion progress between a start date and the current date, counting only working days (excluding weekends), and capping the progress at 100%.

How to Create an Excel Progress Bar Formula That Excludes Weekends
Product
Excel
Device & OS
not provided
Scenario
Tracking project completion progress based on working days rather than standard calendar days.
Observed behavior
The user needs a formula to accurately calculate the progress percentage limited to 100%, taking only business days into account.
Before you start

Ensure you have the exact start date and target end date for your project entered and formatted as Date values in your spreadsheet.

Solution 1Recommended

Use the NETWORKDAYS Formula for Standard Weekends

This is the most direct method to calculate workdays between dates if your weekends fall on Saturday and Sunday.

The NETWORKDAYS function calculates the number of working days between two dates. By dividing the elapsed working days by the total working days, you get the current progress percentage. Wrapping the calculation in the MIN function ensures the progress never exceeds 100% after the end date has passed.

1
Select the target cell

Click on the cell where you want the progress percentage to appear.

2
Enter the formula

Type =MIN(NETWORKDAYS(start_date,TODAY())/NETWORKDAYS(start_date,end_date),100%). Replace 'start_date' and 'end_date' with your actual cell references, such as A2 and B2.

3
Format as percentage

Press Enter, then navigate to the Home tab and click the '%' icon in the Number group to format the decimal result as a percentage.

Use the NETWORKDAYS Formula for Standard Weekends
Exclude Holidays: You can add a holiday range as a third argument, like NETWORKDAYS(start_date, end_date, holidays_range), to exclude specific public holidays from the workday count.

Calculate Project Progress with WPS Spreadsheet

WPS Spreadsheet fully supports advanced date and time functions, including NETWORKDAYS, allowing you to seamlessly track project progress just as you would in Microsoft Excel.

  1. 1. Open your project tracker: Launch WPS Spreadsheet and open the document containing your project start and end dates.
  2. 2. Input the formula: In your progress column, type =MIN(NETWORKDAYS(start_date,TODAY())/NETWORKDAYS(start_date,end_date), 100%).
  3. 3. Add visual progress bars: Highlight the progress cells, go to 'Conditional Formatting' > 'Data Bars' under the Home tab to add a dynamic visual progress bar directly inside the cells.
Fully compatible with Microsoft Excel date formulasBuilt-in conditional formatting for visual progress barsFree and lightweight spreadsheet alternativeSupports advanced functions like NETWORKDAYS and TODAY
microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning a decimal number instead of a percentage?

By default, spreadsheet software formats the result of division as a decimal (e.g., 0.5 instead of 50%). To fix this, select the cell containing the formula, navigate to the Home tab, and click the Percentage style icon to format it correctly.

How do I add a visual bar inside the cell alongside the percentage?

Once your formula calculates the percentage, you can use Conditional Formatting to visualize it. Go to Home > Conditional Formatting > Data Bars and select your preferred color. This will create a progress bar directly inside the cell based on the percentage value.

What happens if the project hasn't started yet?

If the start date is in the future relative to the TODAY() function, the formula will return a negative percentage. You can prevent this by flooring the result at 0% using the MAX function: =MAX(0, MIN(NETWORKDAYS(start_date,TODAY())/NETWORKDAYS(start_date,end_date),1)).