Percentage Past since start date & duration

Cathy Fraser

Hi, I am looking for a formula to look at a percentage past based on the start date and the duration time. We are looking to see what the completed percentage should be and flag a WBS that may need to wither be updated or is falling behind.

I have worked out the formula for the # of days past since the start date, but that does not give me what I am after and when I change it into a % it goes to 300% which is not right.

=NETWORKDAYS([Start Date]@row, TODAY())

Any help would be great.

Thanks

Cathy

Leibel S

Try the below.

=NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row)

You may want to consider adding a check for if it is past the end date...

Leibel S

Try the below.

=NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row)

You may want to consider adding a check for if it is past the end date...

Cathy Fraser

Thanks very much Leibel, that seems to have worked.

Cathy Fraser

Sorry Leibel, I am just looking at it and yes the formula works, but is there away that if the % Complete is = to 100% that it stops the formula? I.e. % complete is %100 at the moment it keeps counting and it is now showing 700% because it is 7 days since the completed date. Hope that makes sense.

Leibel S

You can fix that by placing it into an If statement. See below:

=IF([End Date]@row > TODAY(), NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row),1)