时间戳

首页 > 解决方案 > 计算时间段是否包含在开始时间和结束时间之内

excel - 计算时间段是否包含在开始时间和结束时间之内

问题描述

我有一个问题，我试图在 Excel 中计算提供的工作值班时间段的任何部分是否包含在用户定义的时间段内，该时间段指定夜间工作时间，例如 2330-0559 或 0000-0630。

如果我提供了我想要的工作值班时间，并且任何部分的职责都在指定的期限内，则需要对其进行识别。例如，2230-0630 工作时间在上述两个示例中。

我可以尝试一些不同的解决方案，但仍然没有找到正确的方法来解决它。也许有人可以帮忙。我也知道 excel 中的 24:00 用于一天结束时的午夜，而 00:00 用于从一天开始的午夜，因为 excel 作为一天的一部分从 0-1 工作。

样品溶液

=OR(MOD(A5,1)>$E$2,MOD(A5,1)<$F$2)

标签： excelexcel-formula

解决方案

我会使用十进制的分钟数，并使用它在“值班开始时间”和“值班结束时间”之间建立一个有效范围。

检查你是否能理解这一点：

在此处输入图像描述

这里的例子：

https://drive.google.com/file/d/1O1B76srlY8sYQHsV_TiZVzi-7B_18eSj/view?usp=sharing


更新：抱歉没有解释。在这里，我尽力澄清我是如何做到的。

转换

首先，我尝试将任何时间转换为分钟，所以 00:00 是 0 分钟，01:00 是 60 分钟，24:00（excel 显示 00:00）是 1440 分钟，25:00（excel 显示 01:00 ) 为 1500 分钟。

我用 进行的CONVERT(E2,"day", "mn")转换，确实从一天转换为分钟。

范围归一化和包含

现在每个范围都必须标准化，这意味着“结束”必须始终大于“开始”。对于 00:00 到 06:00，这可以正常工作，但是对于 22:00 到 06:00，它需要进行调整。因此，如果“开始”大于“结束”，那么我将 1440 分钟（24 小时）添加到“结束”。我用IF(A6>B6, 1440,0).

然后，您需要查看是否有任何“工作范围”包含“夜间范围”。这通常是用公式完成的if (DutyEnd > NightStart AND DuttyStart < NightEnd) then TRUE。

挑战一

这是主要概念。但是，如果您尝试比较 24:00-06-00 中包含的 00:00-06:00 之类的范围，则它不起作用。那是因为转换后的分钟是 0-360 和 1440-1830，它们不包含彼此。

在 E3 中，为了解决这个问题，我用模 ( ) 减少了 1440 MOD()，然后MOD(1440, 1440)= 0。因此，即使您使用 25:00 或 52:00 之类的值，它们也会被减少到最小的分钟数。例如 25:00（1500 分钟）= 1:00 = 60 分钟，52:00（3120 分钟）= 4:00 = 240 分钟

挑战 2

我们还有另一个挑战，可能的比较如下：

  • 00:00-06:00 包含在 00:00-06:00 中，以分钟为单位是 0-390 包含在 0-390 中
  • 00:00-06:00 包含在 22:00-06:00 中，以分钟为单位是 0-390 包含在 1320-1830 中
  • 22:00-06:00 包含在 00:00-06:00 中，分钟为 1320-1830 包含在 0-390

最后两个将不匹配。这就是为什么在“包含”列（例如：E6）中我与“夜间标准”进行比较 - 1400 和 + 1400。

希望它有点清楚。否则让我知道..

推荐阅读