+ Reply to Thread
Results 1 to 5 of 5

Percent progress with future dates

  1. #1
    Registered User
    Join Date
    03-28-2022
    Location
    Hawaii
    MS-Off Ver
    2016
    Posts
    3

    Percent progress with future dates

    Hello All,
    I'm building a project tracking tool and am trying to calculate the percent progress using dates. I essentially want to determine what percentage today is between the start date and end date, if that makes sense.

    Excel Question.PNG

    You can see in the image that the first column has the start date, the second has the end, and the final column is supposed to track progress, but all the rows that have a start date in the future have percentages well over 100%. Is there any way to set it to 0% and have it automatically begin tracking when it reaches the relevant date? Also, for some reason the formula (provided below) calculates percentage remaining rather than percentage elapsed, and i can't figure out how to reverse that.

    An example of the formula being used in column J11 (pictured) is =(H11-TODAY())/(H11-G11) which is giving me 240%. This formula works for elapsed start dates, but can't find one that fits for future ones.

    Apologies if this has been addressed elsewhere, but I couldn't find it if it was. Thank you in advance for any help!
    Attached Images Attached Images
    Last edited by Palagi9; 03-28-2022 at 11:37 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,855

    Re: Percent progress with future dates

    Try

    =(TODAY()-G11)/(H11-G11)
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    03-28-2022
    Location
    Hawaii
    MS-Off Ver
    2016
    Posts
    3

    Re: Percent progress with future dates

    Thanks! I've tried that formula as well, but it then sets future tasks at a negative percentage that gets closer to 0% with each passing day as it nears the Start Date. Maybe there is no way to start it at zero, but figured it was worth a try.

    i.e. J11 becomes -143% instead of 233%

  4. #4
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    931

    Re: Percent progress with future dates

    adding to the solution by JohnTopley....

    to prevent negative where the Start Date is in the future, wrap in IF <0, like this:
    Please Login or Register  to view this content.
    NOTE: this formula treats the first day as NOT being in progress

    If you want the first day to be part of progress, use this variation:
    Please Login or Register  to view this content.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  5. #5
    Registered User
    Join Date
    03-28-2022
    Location
    Hawaii
    MS-Off Ver
    2016
    Posts
    3

    Re: Percent progress with future dates

    This worked perfectly! Thank you so much!

+ 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. [SOLVED] Progress tracking i percent
    By paulsen in forum Excel General
    Replies: 6
    Last Post: 05-28-2021, 10:37 AM
  2. [SOLVED] A Macro for progress bar in status bar as a percent of time
    By capson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2014, 08:38 AM
  3. Hide Future Rows Containing Future Dates.
    By Raj_121 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2013, 07:14 AM
  4. [SOLVED] Calculating a future dates based on dates or birth.
    By CDobby in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-31-2012, 11:22 AM
  5. Future Dates
    By cal2600 in forum Excel General
    Replies: 2
    Last Post: 08-17-2009, 01:19 PM
  6. [SOLVED] Track progress to lookup percent complete on another worksheet.
    By IntricateFool in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-02-2006, 11:25 AM
  7. [SOLVED] future dates
    By kevrgallagher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-14-2005, 01:05 PM

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