☰

格式化单元格时，按分钟而不是小数来划分小时，但保持公式不变。这可能吗？

tag-icon • tag-icon microsoft-excel worksheet-function format time
格式化单元格时，按分钟而不是小数来划分小时，但保持公式不变。这可能吗？

我最近用过这个一个单元格内的时间计算公式而且效果非常好！

但我仍然有一个问题：有没有办法允许故障时间？

例如，使用这个：

9pm - 4:30pm

而不是这样：

9pm - 4.5pm

我 知道 我 可以 打字4.5pm, 但 我 担心 人们 会 阅读4:50pm而 不是 打字4:30pm.

答案1

在我看来，这些公式无法解析格式中的时间下午 3:24。 所以不，同时不保持公式的完整性。

我相信我已经得到了有效的公式，使用TIMEVALUE而不是TIME。

编辑：

@FlexYourData 指出了我原始答案中的一个错误（在底部）；部分原因是我无意中遗漏了下午从解析中。

另一部分是TIMEVALUE似乎不喜欢没有空格的时间。

这是一个不关心空格的版本。


单元格 B2：

=TIMEVALUE(LEFT(A2,FIND("-",A2)-3)&" "&MID(A2,FIND("-",A2)-2,2))


单元格 C2：

=TIMEVALUE(MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)-2)&" "&RIGHT(A2,2))


单元格 D2 — 此单元格无变化：

=IF(C2>B2,C2-B2,C2+1-B2)*24

原始答案（略有更正）。这些公式仅适用于下午 3:24和下午 3 点时间——注意空格。不过我喜欢它们，因为它们更简单。也许你可以要求以这种格式输入时间。


单元格 B2：

=TIMEVALUE(LEFT(A2,FIND("-",A2)-1))


单元格 C2：

=TIMEVALUE(MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)))


单元格 D2 — 此单元格无变化：

=IF(C2>B2,C2-B2,C2+1-B2)*24

答案2

使用公式执行此操作有点麻烦，所以我使用了 LET，希望可以使其更易于阅读。使用 PowerQuery 会容易得多，但我认为这会起作用。如果您可以访问 （LAMBDA不幸的是我没有），我建议使用它。

首先，我将开始/结束分隔符（即“ - ”）定义为单元格 B2 中名为“sep”的命名单元格：

在此处输入图片描述

我已经这样做了，因此如果您将来想将其更改为“-”或其他内容，您不必在公式的几个地方更改它，而只需在一个单元格中更改它。

带有开始和结束的辅助列

这里的方法大致如下：

  1. 确定字符串中分隔符的位置并将其称为“pos”
  2. 以文本形式返回时间并将其命名为“str”
  3. 确定文本时间是否包含分钟（以冒号表示）并将其称为“hasmin”
  4. 计算“str”的时间表示，它是根据您使用的原始答案修改而来的，但根据“hasmin”是否为 TRUE 采取不同的操作

B3中的开始时间公式：

=LET(pos,FIND(sep,$A3),
str,LEFT($A3,pos1),
hasmin,NOT(ISERROR(FIND(":",str))),
TIME(LEFT(str,IF(hasmin,FIND(":",str)-1,LEN(str)-2))+IF(RIGHT(str,2)="pm",12,0),IF(hasmin,MID(str,FIND(":",str)+1,2),0),0))

C3 中的结束时间公式：

=LET(pos,FIND(sep,A3),
str,MID(A3,pos+LEN(sep),LEN(A3)),
hasmin,NOT(ISERROR(FIND(":",str))),
TIME(LEFT(str,IF(hasmin,FIND(":",str)-1,LEN(str)-2))+IF(RIGHT(str,2)="pm",12,0),IF(hasmin,MID(str,FIND(":",str)+1,2),0),0))

D3 中的小时数计算公式：

=IF(C3>B3,C3-B3,C3+1-B3)*24

作为单一公式

这里我刚刚将“开始时间”和“结束时间”两个列合并到同一个中LET，分别称为“from_time”和“to_time”。最后一个表达式，即结果，就是“已用小时数”列中的公式，但引用的是函数中的名称LET而不是列。

在 E3 中：

=LET(break_pos,FIND(sep,$A3),

from_text,LEFT($A3,break_pos-1),
from_has_mins,NOT(ISERROR(FIND(":",from_text))),
from_time,TIME(LEFT(from_text,IF(from_has_mins,FIND(":",from_text)-1,LEN(from_text)-2))+IF(RIGHT(from_text,2)="pm",12,0),IF(from_has_mins,MID(from_text,FIND(":",from_text)+1,2),0),0),

to_text,MID($A3,break_pos+LEN(sep),LEN($A3)),
to_has_mins,NOT(ISERROR(FIND(":",to_text))),
to_time,TIME(LEFT(to_text,IF(to_has_mins,FIND(":",to_text)-1,LEN(to_text)-2))+IF(RIGHT(to_text,2)="pm",12,0),IF(to_has_mins,MID(to_text,FIND(":",to_text)+1,2),0),0),

IF(to_time>from_time,to_time-from_time,to_time+1-from_time)*24
)

相关内容