Am Beispiel ein­er Zeit­er­fas­sung für Per­son­al soll fest­gestellt wer­den, ob der Arbeits­be­ginn inner­halb eines gegebe­nen Zeit­fen­sters liegt. Das Arbeit­sende soll hier nicht erörtert wer­den, kann aber nach dem gle­ichen Prinzip in eine Tabelle einge­fügt wer­den.

Wie so oft im Excel-Leben führen viele Wege zum Ziel. Und auch das definierte Ziel kann sich in Kleinigkeit­en unter­schei­den. Das prinzip­ielle Ziel soll sein, dass zu einem gegebe­nen Datum der Arbeits­be­ginn einge­tra­gen wird und in ein­er Spalte daneben in irgend ein­er Form dargestellt wird, ob der Arbeits­be­ginn inner­halb der vorgegebe­nen Kernzeit ist oder nicht.

Grund­sät­zliche Wege sind: Mit Excel-Formeln, also ohne VBA oder per Makro, also mit VBA. Und natür­lich die Def­i­n­i­tion der Auswer­tung des Arbeits­be­ginns. Hier kann ein WAHR oder FALSCH als logis­ch­er Wert aus­gegeben wer­den oder ein beliebiger Text wie „Ja”, „Nein” oder was auch immer.

Als weit­eren Baustein kön­nen Sie dann auch noch angeben, dass am Woch­enende andere Kernzeit­en für den Arbeits­be­ginn gel­ten; natür­lich etwas später. 😉 In der fol­gen­den Abbil­dung sehen Sie all diese Para­me­ter ver­wirk­licht, und dazu gibt es hier die Excel-Tabelle mit allen Formeln und auch mit dem Makro. Beacht­en Sie bitte, dass bei der Datei ohne Makros die Spalte G naturgemäß keine Funk­tion­al­ität hat.

Tabelle mit der kurzen Formel in dem Eingabebere­ich

Hier eine ganz kurze Erk­lärung dazu: Spalte A:B soll­ten selb­sterk­lärend sein. In den bei­den fol­gen­den Spal­ten C:D wird per Formel ein­mal der Wahrheitswert und ein­mal (als Alter­na­tive) der Ja/Nein – Wert aus­gegeben. Hier wird noch nicht zwis­chen Werk­tag und Woch­enende unter­schieden. Wie für alle Spal­ten der Auswer­tung gilt, dass nicht aus­gew­ertet wird, wenn in Spalte B nichts drin ste­ht.

In den Spal­ten E:F ist die gle­iche Logik wie in den bei­den Spal­ten davor, nur gilt hier, dass auch das Woch­enende berück­sichtigt wird. Bere­its auf der Abbil­dung hierunter erken­nen Sie, dass die Formel „etwas” länger ist:

Tabelle mit der lan­gen Formel in dem Eingabebere­ich

Mehr dazu fol­gt gle­ich. Die Spalte G wird von einem Makro, durch VBA-Code gefüllt. Immer wenn in Spalte B eine Änderung bei der Uhrzeit ein­tritt, wird hier automa­tisch ein Wert einge­tra­gen oder die Zelle geleert. So viel zu den Basics, den Grund­la­gen. Bleiben noch einige Anmerkun­gen zu den Auswer­tungs-Spal­ten zu machen.…

▲ nach oben …

Alle Spalten

Für alle Spal­ten gilt das Prinzip: Wenn in Spalte B nichts drin­nen ste­ht, dann wird auch nichts aus­gew­ertet. Es wird bei „kommt„auch eine kor­rek­te Uhrzeit erwartet, son­st kommt es zu Prob­le­men.

Spalte C

Hier ist die ein­fach­ste Formel drin­nen:

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

Grund­sät­zlich gilt für diese und die drei Folges­pal­ten, dass wegen der höheren Trans­parenz Bere­ich­sna­men ver­wen­det wor­den sind. So ste­ht beispiel­sweise MoFr_Fr für Mon­tag bis Fre­itag Früh, was durch die Namen­szuweisung der Zelle J2 entspricht. In der Spalte G (VBA) wurde im Code die absolute Adresse ver­wen­det.

Leicht ist diese Formel gewiss nicht zu ver­ste­hen, ins­beson­dere für Ein­steiger. Aber dieses soll ja auch kein Excel-Kurs sein, darum nur einige stich­wor­tar­tige Hin­weise zu den ver­wen­de­ten Funk­tio­nen.

=WENN(B2;

Das ist auf den ersten Blick gewiss irri­tierend. Kein Ver­gle­ich­sop­er­a­tor? 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 drin­nen ste­ht, dann …

Inner­halb des SUMMENPRODUKTs wer­den 2 Ver­gle­iche durchge­führt. Ist der Ver­gle­ich WAHR, dann wird intern eine 1 angenom­men, bei FALSCH eine 0. Die Aus­gabe erfol­gt wiederum als Wahrheitswert nach den eben beschriebe­nen Regeln. Und das Ergeb­nis sehen Sie in der Tabelle.

Bedenken Sie bei dieser und der Folges­palte, dass Woch­enen­den wie die restlichen Tage der Woche behan­delt wer­den, also immer die Zeit­en Montag..Freitag gerech­net wer­den! Darum gibt es auch Unter­schiede zu den anderen Berech­nun­gen.

▲ nach oben …

Spalte D

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

Prinzip­iell ist die Formel hier wie in der vorheri­gen Spalte, nur wird hier über eine weit­ere WENN – Bedin­gung fest­gelegt, dass der entsprechende Text statt eines Wahrheitswertes aus­gegeben wird. Natür­lich gilt auch hier, dass ein Woch­enende genau so bew­ertet 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 prinzip­iell ist es das auch. Wenn Sie genau hin­se­hen, ist es aber „nur” die Formel aus Spalte C, welche über eine weit­ere WENN – Funk­tion entschei­det, ob es sich um ein Woch­enende han­delt oder nicht. Beacht­en Sie bitte, dass die Funk­tion WOCHENTAG() hier mit einem zweit­en Argu­ment aus­ges­tat­tet 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 – Ver­schachtelung mehr. Wie auch in Spalte D wird hier statt des Wahrheitswertes ein frei festzule­gen­der Text aus­gegeben. Und ich denke, dass hier die Gren­ze der Über­sichtlichkeit erre­icht ist. Darum gibt es auch eine Alter­na­tive …

▲ nach oben …

Spalte G

Die Alter­na­tive für mehr Über­sichtlichkeit heißt VBA, Pro­gram­mierung per Makro. Zugegeben, wer nie pro­gram­miert hat, ste­ht da wahrschein­lich genau so hil­f­los davor wie bei der „Mon­ster­formel” hierüber, aber für jeman­den, der sich in ein­er beliebi­gen Pro­gram­mier­sprache oder sog­ar in VB oder VBA ausken­nt, der wird sehr ein­fach Anpas­sun­gen im Code vornehmen kön­nen.

Der Code ist noch einiger­maßen über­sichtlich und umfasst nur wenige Zeilen. Und er tut genau das, was er soll. Zumal es hier wirk­lich ein­fach ist, noch Feiertage (ein­schließlich der beweglichen wie Ostern) einzubrin­gen:

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 ein­mal ein Überwachungs­bere­ich fest­gelegt, der alle Zellen der Spalte B umfasst, die zu dem entsprechen­den Monat gehören. Wenn eine Änderung in diesem Bere­ich geschieht, wird der Inhalt aus­gew­ertet und in Spalte G OK oder Nicht OK geschrieben; wird die Uhrzeit gelöscht, dann wird auch ein eventuell vorhan­den­er Inhalt in Spalte G gelöscht.