Zeitfenster

Liegt eine Zeit innerhalb eines Zeitfensters?

Am Beispiel einer Zeiterfassung für Personal soll festgestellt werden, ob der Arbeitsbeginn innerhalb eines gegebenen Zeitfensters liegt. Das Arbeitsende soll hier nicht erörtert werden, kann aber nach dem gleichen Prinzip in eine Tabelle eingefügt werden.

Wie so oft im Excel-Leben führen viele Wege zum Ziel. Und auch das definierte Ziel kann sich in Kleinigkeiten unterscheiden. Das prinzipielle Ziel soll sein, dass zu einem gegebenen Datum der Arbeitsbeginn eingetragen wird und in einer Spalte daneben in irgend einer Form dargestellt wird, ob der Arbeitsbeginn innerhalb der vorgegebenen Kernzeit ist oder nicht.

Grundsätzliche Wege sind: Mit Excel-Formeln, also ohne VBA oder per Makro, also mit VBA. Und natürlich die Definition der Auswertung des Arbeitsbeginns. Hier kann ein WAHR oder FALSCH als logischer Wert ausgegeben werden oder ein beliebiger Text wie „Ja“, „Nein“ oder was auch immer.

Als weiteren Baustein können Sie dann auch noch angeben, dass am Wochenende andere Kernzeiten für den Arbeitsbeginn gelten; natürlich etwas später. 😉 In der folgenden Abbildung sehen Sie all diese Parameter verwirklicht, und dazu gibt es hier die Excel-Tabelle mit allen Formeln und auch mit dem Makro. Beachten Sie bitte, dass bei der Datei ohne Makros die Spalte G naturgemäß keine Funktionalität hat.

Tabelle mit der kurzen Formel in dem Eingabebereich

Tabelle mit der kurzen Formel in dem Eingabebereich

Hier eine ganz kurze Erklärung dazu: Spalte A:B sollten selbsterklärend sein. In den beiden folgenden Spalten C:D wird per Formel einmal der Wahrheitswert und einmal (als Alternative) der Ja/Nein – Wert ausgegeben. Hier wird noch nicht zwischen Werktag und Wochenende unterschieden. Wie für alle Spalten der Auswertung gilt, dass nicht ausgewertet wird, wenn in Spalte B nichts drin steht.

In den Spalten E:F ist die gleiche Logik wie in den beiden Spalten davor, nur gilt hier, dass auch das Wochenende berücksichtigt wird. Bereits auf der Abbildung hierunter erkennen Sie, dass die Formel „etwas“ länger ist:

Tabelle mit der langen Formel in dem Eingabebereich

Tabelle mit der langen Formel in dem Eingabebereich

Mehr dazu folgt gleich. Die Spalte G wird von einem Makro, durch VBA-Code gefüllt. Immer wenn in Spalte B eine Änderung bei der Uhrzeit eintritt, wird hier automatisch ein Wert eingetragen oder die Zelle geleert. So viel zu den Basics, den Grundlagen. Bleiben noch einige Anmerkungen zu den Auswertungs-Spalten zu machen.…

▲ nach oben …

Alle Spalten

Für alle Spalten gilt das Prinzip: Wenn in Spalte B nichts drinnen steht, dann wird auch nichts ausgewertet. Es wird bei „kommt“auch eine korrekte Uhrzeit erwartet, sonst kommt es zu Problemen.

Spalte C

Hier ist die einfachste Formel drinnen:

=WENN(B2; SUMMENPRODUKT((B2>=MoFr_Fr) * (B2<=MoFr_Sp))>0; "")

Grundsätzlich gilt für diese und die drei Folgespalten, dass wegen der höheren Transparenz Bereichsnamen verwendet worden sind. So steht beispielsweise MoFr_Fr für Montag bis Freitag Früh, was durch die Namenszuweisung der Zelle J2 entspricht. In der Spalte G (VBA) wurde im Code die absolute Adresse verwendet.

Leicht ist diese Formel gewiss nicht zu verstehen, insbesondere für Einsteiger. Aber dieses soll ja auch kein Excel-Kurs sein, darum nur einige stichwortartige Hinweise zu den verwendeten Funktionen.

=WENN(B2;

Das ist auf den ersten Blick gewiss irritierend. Kein Vergleichsoperator? Nein, denn das bedeutet so viel wie WENN B2=WAHR. Und Wahr ist hier alles, was nicht leer oder 0 ist. Mit anderen Worten: Wenn in B2 etwas drinnen steht, dann …

Innerhalb des SUMMENPRODUKTs werden 2 Vergleiche durchgeführt. Ist der Vergleich WAHR, dann wird intern eine 1 angenommen, bei FALSCH eine 0. Die Ausgabe erfolgt wiederum als Wahrheitswert nach den eben beschriebenen Regeln. Und das Ergebnis sehen Sie in der Tabelle.

Bedenken Sie bei dieser und der Folgespalte, dass Wochenenden wie die restlichen Tage der Woche behandelt werden, also immer die Zeiten Montag..Freitag gerechnet werden! Darum gibt es auch Unterschiede zu den anderen Berechnungen.

▲ nach oben …

Spalte D

=WENN(B2; WENN(SUMMENPRODUKT((B2>=MoFr_Fr) * (B2<=MoFr_Sp))>0; "Ja"; "Nein"); "")

Prinzipiell ist die Formel hier wie in der vorherigen Spalte, nur wird hier über eine weitere WENN – Bedingung festgelegt, dass der entsprechende Text statt eines Wahrheitswertes ausgegeben wird. Natürlich gilt auch hier, dass ein Wochenende genau so bewertet wird wie der Rest der Woche.

Spalte E

=WENN(B2;WENN(WOCHENTAG(A2;2)<6; SUMMENPRODUKT((B2>=MoFr_Fr) *   (B2<=MoFr_Sp))>0; SUMMENPRODUKT((B2>=SaSo_Fr) * (B2<=SaSo_Sp))>0); "")

Das sieht mächtig aus. Und prinzipiell ist es das auch. Wenn Sie genau hinsehen, ist es aber „nur“ die Formel aus Spalte C, welche über eine weitere WENN – Funktion entscheidet, ob es sich um ein Wochenende handelt oder nicht. Beachten Sie bitte, dass die Funktion WOCHENTAG() hier mit einem zweiten Argument ausgestattet wurde.

Spalte F

=WENN(B2; WENN(WOCHENTAG(A2;2)<6; WENN(SUMMENPRODUKT((B2>=MoFr_Fr) *  (B2<=MoFr_Sp))>0; "Ja"; "Nein"); WENN(SUMMENPRODUKT((B2>=SaSo_Fr) * (B2<=SaSo_Sp))>0;"Ja";"Nein")); "")

… Und noch eine WENN – Verschachtelung mehr. Wie auch in Spalte D wird hier statt des Wahrheitswertes ein frei festzulegender Text ausgegeben. Und ich denke, dass hier die Grenze der Übersichtlichkeit erreicht ist. Darum gibt es auch eine Alternative …

▲ nach oben …

Spalte G

Die Alternative für mehr Übersichtlichkeit heißt VBA, Programmierung per Makro. Zugegeben, wer nie programmiert hat, steht da wahrscheinlich genau so hilflos davor wie bei der „Monsterformel“ hierüber, aber für jemanden, der sich in einer beliebigen Programmiersprache oder sogar in VB oder VBA auskennt, der wird sehr einfach Anpassungen im Code vornehmen können.

Der Code ist noch einigermaßen übersichtlich und umfasst nur wenige Zeilen. Und er tut genau das, was er soll. Zumal es hier wirklich einfach ist, noch Feiertage (einschließlich der beweglichen wie Ostern) einzubringen:

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
   Dim lRow As Integer
   Dim Ultimo As Integer
   Dim Datum1 As Date
   Dim ZeitOK As Boolean

   Datum1 = Range("A2")
   Ultimo = Day(DateSerial(Year(Datum1), Month(Datum1) + 1, 0))
   lRow = Ultimo + 1
   If Not Intersect(Range("B2:B" & lRow), Target) Is Nothing Then
      If Target Then
         ZeitOK = KernZeitOK(Target.Address)
         Range("G" & Target.Row) = IIf(ZeitOK, "OK", "Nicht OK")
      Else
         Range("G" & Target.Row) = Null
      End If
   End If
End Sub

Hier wird erst einmal ein Überwachungsbereich festgelegt, der alle Zellen der Spalte B umfasst, die zu dem entsprechenden Monat gehören. Wenn eine Änderung in diesem Bereich geschieht, wird der Inhalt ausgewertet und in Spalte G OK oder Nicht OK geschrieben; wird die Uhrzeit gelöscht, dann wird auch ein eventuell vorhandener Inhalt in Spalte G gelöscht.

▲ nach oben …

Dieser Beitrag wurde unter Datum und Zeit, Mit VBA/Makro, Ohne Makro/VBA abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.