+ Reply to Thread
Results 1 to 18 of 18

formula in excel to calcualte percentage of completion for a task

  1. #1
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Question formula in excel to calcualte percentage of completion for a task

    what is the formula in excel to calcualte percentage of completion for a task

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: formula in excel to calcualte percentage of completion for a task

    =A1/B1

    Cell Format as %
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Re: formula in excel to calcualte percentage of completion for a task

    thank you for your reply: can you help me more please: I have created an Task tracker sheet and it goes like this:
    A1 task description
    B1 start date
    C1 end date
    D1 duration
    E1 % completion
    how can I calculate the % of completion up to date for a task

    thanks

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Is D1 today's date less the start date? If so d1/(c1-b1) should do it.

  5. #5
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Re: formula in excel to calcualte percentage of completion for a task

    thanks for your help, but it is showing me Error.
    O.k. if this is not too much to ask, can anyone send me an excel sheet with the formula to see how it is done, I would be greatful

    thanks

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Better that you post your spreadsheet so we can see what you've done

  7. #7
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Post Re: formula in excel to calcualte percentage of completion for a task

    you're right, thanks. please find attached.

    regards,
    Attached Files Attached Files

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Tasks%20Tracker(2).xlsx

    Try this. It uses the start and finish date to determine the duration in days and the % complete is the number of days elapsed since the start date as a percentage of the total duration.

  9. #9
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Re: formula in excel to calcualte percentage of completion for a task

    thanks again. the percentage is shwoing more than 100%. I need the following like for example

    if:
    A2: prepare communication plan
    B2: start date: 01/08/2014
    C2: finish date: 10/08/2014
    D2: Duration: 9
    E2: percentage of completion till date: let's say today is the 4/08/2014

    what is the formula for calculating the percetange of completion for this task till date 04/08/2014?

    I really appreciete your help. thanks again

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Tasks%20Tracker(2).xlsx

    Try this. I'm assuming the % completion is the amount of time elapsed from teh start date divided by the entire duration.

    If the start date is after todays date the activity hasn't comemnced yet so the % complete is blank. If todays date is after the end date the activity is finsihed and it returns 100%. For todays date bewteen the two the completion % formula is time elapsed /duration

  11. #11
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Re: formula in excel to calcualte percentage of completion for a task

    thanks again very much

    would it be possible to make the activity that has not strated yet to show 0% instead of showing blank.


    best

  12. #12
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Real_madrid

    Absolutely. In the if formula change the "" to a zero. The two inverted commas together simply tell excel to show a blank. If you get stuck I'll do it but have a go. Easiest to change the top formula and copy down by dragging or even double clicking bottom right corner of highlighted cell.

  13. #13
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Re: formula in excel to calcualte percentage of completion for a task

    thanks. I did it.

    best

  14. #14
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Well done. You should mark this as solved now.

  15. #15
    Registered User
    Join Date
    07-04-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    10

    Re: formula in excel to calcualte percentage of completion for a task

    thanks again for helping me out last time.

    one think I noticed that I need too, is how can I show the actual % of completion
    for example: please find the attached.

    regards,
    Attached Files Attached Files

  16. #16
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: formula in excel to calcualte percentage of completion for a task

    Looks like you've got the completion as a decimal. Just format it as %

  17. #17
    Registered User
    Join Date
    12-02-2014
    Location
    Lahore, Pakistan
    MS-Off Ver
    2013
    Posts
    1

    Re: formula in excel to calcualte percentage of completion for a task

    Thanks i was searching for it and found it here, just joined forum and downloaded to understand it correctly.

  18. #18
    Registered User
    Join Date
    07-21-2015
    Location
    Dubai, UAE
    MS-Off Ver
    2013
    Posts
    1

    Re: formula in excel to calcualte percentage of completion for a task

    I also need to Calculate and display the Percentage of Completion for a task. But I needed to have as a result several options. Can it be done with Nested IF and how would it be done
    This is what I would like to get:
    If today is <1% then would like to show: NOT STARTED
    If today is <100% but >0% then would like to show: (the actual Percentage)
    If today is =100% then would like to show: COMPLETED
    If today is >100% then would like to show: DELAYED

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2014, 03:23 AM
  2. Percentage Completion Bar Formula not working
    By chriseverclear in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-25-2012, 12:29 PM
  3. Calculate Percentage of task completion with Checkboxes
    By DMA-Pacific in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-19-2011, 02:18 AM
  4. Replies: 6
    Last Post: 04-25-2010, 03:26 PM
  5. [SOLVED] how do i create a completion of task checklist
    By chris in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-27-2006, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1