track progress VS. planned duration

Options

hello all,

my director asked me to find a way that tracks the progress VS. the planned duration....for example, I have automated a workflow to send an alert when the due date is within days and % of completion is less than 100%.....but he wanted if we have an activity duration 100days so at day25 to have 25% progress and at day 50 we must have 50% progress and so on... if not send an alert and be in a regular Mannar

is there a way to do that??....an equation or formula as I have a 1200 activity .

thanks in advance :)

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Rana HUSSEIN 

    Hope you are fine, try the following:

    1- create column to calculate the planned progress use the following formula :

    =IF((TODAY() - Start@row) / (Finish@row - Start@row) > 1, 1, (TODAY() - Start@row) / (Finish@row - Start@row))

    2- you have already a column for actual progress

    3- creat a column ( check box ) name it "Delay Check" and use the following formula to check if the activity is delayed or not and convert it to column formula

    =IF(Planned@row > Progress@row, 1, 0)

    the sheet will lock like the following:

    4- create a weekly alert workflow send to your director a table contain all delayed activities as following


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @Rana HUSSEIN

    you are more than welcome, first Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    to avoid negative value please change the formula as following

    =IF(Start@row > TODAY(), 0, (IF((TODAY() - Start@row) / (Finish@row - Start@row) > 1, 1, (TODAY() - Start@row) / (Finish@row - Start@row))))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Rana HUSSEIN 

    Hope you are fine, try the following:

    1- create column to calculate the planned progress use the following formula :

    =IF((TODAY() - Start@row) / (Finish@row - Start@row) > 1, 1, (TODAY() - Start@row) / (Finish@row - Start@row))

    2- you have already a column for actual progress

    3- creat a column ( check box ) name it "Delay Check" and use the following formula to check if the activity is delayed or not and convert it to column formula

    =IF(Planned@row > Progress@row, 1, 0)

    the sheet will lock like the following:

    4- create a weekly alert workflow send to your director a table contain all delayed activities as following


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Rana HUSSEIN
    Options

    @Bassam.M Khalil thanks a lot you saved me :), that's exactly what I need.

    I have another question if you don't mind, as the image the activities that still in the future the percentage result in a negative value, could we avoid this??


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @Rana HUSSEIN

    you are more than welcome, first Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    to avoid negative value please change the formula as following

    =IF(Start@row > TODAY(), 0, (IF((TODAY() - Start@row) / (Finish@row - Start@row) > 1, 1, (TODAY() - Start@row) / (Finish@row - Start@row))))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Rana HUSSEIN
    Options

    really appreciated your support :)

  • Rana HUSSEIN
    Options

    I know this is too much but I have another question

    I need to calculate the actual percentage for hiring for different departments and for each department I have about 5 parents with sub children with a different number of employee …I insert the equation shown in the attached image below for each parent, then make Avg for the 5 parents hence I will get the % for each department ….is that right ??


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Rana HUSSEIN

    don't forget to vote for me :)

    if you can publish the sheet and send the link to me i will check what you do for calculate the actual percentage for hiring for different departments

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Rana HUSSEIN

    Hope you are fine, yes your formula is correct for calculate the actual percentage for hiring for different departments

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"