如何修正Projected End Time与Actual End Time差值计算的异常结果？

问题背景 #

你有两列时间数据：

• Projected End Time（计划结束时间）
• Actual End Time（实际结束时间）

原本用公式=SUM(K3-J3)（其中K3是实际结束时间，J3是计划结束时间）计算时间差，但出现异常：

• 当实际晚于计划（如3:50 PM | 3:55 PM），结果显示0:05，符合预期；
• 当实际早于计划（如3:50 PM | 3:45 PM），结果显示23:55，但需要显示为+0:05来体现时间节省。

解决方法 #

方法1：公式判断+格式输出 #

用IF函数判断时间差的正负，搭配文本格式实现带符号的正确显示：

=IF(J3-K3>0, "+"&TEXT(J3-K3,"h:mm"), TEXT(K3-J3,"h:mm"))

逻辑说明：

• 若**计划结束时间(J3) - 实际结束时间(K3)**为正（实际提前完成），在结果前添加+并按h:mm格式输出；
• 若实际晚于计划，直接输出正常时间差格式。

方法2：自定义单元格格式（无需修改原公式） #

保留原公式=J3-K3，给目标单元格设置自定义格式：

[h]:mm;+[h]:mm;0:00

格式说明：

• [h]:mm：处理正数（实际晚于计划），显示正常时间差；
• +[h]:mm：处理负数（实际早于计划），自动添加+并显示绝对值的时间差；
• 0:00：处理差值为0的情况，显示0:00。

注：[h]用于支持超过24小时的时间差，若你的时间差都在一天内，也可以用h:mm;+h:mm;0:00。

原理说明 #

Excel中时间本质是小数（1天=1，1小时=1/24），当计算出负数时间时，会自动借一天（加24小时），导致出现23:55这类异常显示。上述方法通过判断正负或自定义格式，直接输出带符号的真实时间差。

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