Menu
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)
965
To determine the percentage of completion, you divide current costs by total costs and multiply the result by 100.
Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots.
Its calculated as (actual/target)*100. My problem is that some of the targets are actually negative. So if you had target of -2 and the actual was 4 the calculation will be -200%. This is incorrect as it should be -300%.
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.
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
