Formula


May 8th, 2013 by Alan Crisp Alan Crisp

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 Responses to “Formula”

  1. admin admin Says:

    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.

  2. admin admin Says:

    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.

  3. admin admin Says:

    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.

  4. Alan Crisp Says:

    Fantastic thats what i was loking for thanks very much.

    Cheers

  5. Swapan Kumar Roy Says:

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

  6. Alan Crisp Says:

    Hi Guys Need your Help

    I am working on a spreadshhet to monitor both time and cost, the attached is a file where i am traking work progress, as you will note colum E & F have planned start and finish dates, colum H & L have actual start and finish dates, you will also note the bar chart shows three colors, Green is planned, Blue is actual start with colum G showing progress completed, Red shows the delay factor based on colums E & H having diffrent start dates,as you will note colums I,J & K allow for the same activity to to have diffrent start dates with a Percentage completed on each visit, how can i record the same within the bar chart also showing the delay factor, i have managed to build the spreadsheet but have got stuck trying to work out the required formula.
    Cheers

Leave a Reply

You must be logged in to post a comment.

s2Member®