☰

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+1C3 の時刻 + 1 日を意味することに注意してください。Excel は日付と時刻を 1/1/1900 からの日数として同じように保存するため、このようになります。小数は、実際には 1 日の分数として保存された時間です。たとえば 12:00 という時刻を入力すると、数値 0.5 として保存されます。1/2/1900 の同じ時刻は 2.5 になります。

ただし、実際のバックアップが 23 時 45 分から 24 時 15 分の間に遅れたり早まったりした場合は、誤検知が発生する可能性があります。

答え2

私の例では、列 A から D にはあなたの例と同じデータが含まれています。列 E、F、G には、その下の数式が入力されます。

日付/時刻スタンプ付きの列を 2 つ追加できます。1 つは、このケースのスケジュール時間用です (列 E):

=A2+C2

そして、開始時刻（列 F）です。

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

この数式は、開始時刻の時間がスケジュールされた時間から 15 分を引いた時間より小さいかどうか (つまり翌日で、15 分以上早い場合はどちらにしても非準拠) を確認し、日付スタンプに 1 日を追加します。

次に、この式 (列 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))

関連情報