I am working on a spreadsheet with regards to construction,

i have a start date say 1 july with a duration of 10 days to carry out the works, therefore we need to complete 10% per day, i have a summary progress chart that will record the % completed, however i need to calculate the planned %, so my problem is how do i link the start date to the summary and get the same to calculated the same on a daily bases, ie todays planned is 10% and every day thereafter the planned % needs to increas by the daily planed %

Regards

working file on formula

### 6 replies to "Formula"

• admin

Could you explain it with a sample file? You can send it to enquiry@everydayexcel.com and I will upload it for you to the portal.

• admin

Alan Wrote:

Thanks for getting back to me, I have attached a sample of the file, as you will note sheet 1 gives the task, planned start and finish dates with the duration for completing the same, sheet 2 is the progress summary, which needs to calculate the planned works based on the specific start and finish dates and the duration in percentages i.e. 10 days duration /100 = 10% per day, the calculation needs to commence on the start date and show 100% after 10 days, sheet 3 shows the actual % completed which I have managed to process through to the summary sheet with no issue, it’s the planned that’s got me stumped.

• admin

You should create a column in Sheet1 to measure the number of days completed as at today.

1)Since the start date can be later than today, you need a IF formula to determine whether the number of days completed should be computed.

2)If the IF formula determines that the start date is earlier than today, then calculate the planned duration as of today. Another scenario that appears is that the planned finished date is also in the past. In this case, when we calculate duration, we will have to make sure that it cannot be greater than the planned duration. Hence the use of the MIN formula to determine the number of days have passed or the total duration whichever is shorter. The formula should be MIN(TODAY()-C5,E5).

3) Combining the 2 formulas in point 1 and 2, you will get

=IF(C5>TODAY(),0,MIN(TODAY()-C5,E5))
You can put this formula into cell F5 of Sheet1 and copy the formula down the column.

4) In your worksheet 2, you can compute the planned completed percentage by dividing Sheet1!F5 with Sheet1!E5. The percentage will increase by the day because of the TODAY formula used.

• Alan Crisp

Fantastic thats what i was loking for thanks very much.

Cheers

• Swapan Kumar Roy

It’s a great opportunity to learn and save our valuable time.

• Alan Crisp