5

I have a gant with start date, end date, and % complete columns. By manually entering a number in the % column the bar representing the task gets shaded. What I want to do is instead of representing % completed, I want to show how much time is left before the end date from today.

      Start        End       % Time remaining from TODAY()
i.e. 12/01/2014   03/15/2015   (End date has not yet occurred)
     12/29/2014   12/29/2014   (Task was started and finished this day)
BBDev
  • 93
  • 1
  • 1
  • 8
  • What have you tried so far? If nothing yet then here are some hints. Know that excel treats dates as numbers. So you can subtract one date from another, or today() from another and simply divide the results. You will also likely need an `IF` statement. – guitarthrower Jan 15 '15 at 16:39
  • @guitarthrower Good points but an `IF` statement is avoidable if you prefer `MAX` and `MIN`. One question though is whether today counts as completed. – Mark Balhoff Jan 15 '15 at 16:51
  • @MarkBalhoff I also tend to use `MAX` and `MIN`. I had to make an assumption that the OP is a beginner and the concept of `IF` may be an easier start. – guitarthrower Jan 15 '15 at 16:53
  • 1
    @guitarthrower Point taken. I tend to find `MAX` and `MIN` for date operations a more elegant solution (especially in complicated formulas) but I concur that explaining why that works to people always seems much more difficult than a "simple" `IF`. – Mark Balhoff Jan 15 '15 at 16:57
  • 2
    @pnuts Really close and borderline but for the sake of discussion let me make a case for the OP. Same concept but solving for a different variable (and different known variables too). IMHO though, where the biggest difference lies is the potential duplicate you referenced does not address the out of bounds condition (at least not in the answers there). Handling a TODAY() that is outside the valid date range (here) is a much more unavoidable issue than handling percentage less than 0 or greater than 100 (proposed duplicate). – Mark Balhoff Jan 15 '15 at 20:52

2 Answers2

5

Assuming your end date is in column B:

=IF(TODAY()>=B2,"Done",CONCATENATE(B2-TODAY(),""))

This will show you the number of days remaining. If you want the percentage of time spent, use

=IF(TODAY()>=B2,"Done",MAX((TODAY()-A2)/MAX(B2-A2,1),0))

and format the cell as a percentage.

Ben I.
  • 1,065
  • 1
  • 13
  • 29
  • I accepted it befoe I tried it on tasks that haven't yet begun, A2 is in the future. – BBDev Jan 15 '15 at 18:59
  • Which one do you need me to fix? (And what behavior would you expect when A2 is in the future?) – Ben I. Jan 15 '15 at 19:20
  • =IF(TODAY()>=B2,"Done",(TODAY()-A2)/(B2-A2)) Is the one I need because I need a %. If A2 is in the future I would just like to show 0% complete. – BBDev Jan 15 '15 at 19:26
  • `=IF(TODAY()>=B2,"Done",MAX((TODAY()-A2)/MAX(B2-A2,1),0))` You might have to set the format of the cell to percent again when you put the new formula in. (I had to). I also made it not break if the start date and end date are the same. – Ben I. Jan 15 '15 at 19:31
0

Here is a bit more succinct option it will show the percent completion as of the end of the current day.

=(MIN(TODAY(),B2)-A2+1)/(B2-A2+1)
guitarthrower
  • 5,624
  • 3
  • 29
  • 37