给定开始日期，结束日期和今天（），如何计算任务完成百分比

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)

2 个解决方案

#1

Assuming your end date is in column B:

假设您的结束日期在B列：

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

= IF（TODAY（）> = B2， “完成”，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))

= IF（TODAY（）> = B2， “完成”，MAX（（TODAY（） - A2）/ MAX（B2-A2,1），0））

and format the cell as a percentage.

并将单元格格式化为百分比。

#2

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)

#1