The expected progress percentage does not match the completed percentage.

Options
edited 02/09/24

Hello,

I'm trying to set up my spreadsheet in a way that I can have a column that calculates the expected percentage of an activity for the current day based on the start date and end date.

The formula I am using for this column is:

Expected percentage: =IF(TODAY() > [End Date]@row; 1; IF(TODAY() >= [Start Date]@row; (WORKDAY(TODAY(); 0) - WORKDAY([Start Date]@row; 0)) / (WORKDAY([End Date]@row; 0) - WORKDAY([Start Date]@row; 0)); 0))

The logic is, if the start date < today = expected % is 0 and if the end date > today = expected % is 100%

The problem I have is that as you can see in the image attached, the expected % of main activities like row 1 and 7 does not match the % completed that SS automatically sums up.

How could I set up the formula in the main columns so that both values match in case the completion percentage is equal to the expected in the sub-activities that make up the main activity?

• ✭✭✭✭✭
Options

The problem statement is briefed in detail and to get the right solution without accessing the spreadsheet is a bit complex, however kindly go over the below steps and give a try

We have to solve it step by step, i believe

Please ensure the percentage expected in the main activity matches the completed percentage in the sub activities

Also ensure the formula to consider the completion status of the sub activities

Also check out todays date is greater than the enddate is percentage is 100%

Huma