☰

Bestimmen, ob die angegebene Zeit innerhalb von 15 Minuten der Zielzeit in Excel liegt

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Bestimmen, ob die angegebene Zeit innerhalb von 15 Minuten der Zielzeit in Excel liegt

Ich versuche, eine Tabelle zu erstellen, um die Aktualität der ausgeführten Backups zu überwachen. Backups müssen innerhalb von 15 Minuten nach der Zielzeit ausgeführt werden, um als konform zu gelten. Die von der Online-Überwachungsanwendung heruntergeladenen Rohdaten enthalten die folgenden relevanten Spalten:

  • Datum der Transaktion
  • Klebebandetikett
  • Geplante Zeit
  • Ausführungszeit

Ich habe die folgenden zusätzlichen Spalten hinzugefügt:

  • Konform (Ja/Nein)

    =WENN(UND(ZEIT(STUNDE(D3), MINUTE(D3), SEKUNDE(D3))>F3,ZEIT(STUNDE(D3), MINUTE(D3), SEKUNDE(D3))<G3),"Ja","Nein")

  • -15 Minuten(Geplante Zeit minus 15 Minuten)

    =WENN(ODER(C3=ZEIT(0,0,0),C3=ZEIT(0,1,0)),ZEIT(23,59,0),C3)-ZEIT(0,15,0)+WENN(C3=ZEIT(0,0,0),ZEIT(0,1,0),ZEIT(0,0,0))+WENN(C3=ZEIT(0,1,0),ZEIT(0,2,0),ZEIT(0,0,0))

  • +15 Minuten(Geplante Zeit plus 15 Minuten)

    =C3+"0:15"

Alle Zeitspalten verwenden das Zeitformat „13:30“.

Es funktioniert fast, aber es gibt ein Problem, wenn die Ausführungszeit „0,01“ und die geplante Zeit „0:00“ ist. Dies wird als nicht konform markiert, obwohl es konform ist. Kann bitte jemand dabei helfen, dies zum Laufen zu bringen?

Beispiel einer Datei

Ich verwende MS Office 2013.

AKTUALISIEREN

Weitere Kriterien:

  • Die „geplante Zeit“ ist die Uhrzeit, zu der die Sicherung an dem jeweiligen Tag ausgeführt wird. Sie kann also kein Datum enthalten, da sie für alle Daten gilt.
  • Manchmal werden Backups vor der geplanten Zeit ausgeführt. Das ist in Ordnung, solange es innerhalb von 15 Minuten nach der geplanten Zeit geschieht.

Mir ist aufgefallen, dass ich mein Blatt leicht ändern muss, damit es wie folgt aussieht, wobei die Monatsdaten wie unten nach rechts verlaufen und ich werde auch das Transaktionsdatum entfernen, da die Transaktion unter dem entsprechenden Datum erscheint. Bitte verwenden Sie jedoch das Obige für die Zwecke der Beispiele (das Bild unten soll Ihnen helfen zu verstehen, was wir berücksichtigen müssen).

Bildbeschreibung hier eingeben

Antwort1

Sie können es so machen:

=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 dient zum Testen, ob sich die tatsächliche Zeit auf den Tag davor oder danach bezieht. Dies ist für die Arbeit mit diesen Randfällen erforderlich.

Beachten Sie, dass dies C3+1die Zeit in C3 + 1 Tag bedeutet. Dies funktioniert, weil Excel Datum und Uhrzeit auf dieselbe Weise speichert, nämlich als Anzahl von Tagen ab dem 1.1.1900. Die Dezimalstellen sind tatsächlich die Zeit, die als Bruchteil eines Tages gespeichert wird. Wenn Sie eine Zeit eingeben, beispielsweise 12:00, wird sie als Zahl 0,5 gespeichert. Dieselbe Zeit am 1.2.1900 wäre 2,5.

Es können jedoch Fehlalarme auftreten, wenn Ihr tatsächliches Backup zwischen 23:45 und 24:15 Uhr zu spät oder zu früh erfolgt.

Antwort2

In meinem Beispiel enthalten die Spalten A bis D die gleichen Daten wie in deinem Beispiel. Die Spalten E, F und G werden dann mit den darunter liegenden Formeln ausgefüllt.

Sie könnten zwei Spalten mit einem Datums-/Zeitstempel hinzufügen. Eine für die geplante Zeit, die in Ihrem Fall (Spalte E) lautet:

=A2+C2

Und eine, die die Startzeit angibt (Spalte F).

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

Diese Formel prüft, ob die Startzeit kleiner ist als die geplante Zeit minus 15 Minuten (also der nächste Tag, und wenn sie mehr als 15 Minuten früher ist, ist sie in jedem Fall nicht konform) und fügt dann einen Tag zum Zeitdatumsstempel hinzu.

Dann können Sie einfach mit dieser Formel (Spalte G) prüfen, ob es konform ist:

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

Bearbeiten: Wenn ich mir alle Ihre Kommentare vorhin durchlese, handelt es sich bei der Startzeit und der geplanten Zeit um Datums-/Uhrzeitobjekte, also sollte so etwas Einfaches funktionieren

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

Antwort3

Ich denke, das geht mit Ihrer derzeitigen Einstellung nicht. Wenn Excel nur die ZEIT-Funktionen verwendet, kann es nicht zwischen gestern und heute unterscheiden. Im Hintergrund geht die ZEIT von 0 um Mitternacht auf .99999999 um 23:59:59 Uhr. Meines Wissens nach gibt es keine Rücksetzung auf 1.0000, 1.0001 usw.

Wenn Sie den vollständigen Zeitstempel in Ihren Bericht einfügen können, lässt sich Ihre Formel so anpassen, dass sie berücksichtigt, dass die 15 Minuten davor tatsächlich zum gestrigen Tag gehören können und die 15 Minuten danach tatsächlich zu einem zukünftigen Datum gehören.

Antwort4

Sie müssen die Datumsspalte berücksichtigen.

A: Transaktionsdatum
C: Zeitplan
D: Startzeit
E: Konform

-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)

Ergebnis für konform: (0 = Nein, 1 = Ja)

es geht auch ohne die Spalte -/+ 15 Min:

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

verwandte Informationen