☰

这是在 Excel 中显示负时间值的最佳策略吗？

tag-icon • tag-icon microsoft-excel worksheet-function date-time
这是在 Excel 中显示负时间值的最佳策略吗？

我正在尝试发送一个计算单元格，duedate - now()以便量化逾期与剩余时间（负值 = 逾期，正值 = 剩余时间）。考虑：

      Col A DueDate    Col B  NOW         Col C remaining time
Row1  3/7/2013  9:00   3/8/2013   12:00   -1:03:00:00
Row1  3/8/2013 15:00   3/8/2013   12:00    0:03:00:00
Row3  3/9/2013  9:00   3/8/2013   12:00    1:03:00:00

然而，

  • 该工作表链接到很多使用 1900 日期系统的其他工作表，因此，我对更改为 1904 系统持谨慎态度，因为两个日期系统之间日期的解释方式存在不可预测性。
  • A1-B1产量##################，正如我们预期的那样，当日期返回负日期时。
  • 我确实找到了文本转换，TEXT(ABS(A1-B1),-h:mm:ss但因为它只是在绝对值上添加一个负数，所以每个日期值都返回为负数。ABS是正确的，只是没有符号。

以此为线索，我尝试了TEXT((A1-B1),h:mm:ss但得到了VALUE!#；我假设它不喜欢时间数学的负面结果。

这：IF(B1>A1,(TEXT(ABS(A1-B1),-h:mm:ss,A1-B1)有几个问题。

  • 今天到期的任何款项均会返回0:00:00:00；它似乎忽略了小时、分钟和秒。
  • 按时间对列进行排序似乎可以ABS这样排序：我的“0:00:00:00”值位于列表顶部，最大负数位于底部。我预计正常排序将具有最大负数，然后是零，然后升序到最高值。

编辑：

Martineau 的建议让我更接近目标，但任何在未来一两天内到期的事情都不太确定。我确实修改了 toh以[h]考虑超过 24 小时的时间。

DueDate         NOW()           
3/1/2013 17:00  3/8/2013 12:16  -163:16:11
3/7/2013 17:00  3/8/2013 12:16  -19:16:11
3/8/2013 12:16  3/8/2013 12:16  992196:16:11    <-- ???
3/15/2013 17:00 3/8/2013 12:16  992369:00:00   <--  ???
3/31/2013 17:00 3/8/2013 12:16  992753:00:00   <--  ???
5/8/1944 12:00  3/8/2013 12:16  -603384:16:11
6/14/2238 7:00  3/8/2013 12:16  2966839:00:00
3/8/2013 9:00   3/8/2013 12:16  -3:16:11
3/8/2013 17:00  3/8/2013 12:16  992201:00:00   <--  ???

但是，当h最后一个为时h，相同的行将返回 sqirrely 值：

DueDate         NOW()       3/1/2013 17:00  3/8/2013 12:23  -19:23:45
3/7/2013 17:00  3/8/2013 12:23  -19:23:45
3/8/2013 12:23  3/8/2013 12:23  12:23:45     <-- ???
3/15/2013 17:00 3/8/2013 12:23  17:00:00     <-- ???
3/31/2013 17:00 3/8/2013 12:23  17:00:00     <-- ???
5/8/1944 12:00  3/8/2013 12:23  -0:23:45
6/14/2238 7:00  3/8/2013 12:23  7:00:00
3/8/2013 9:00   3/8/2013 12:23  -3:23:45
3/8/2013 17:00  3/8/2013 12:23  17:00:00     <-- ???

有没有更优雅的方式来按照我希望的方式格式化 C 列？

答案1

我认为这个公式是可行的：

=IF(B1>A1, TEXT(B1-A1,"-h:mm:ss"), TEXT(A1-B1,"h:mm"))

但仅适用于时差小于 24 小时的情况。对于较长的时间，您可以使用：

=IF(B1>A1, "-",) & TEXT(INT(ABS(B1-A1)), "#,##0") & "d " & TEXT(MOD(ABS(B1-A1), 1), "hh:mm")

得到如下结果（我将剩余时间右对齐）：

DueDate             Now                 Remaining Time
03/01/2013 17:00    03/08/2013 12:16         -6d 19:16
03/07/2013 17:00    03/08/2013 12:16         -0d 19:16
03/08/2013 12:16    03/08/2013 12:16          0d 00:00
03/15/2013 17:00    03/08/2013 12:16          7d 04:44
03/31/2013 17:00    03/08/2013 12:16         23d 04:44
05/08/1944 12:00    03/08/2013 12:16    -25,141d 00:16
06/14/2238 07:00    03/08/2013 12:16     82,276d 18:44
03/08/2013 09:00    03/08/2013 12:16         -0d 03:16
03/08/2013 17:00    03/08/2013 12:16          0d 04:44

如果有必要，它或许还可以扩展到处理年份。但是，完全准确地做到这一点会有点棘手，因为一年中的天数会变化1 —— 所以这将留给读者练习。;-)

[1] 根据维基百科，公历年是 365.2425 天。

答案2

如果您希望 Excel 显示负数时间，即使在使用它们进行计算时，您也可以更改时间的创建方式。您可以转到工具、选项、计算选项卡并选择 1904 日期系统来执行此操作。现在，这会将负数时间显示为 -xx:xx:xx

相关内容