# Percent progress with future dates

1. ## 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!

2. ## Re: Percent progress with future dates

Try

=(TODAY()-G11)/(H11-G11)

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. ## 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.``

5. ## Re: Percent progress with future dates

This worked perfectly! Thank you so much!

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