☰

Excel中判斷指定時間是否在目標時間15分鐘以內

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Excel中判斷指定時間是否在目標時間15分鐘以內

我正在嘗試建立電子表格來監控執行的備份的及時性。備份必須在目標時間後 15 分鐘內執行，才能被視為合規。從線上監測應用程式下載的原始資料具有以下相關欄位：

  • 交易日期
  • 膠帶標籤
  • 預定時間
  • 執行時間

我已向其中新增了以下附加列：

  • 合規（是/否）

    =IF(AND(時間(小時(D3), 分鐘(D3), 秒(D3))>F3,時間(小時(D3), 分鐘(D3), 秒(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=時間(0,0,0),時間(0,1,0),時間(0,0,0))+IF(C3=時間(0,1,0),時間(0,2, 0),時間(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 的 0.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 Min 列的情況下執行此操作：

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

相關內容