track progress VS. planned duration

Rana HUSSEIN

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

Bassam Khalil

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 Khalil

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

Bassam Khalil

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

Rana HUSSEIN

@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

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

Rana HUSSEIN

really appreciated your support :)

Rana HUSSEIN

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

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 Khalil

Hi @Rana HUSSEIN

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