☰

Excel 公式将 4.15 解释为 4 小时 15 分钟

tag-icon • tag-icon microsoft-excel equations
Excel 公式将 4.15 解释为 4 小时 15 分钟

我正在为我的公司创建时间表/轮班表，每个单元格包含上班时间和下班时间，显示为“9-3”。

我创建了一个函数，它将对 6 天的字符串进行求和，计算出数值差9 和 3 之间有 15，公式中的 3 被计算为 15。这可以作为固定时间，但是当包含除整数以外的任何数字时，时间解释就会中断。“9.30-3”返回一个数字，该数字不能解释为正确的时间。这是可以理解的，因为我没有将数字注册为时间，但这主要是由于源数据的格式 - 即带连字符的范围。

我知道时间计算是通过正确的十进制时间和重新格式化单元格以显示解释的时间来解决的，但是我的源数据的性质不允许这样做，或者至少我必须改变一个已经很大的方程来正确地量化十进制的时间。

此外，重要的是要知道输入的信息将要格式为 4.15，表示下午 4:15，而不是 Excel 读取的 15/100 小时格式。

答案1

假设我们有A1：

8.45-2.23

在B1进入：

=--LEFT(A1,FIND("-",A1)-1)

以及C1进入：

=--MID(A1,FIND("-",A1)+1,9999)

这两个单元格是开始和停止时间的“十进制”版本。D1我们根据上午/下午进行调整：

=IF(C1<B1,C1+12,C1)

最后E1进入：

=TIMEVALUE(SUBSTITUTE(D1,".",":"))-TIMEVALUE(SUBSTITUTE(B1,".",":"))

在此处输入图片描述

采用合理的格式。

如果需要的话，可以将它们组合成一个公式：

=TIMEVALUE(SUBSTITUTE((IF((--MID(A1,FIND("-",A1)+1,9999))<(--LEFT(A1,FIND("-",A1)-1)),(--MID(A1,FIND("-",A1)+1,9999))+12,(--MID(A1,FIND("-",A1)+1,9999)))),".",":"))-TIMEVALUE(SUBSTITUTE((--LEFT(A1,FIND("-",A1)-1)),".",":"))

答案2

好吧，我设法自己做了这件事。我意识到时间格式不太容易从单元格中提取出来，所以我决定解析单元格的元素。这意味着，从一个文本格式为“9.30-5.15”的单元格中，我需要 Excel 来理解：

  • 9 点开始轮班小时
  • & 30分钟
  • & 0 秒
  • 5 点下班小时
  • & 15分钟
  • & 0 秒
  • 5 小时少于 9 小时，因此加 12 小时，即达到 24 小时
  • 从现在较少的时间中减去现在较大的时间

下面的公式看上去相当丑陋，但它确实有效（在一定程度上）。

=IF(ISBLANK(A1),TIME(0,0,0),IFERROR(IF((--MID(A1,FIND("-",A1)+1,9999))<=(--LEFT(A1,FIND("-",A1)-1)),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(MID(A1,1,SEARCH(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,SEARCH(".*-",A1)+1,SEARCH("-",A1)-SEARCH(".*-",A1)-1),0),0)+TIME(12,0,0),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(LEFT(A1,FIND(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,FIND(".",A1)+1,SEARCH(".*-",A1)),0),0)),(TIME(0,0,0))))

此公式允许将空白单元格算作零次，也允许将填有单词的单元格算作零次，以免破坏计算。

这样做的目的是创建每月轮班表，并按以下格式输入时间：

  • 9-3
  • 9.30-3
  • 9-3.30
  • 9.30-3.30

这意味着轮班经理可以输入时间（可以随着每日变化而变化），工作表将计算一周的 6 个工作日，并通过条件格式突出显示达到工作时间配额时的计算，如下图所示： 工时表轮换计算

希望这对任何想做同样事情的人有所帮助！！只有一个错误我还没有处理——第二次你不能输入 2 位数的小时数。所以你可以输入 3-9.59，但不能输入 3-10。这只是因为我设计这个的地方没有人在晚上 6 点后工作，而我也没有需要修复它...所以何必呢。不过，如果你想要清除它并使用它，修复它并不太难。

如果这对您有用，请告诉我，因为我已经绞尽脑汁让它按照现在的方式工作了一段时间！

相关内容