track progress VS. planned duration
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

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
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"

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

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

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

really appreciated your support :)

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 ??

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
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"

Hi @Rana HUSSEIN
Hope you are fine, yes your formula is correct for calculate the actual percentage for hiring for different departments
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"