How to Create an Excel Progress Bar Formula That Excludes Weekends
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%.

- 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.
Ensure you have the exact start date and target end date for your project entered and formatted as Date values in your spreadsheet.
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.
Click on the cell where you want the progress percentage to appear.
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.
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 NETWORKDAYS.INTL for Custom Weekend Schedules
Ideal if your working week is different from the standard Monday-to-Friday schedule.
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. Open your project tracker: Launch WPS Spreadsheet and open the document containing your project start and end dates.
- 2. Input the formula: In your progress column, type =MIN(NETWORKDAYS(start_date,TODAY())/NETWORKDAYS(start_date,end_date), 100%).
- 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.

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)).




