I have a project plan with different tasks, each with a reference number i.e. main step, key activity and task. I have planned start date and planned end date for each in two separate columns. I want to figure out how far behind or on track I am with my project dates using by creating a formula/if statement. Can anyone help please?


  • Where is your task progress information? Knowing the planned start and end dates is not enough to give you a meaningful estimate of how far ahead or behind you are. – Excellll May 5 '15 at 20:05

This is only an example that you can adapt to your needs. The job involves placing 600 large blocks. We initially assume that we can place 3 blocks per day. The job started April 1st. In A2 we enter:


In B2 we enter the formula:

=A2+600/3 our initial estimate of the end date.In C2 we enter today's date and in D2 we enter the total progress to date. (Both C1 and D1 can be updated daily.)
In D3 we enter the formula:

=D2/600 This is our percent complete.

In E2 we enter a formula to re-evaluate the productivity based on actual data:


Finally in F2 we can enter our latest, best, estimate of the completion date:

=TODAY()+(600-D2)/E2 Here is a sample:

enter image description here

In this sample, we initially thought it would take 200 days to place the blocks. We now see that the actual productivity is only around 2.2 compared to 3.0 This allows us to re-estimate the completion date based upon the actual productivity and the number of blocks that still must be placed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.