Am Bei­spiel ein­er Zeit­er­fas­sung für Per­son­al soll fest­gestellt wer­den, ob der Arbeits­be­ginn inner­halb ei­nes gegebe­nen Zeit­fen­sters liegt. Das Arbeit­sende soll hier nicht er­ör­tert wer­den, kann aber nach dem gle­ichen Prin­zip in eine Ta­bel­le einge­fügt wer­den.

Wie so oft im Ex­cel-Le­ben füh­ren vie­le Wege zum Ziel. Und auch das de­fi­nier­te Ziel kann sich in Kleinigkeit­en unter­schei­den. Das prinzip­ielle Ziel soll sein, dass zu ei­nem gegebe­nen Da­tum der Arbeits­be­ginn einge­tra­gen wird und in ein­er Spal­te da­ne­ben in ir­gend ein­er Form dar­ge­stellt wird, ob der Arbeits­be­ginn inner­halb der vorgegebe­nen Kern­zeit ist oder nicht.

Grund­sät­zliche Wege sind: Mit Ex­cel-For­meln, also ohne VBA oder per Ma­kro, 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 be­lie­bi­ger Text wie „Ja”, „Nein” oder was auch im­mer.

Als weit­eren Bau­stein kön­nen Sie dann auch noch an­ge­ben, dass am Woch­enende an­de­re Kernzeit­en für den Arbeits­be­ginn gel­ten; natür­lich et­was spä­ter. 😉 In der fol­gen­den Abbil­dung se­hen Sie all die­se Para­me­ter ver­wirk­licht, und dazu gibt es hier die Ex­cel-Ta­bel­le mit al­len For­meln und auch mit dem Ma­kro. Beacht­en Sie bit­te, dass bei der Da­tei ohne Ma­kros die Spal­te G na­tur­ge­mäß kei­ne Funk­tion­al­ität hat.

Ta­bel­le mit der kur­zen For­mel in dem Eingabebere­ich

Hier eine ganz kur­ze Erk­lärung dazu: Spal­te A:B soll­ten selb­sterk­lärend sein. In den bei­den fol­gen­den Spal­ten C:D wird per For­mel ein­mal der Wahr­heits­wert 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 Spal­te B nichts drin ste­ht.

In den Spal­ten E:F ist die gle­iche Lo­gik wie in den bei­den Spal­ten da­vor, nur gilt hier, dass auch das Woch­enende berück­sichtigt wird. Bere­its auf der Abbil­dung hier­un­ter erken­nen Sie, dass die For­mel „et­was” län­ger ist:

Ta­bel­le mit der lan­gen For­mel in dem Eingabebere­ich

Mehr dazu fol­gt gle­ich. Die Spal­te G wird von ei­nem Ma­kro, durch VBA-Code ge­füllt. Im­mer wenn in Spal­te B eine Än­de­rung bei der Uhr­zeit ein­tritt, wird hier automa­tisch ein Wert einge­tra­gen oder die Zel­le ge­leert. So viel zu den Ba­sics, den Grund­la­gen. Blei­ben noch ei­ni­ge Anmerkun­gen zu den Auswer­tungs-Spal­ten zu ma­chen.…

▲ nach oben …

Alle Spal­ten

Für alle Spal­ten gilt das Prin­zip: Wenn in Spal­te B nichts drin­nen ste­ht, dann wird auch nichts aus­gew­ertet. Es wird bei „kommt„auch eine kor­rek­te Uhr­zeit er­war­tet, son­st kommt es zu Prob­le­men.

Spal­te C

Hier ist die ein­fach­ste For­mel drin­nen:

=WENN(B2; SUM­MEN­PRO­DUKT((B2>=Mo­Fr_Fr) * (B2<=Mo­Fr_Sp))>0; "")

Grund­sät­zlich gilt für die­se und die drei Folges­pal­ten, dass we­gen der hö­he­ren Trans­parenz Bere­ich­sna­men ver­wen­det wor­den sind. So ste­ht beispiel­sweise Mo­Fr_Fr für Mon­tag bis Fre­itag Früh, was durch die Namen­szuweisung der Zel­le J2 ent­spricht. In der Spal­te G (VBA) wur­de im Code die ab­so­lu­te Adres­se ver­wen­det.

Leicht ist die­se For­mel ge­wiss nicht zu ver­ste­hen, ins­beson­dere für Ein­steiger. Aber die­ses soll ja auch kein Ex­cel-Kurs sein, dar­um nur ei­ni­ge stich­wor­tar­tige Hin­weise zu den ver­wen­de­ten Funk­tio­nen.

=WENN(B2;

Das ist auf den ers­ten Blick ge­wiss irri­tierend. Kein Ver­gle­ich­sop­er­a­tor? Nein, denn das be­deu­tet so viel wie WENN B2=WAHR. Und Wahr ist hier al­les, was nicht leer oder 0 ist. Mit an­de­ren Wor­ten: Wenn in B2 et­was drin­nen ste­ht, dann …

Inner­halb des SUM­MEN­PRO­DUKTs wer­den 2 Ver­gle­iche durchge­führt. Ist der Ver­gle­ich WAHR, dann wird in­tern eine 1 angenom­men, bei FALSCH eine 0. Die Aus­gabe erfol­gt wie­der­um als Wahr­heits­wert nach den eben beschriebe­nen Re­geln. Und das Ergeb­nis se­hen Sie in der Ta­bel­le.

Be­den­ken Sie bei die­ser und der Folges­palte, dass Woch­enen­den wie die rest­li­chen Tage der Wo­che behan­delt wer­den, also im­mer die Zeit­en Mon­tag..​Freitag gerech­net wer­den! Dar­um gibt es auch Unter­schiede zu den an­de­ren Berech­nun­gen.

▲ nach oben …

Spal­te D

=WENN(B2; WENN(SUM­MEN­PRO­DUKT((B2>=Mo­Fr_Fr) * (B2<=Mo­Fr_Sp))>0; "Ja"; "Nein"); "")

Prinzip­iell ist die For­mel hier wie in der vorheri­gen Spal­te, nur wird hier über eine weit­ere WENN – Bedin­gung fest­gelegt, dass der ent­spre­chen­de Text statt ei­nes Wahr­heits­wer­tes aus­gegeben wird. Natür­lich gilt auch hier, dass ein Woch­enende ge­nau so bew­ertet wird wie der Rest der Wo­che.

Spal­te E

=WENN(B2;WENN(WO­CHEN­TAG(A2;2)<6; SUM­MEN­PRO­DUKT((B2>=Mo­Fr_Fr) *   (B2<=Mo­Fr_Sp))>0; SUM­MEN­PRO­DUKT((B2>=Sa­So_Fr) * (B2<=Sa­So_Sp))>0); "")

Das sieht mäch­tig aus. Und prinzip­iell ist es das auch. Wenn Sie ge­nau hin­se­hen, ist es aber „nur” die For­mel aus Spal­te C, wel­che über eine weit­ere WENN – Funk­tion entschei­det, ob es sich um ein Woch­enende han­delt oder nicht. Beacht­en Sie bit­te, dass die Funk­tion WO­CHEN­TAG() hier mit ei­nem zweit­en Argu­ment aus­ges­tat­tet wur­de.

Spal­te F

=WENN(B2; WENN(WO­CHEN­TAG(A2;2)<6; WENN(SUM­MEN­PRO­DUKT((B2>=Mo­Fr_Fr) *  (B2<=Mo­Fr_Sp))>0; "Ja"; "Nein"); WENN(SUM­MEN­PRO­DUKT((B2>=Sa­So_Fr) * (B2<=Sa­So_Sp))>0;"Ja";"Nein")); "")

… Und noch eine WENN – Ver­schachtelung mehr. Wie auch in Spal­te D wird hier statt des Wahr­heits­wer­tes ein frei festzule­gen­der Text aus­gegeben. Und ich den­ke, dass hier die Gren­ze der Über­sichtlichkeit erre­icht ist. Dar­um gibt es auch eine Alter­na­tive …

▲ nach oben …

Spal­te G

Die Alter­na­tive für mehr Über­sichtlichkeit hei­ßt VBA, Pro­gram­mierung per Ma­kro. Zu­ge­ge­ben, wer nie pro­gram­miert hat, ste­ht da wahrschein­lich ge­nau so hil­f­los da­vor 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 vor­neh­men kön­nen.

Der Code ist noch einiger­maßen über­sichtlich und um­fasst nur we­ni­ge Zei­len. Und er tut ge­nau das, was er soll. Zu­mal es hier wirk­lich ein­fach ist, noch Fei­er­ta­ge (ein­schließlich der be­weg­li­chen wie Os­tern) 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 Zel­len der Spal­te B um­fasst, die zu dem entsprechen­den Mo­nat ge­hö­ren. Wenn eine Än­de­rung in die­sem Bere­ich ge­schieht, wird der In­halt aus­gew­ertet und in Spal­te G OK oder Nicht OK ge­schrie­ben; wird die Uhr­zeit ge­löscht, dann wird auch ein even­tu­ell vorhan­den­er In­halt in Spal­te G ge­löscht.