+ Reply to Thread
Results 1 to 4 of 4

Calculating Percentage Work Completed Based on Timestamp

  1. #1
    Registered User
    Join Date
    04-12-2020
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    2

    Smile Calculating Percentage Work Completed Based on Timestamp

    I want to calculate the daily % of work done on my projects based on the Back Log reasons and the previous timestamp. Currently, the percentages are calculated by multiplying the number of request with the % of work that should be done at the given Backlog reason (outlined in the reference tab). Ideally the percentages of each project should add up to 100% once the Backlog reason "Close Complete" is used.

    For example: Project #283, 45% was worked on 3/30 and 80% on the 3/31. The second day's percentage of work done should be 80% - 40% (from the previous time stamp) and the final day on the project should be 20% as listed as, "Closed Complete" is the Back Log reason that indicates that the project is done.

    Is there a way to have a formula for the percentage, to subtract the percentage from the previous backlog reason and timestamp?

    Hope you can help!

    Thank you!
    Attached Files Attached Files
    Last edited by mpine16; 04-12-2020 at 06:42 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Calculating Percentage Work Completed Based on Timestamp

    Can you explain your overall scheme for this type of tracking? It seems like sometimes the % is supposed to be total progress to date, and sometimes it's just the progress for a particular step. But to give you a solution we have to understand which is which.

    For example, you want to show the % for each of these steps individually on sheet Data

    45% PW Waiting for Pre- Approval (Client or Manager)
    20% Closed Complete

    but this one

    80% Pending Approval

    you need to subtract the prior progress. Why? Why is Pending Approval treated differently? Are there other statuses in sheet Reference that are treated the same way? What do the percentages on Reference actually mean?

    It is probably not a hard problem to solve but I think the reason there are no responses is that the problem is not clear.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-12-2020
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    2

    Re: Calculating Percentage Work Completed Based on Timestamp

    Hello - my apologies!

    The Reference tab refers to the % of the work that should have been completed. Essentially, if there are multiple entries for the same project number the entire % should equal 100%. The problem lies when the individual uses multple statuses.

    Is there a way for me to flag in a new column, when a project has more than 1 entry and if any of those entries is "Closed Complete"?
    Last edited by mpine16; 04-13-2020 at 11:55 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Calculating Percentage Work Completed Based on Timestamp

    If I understand then the following formula for column A on the Data sheet is what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If that isn't what you want, please tell us the expected results that should be in column A on the Data sheet.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Calculating percentage completed
    By Ginette Lacombe in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-26-2016, 03:02 PM
  2. [SOLVED] Percentage of Completed Tasks Based Unique Column A Values
    By blindqui in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-02-2016, 06:54 PM
  3. [SOLVED] calculating a percentage based on a yes/no column
    By hceoc_energy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2014, 08:33 PM
  4. Calculating percentage of completed tasks in various rows
    By excelnoob808 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2013, 05:37 AM
  5. Calculating percentage based on new data
    By Ken_NJ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2013, 02:38 PM
  6. Formula to calculate the percentage of work completed
    By Zodiark in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2012, 04:42 PM
  7. Calculating the Percentage Completed
    By KyrithAZ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2006, 03:40 AM

Tags for this Thread

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