求Excel/Google Sheets基于起止日期自动计算项目进度的公式

问题场景 #

当前日期：23/04/2023

项目名称	开始日期	结束日期	进度	状态
project 1	17/01/2023	30/03/2023	100%	completed
project 2	24/01/2023	04/04/2023	100%	failed
project 3	15/02/2023	15/05/2023	80%	progress

需求：

• 自动计算项目进度，支持跨年度项目（如20/12/2023至10/02/2024）
• 临近结束日期时进度自动设为100%
• 手动标记的状态字段（completed/failed）优先覆盖进度值

解决方案 #

核心逻辑 #

1. 手动标记状态优先：若状态为completed或failed，进度直接显示100%
2. 进行中项目：
   • 当前日期距结束日期小于等于N天（可自定义）或已过结束日期，进度设为100%
   • 否则按当前日期在项目周期内的时间占比计算进度，确保值在0%-100%之间

Excel 公式实现 #

假设数据从第2行开始，开始日期在B列，结束日期在C列，状态在E列，在D2单元格输入以下公式后下拉填充：

=IF(OR(E2="completed",E2="failed"),1,
 IF(TODAY()>=C2-3,1,
  MAX(0,MIN(1,(TODAY()-B2)/(C2-B2)))))

最后将D列单元格格式设置为百分比。

公式说明

• OR(E2="completed",E2="failed")：匹配手动标记的状态，符合则返回1（即100%）
• TODAY()>=C2-3：设置临近结束的阈值（这里是提前3天，可修改数字调整），满足则返回1
• (TODAY()-B2)/(C2-B2)：计算当前日期在项目总周期内的占比，MAX(0,MIN(1,...))确保进度不会出现负数或超过100%

Google Sheets 公式实现 #

逻辑与Excel完全一致，在D2单元格输入公式后下拉填充：

=IF(OR(E2="completed",E2="failed"),1,
 IF(TODAY()>=C2-3,1,
  MAX(0,MIN(1,(TODAY()-B2)/(C2-B2)))))

同样设置单元格格式为百分比即可。

跨年度项目处理 #

公式通过计算日期差值来获取天数，Excel和Google Sheets会自动处理跨年日期的计算，无需额外调整。比如2023-12-20到2024-02-10的周期天数会被正确计算，当前日期的占比也能准确得出。

自定义调整 #

• 调整临近结束阈值：修改C2-3中的数字，比如改为C2-7表示提前7天显示100%进度
• 按工作日计算：将日期差值替换为NETWORKDAYS函数，例如NETWORKDAYS(B2,TODAY())/NETWORKDAYS(B2,C2)，Excel需启用分析工具库，Google Sheets直接支持

内容的提问来源于stack exchange，提问作者dhanyn10