The expected progress percentage does not match the completed percentage.

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?


Answers

  • Humashankar
    Humashankar ✭✭✭

    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%

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!