☰

在 Excel 中确定指定时间是否在目标时间的 15 分钟内

tag-icon • tag-icon microsoft-excel worksheet-function date-time
在 Excel 中确定指定时间是否在目标时间的 15 分钟内

我正在尝试创建电子表格来监控执行备份的及时性。备份必须在目标时间的 15 分钟内执行，才能被视为合规。从在线监控应用程序下载的原始数据具有以下相关列：

  • 交易日期
  • 胶带标签
  • 时间安排
  • 执行时间

我添加了以下附加列：

  • 符合 (是/否)

    =IF(AND(TIME(HOUR(D3), MINUTE(D3), SECOND(D3))>F3,TIME(HOUR(D3), MINUTE(D3), SECOND(D3))<G3),"是","否")

  • -15分钟（预计时间减去 15 分钟）

    =IF(OR(C3=TIME(0,0,0),C3=TIME(0,1,0)),TIME(23,59,0),C3)-TIME(0,15,0)+IF(C3=TIME(0,0,0),TIME(0,1,0),TIME(0,0,0))+IF(C3=TIME(0,1,0),TIME(0,2,0),TIME(0,0,0))

  • +15分钟（比预定时间多 15 分钟）

    =C3+"0:15"

所有时间列均采用时间格式“13:30”。

它几乎可以正常工作，但当执行时间为“0.01”且计划时间为“0:00”时会出现问题。这被标记为不合规，但实际上它是合规的。有人可以帮忙让它正常工作吗？

文件示例

我正在使用 MS Office 2013。

更新

其他标准：

  • “计划时间”是备份在运行日期执行的时间，因此它不能包含日期，因为它适用于所有日期。
  • 备份有时会在预定时间之前执行。这没问题，只要是在预定时间的 15 分钟内即可。

我意识到我需要稍微修改一下我的表格，使其看起来像下面这样，月份的日期向右延伸，如下所示，我还将删除交易日期，因为交易将出现在相关日期下。但是，请使用上面的示例（下图是为了帮助理解我们需要适应什么）。

在此处输入图片描述

答案1

你可以这样做：

=IF(OR(ABS(D3-INT(D3)-(C3-INT(C3)))<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))-1)<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))+1)<TIME(0,15,0)),"Yes","No")

OR 用来测试实际时间是指前一天还是后一天。它对于这些边缘情况很有用。

请注意，这C3+1意味着 C3 中的任何时间 + 1 天。这是可行的，因为 Excel 以相同的方式存储日期和时间，即从 1900 年 1 月 1 日起的天数。小数实际上是以一天的分数存储的时间。如果您输入某个时间，比如 12:00，它将被存储为数字 0.5。1900 年 1 月 2 日的同一时间将是 2.5。

但是，如果您的实际备份在 23 小时 45 分至 24 小时 15 分之间迟到或早到，则可能会产生误报。

答案2

在我的示例中，A 至 D 列包含与您的示例相同的数据。然后，E、F 和 G 列填充了下面的公式。

您可以添加两列，其中包含日期/时间戳。一列用于计划时间，就您的情况而言（E 列）：

=A2+C2

其中一个是开始时间（F列）。

=IF(D2<C2-TIME(0,15,0),A2+1+D2,A2+D2)

此公式检查开始时间是否小于计划时间减 15 分钟（因此是第二天，如果早于 15 分钟以上，则无论如何都不符合要求），然后在日期戳上添加一天。

然后您只需检查它是否符合这个公式（G 列）：

IF(AND(F2<E2+TIME(0,15,0),F2>=E2),"Compliant","Not compliant")

编辑：阅读您之前的所有评论，开始时间和预定时间都是日期时间对象，因此像这样简单的操作应该可以起作用。

=IF(AND(D2>C2-TIME(0,15,0),D2<C2+TIME(0,15,0)),"Compliant","Non compliant")

答案3

我认为您目前的设置无法实现这一点。仅使用 TIME 函数，Excel 无法区分昨天和今天。在后台，TIME 从午夜的 0 变为晚上 11:59:59 的 .99999999。据我所知，它不会滚动到 1.0000、1.0001 等。

如果您可以将完整的时间戳放入报告中，那么您的公式就可以进行调整，以实现 15 分钟前实际上可能是昨天的一部分，或者 15 分钟后可能是未来的某个日期。

答案4

您必须考虑日期列。

A：交易日期
C：时间表
D：开始时间
E：合规

-15 Min column (F):  =A6+C6-TIME(0,15,0)  
+15 Min column (G):  =A6+C6+TIME(0,15,0)

Compliant column (E):   =--AND(A6+D6>=F6,A6+D6<=G6)

合规结果：（0 = 否，1 = 是）

您也可以不使用 -/+ 15 分钟列：

=--AND(A6+D6>=A6+C6-TIME(0,15,0),A6+D6<=A6+C6+TIME(0,15,0))

相关内容