Nicht sel­ten stellt sich die Fra­ge, ob ein bes­timmtes Da­tum oder eine ge­ge­be­ne Uhr­zeit in ei­nem zu ver­gle­ichen­den Zeitrah­men liegt. Die Fra­ge kann laut­en, ob der 25.12. ein Fei­er­tag ist (ja klar 😉 ) oder ob am 11.​August des ak­tu­el­len Jah­res Betrieb­s­fe­rien sind. Auch für Zei­tangaben kann solch ein Ergeb­nis ge­for­dert wer­den, beispiel­sweise in wel­che Schicht ein Vor­fall um 17:15 Uhr fällt.

Um mit eini­gen klei­nen Beispie­len arbeit­en zu kön­nen, ha­ben wir Ih­nen eine Mus­ter­datei er­stellt. An­hand die­ses Tabel­len­blatts zei­gen wir Ih­nen auf, wie Sie ziel­gerichtet eine Ant­wort auf die Fra­ge bekom­men, ob die­ser bes­timmte Ter­min in ei­nem de­fi­nier­ten Zeit­raum (Da­tum oder Uhr­zeit) liegt.

▲ nach oben …

Da­tum

Ge­ge­ben sei ir­gend ein berech­netes oder von Hand ein­ge­ge­be­nes (kalen­darisches) Da­tum. Es gibt ver­schiedene Kri­te­rien, auf wel­che die­ser Tag über­prüft wer­den kann. Wir ver­suchen, hier mög­lichst vie­le Fragestel­lun­gen einzu­binden … Wir ver­wen­den prinzip­iell die Zel­ladresse A1, wo das abzufra­gende Da­tum oder die Uhr­zeit drin­nen ste­ht. 

Ist Da­tum ein Wo­chen­en­de (Sams­tag oder Sonn­tag)?

Das lässt sich recht ein­fach mit ein­er Funk­tion beant­worten:
=WO­CHEN­TAG(A1; 21)>5
gibt das Ergeb­nis WAHR oder FALSCH zu­rück. Bei­spiel für eine Funk­tion mit ein­er „vernün­fti­gen” Tex­taus­gabe:

="Der " & TEXT(A1; "T. MMMM JJJJ") & " ist " & WENN(WOCHENTAG(A1; 2)<6; "kein"; "ein") & " Wochenende."

Beacht­en Sie bit­te hier und auch den weit­eren For­meln, dass die­se in 1 Zei­le der Bear­beitungsleiste ge­hö­ren. Die Darstel­lung hier im Blog ist mit­un­ter mehr­zei­lig. 

▲ nach oben …

Ist Da­tum ein Fei­er­tag?

Ex­cel „weiß” natür­lich nicht, ob ein ein­ge­ge­be­nes Da­tum ein Fei­er­tag ist oder oder nicht. Wo­her auch schon in der EU sind di­ver­se Tage in ei­nem Staat Fei­er­ta­ge und im an­de­ren nicht. Selb­st in Deutsch­land ist es nicht im­mer ein­heitlich. Ver­suchen Sie ein­mal am Rosen­mon­tag in Köln eine ge­öff­ne­te Bank zu find­en, das wird ge­wiss schwie­rig. Dar­um ha­ben wir in der Mus­ter-Map­pe ei­nen Bere­ich geschaf­fen, wo alle Fei­er­ta­ge des ak­tu­el­len und des Fol­ge­jahres ver­merkt sind. Sie wer­den natür­lich den ei­nen oder an­de­ren Tag lö­schen oder lo­ka­le Fei­er­ta­ge hinzufü­gen. In die­sen Fäl­len acht­en Sie bit­te dar­auf, dass Sie erforder­lichen­falls den Bere­ich­sna­men an­pas­sen. Sie wer­den gle­ich se­hen, dass das arbeit­en mit Bere­ich­sna­men viel an­ge­neh­mer ist und da­durch eine For­mel we­sent­lich trans­par­enter wird. Zu­erst die WAHR/FALSCH – For­mulierung:
=NICHT(ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH)))

Wenn die Grun­dregel für den SVER­WEIS(), dass die Such­w­erte auf­steigend sor­tiert sein müs­sen be­ach­tet wur­de (wich­tig bei Änderun­gen), dann ist das Ergeb­nis auch rich­tig. Und der Voll­ständigkeit hal­ber noch eine For­mel, wie sie dur­chaus vorkom­men kön­nte:

="Der " & TEXT(A1; "TT.MM.JJJJ") & " ist "& WENN(ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH)); "k"; "") & "ein Feiertag."

 Zwei klei­ne Hin­weise zu der For­mel: Wenn Da­tum nicht im Namens­bere­ich Fei­er­ta­ge gefun­den wird, dann gibt die Funk­tion ei­nen Fehler­w­ert zu­rück. Und die­ser wird mit IST­FEH­LER() abge­fragt. In der Excel-Ver­sio­nen ab 2007 gibt es eine et­was ein­fachere Funk­tion: WENN­FEH­LER(); die­se ist hier aber nicht wirk­lich hil­fre­ich. 

▲ nach oben …

Ist Da­tum ein Ar­beits­tag?

Was auf den ers­ten Blick ganz ein­fach zu beant­worten scheint, ent­pup­PT sich rasch als et­was kom­plex­er. Mon­tag bis Fre­itag sind Arbeit­stage, klar. Au­ßer wenn der Tag ger­ade ein Fei­er­tag ist. OK, stimmt auch. Und natür­lich auch auch dann nicht, wenn da ger­ade Betrieb­s­fe­rien sind. Sie se­hen, der ers­te An­schein täuscht mit­un­ter.

OK, die Sa­che mit dem Wochen­tag ist ganz leicht: 
=WO­CHEN­TAG(A1; 2)<6
gibt das logis­che Ergeb­nis zu­rück. Auch den Fei­er­tag ha­ben wie ger­ade vor­her ab­ge­checkt. Um das Gan­ze Schritt für Schritt zu erar­beit­en, be­las­sen wir es erst ein­mal bei die­ser Kon­stel­la­tion und „be­haup­ten”, dass es kei­ne Werks­fe­rien gibt. Dann müss­te die Funk­tion im Umgangs­deutsch so ausse­hen: „Wenn Da­tum im Bere­ich Mon­tag .. Fre­itag liegt und auch kein Fei­er­tag ist, dann ha­ben wir ei­nen Arbeit­stag, son­st nicht”.

Bei solch ver­schachtel­ten Bedin­gun­gen emp­feh­len wir stets, erst ein­mal jede ein­zel­ne Bedin­gung in ein­er getren­nten Zel­le auszuw­erten und dann in ein­er weit­eren Zel­le die Gesam­tauswer­tung zu for­mulieren. Bei­spiel: in F1 ste­ht die For­mel zur Berech­nung des Wochen­t­ages: =WO­CHEN­TAG(A1; 2)<6. Das Ergeb­nis ist ein Bool’scher Wert. In F2 kommt die Funk­tion in Sa­chen Fei­er­tag:
=IST­FEH­LER(SVER­WEIS(A1; Fei­er­ta­ge; 1; FALSCH))
Auch hier wird ein Wahr­heits­wert zu­rück ge­ge­ben. Und in F3 schrei­ben Sie nun die­se For­mel: =UND(F1;F2). Na­tur­ge­mäß ist das Ergeb­nis auch ein WAHR oder FALSCH, welch­es aus­sagt, ob es sich bei dem Da­tum in A1 um ei­nen Arbeit­stag han­delt oder nicht. Und in F4 noch ein­mal als „sprechen­des” Ergeb­nis:
="Der " & TEXT(A1; "TT.MM.JJJJ") &  " ist " & WENN(UND(F1; F2); "ein"; "kein") & " Arbeitstag."

Nun ist das Mit den Hil­fs-Zellen nicht im­mer der Hit.​Und natür­lich las­sen sich die bei­den For­meln so zusam­men­fassen, dass eine ein­zi­ge, wenn auch kom­plexere (sprich: län­gere) For­mel in der Ein­ga­be­zei­le ste­ht und in der entsprechen­den Zel­le das Ergeb­nis aus­gibt:

=WENN(UND(WOCHENTAG(A1; 2)<6; ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH))); ""; "Kein ") & "Arbeitstag"

Sin­nvoller­weise wird im End­ef­fekt auf ei­nen Arbeit­stag ge­prüft, weil ja ver­schiedene Para­me­ter wahr oder falsch sein kön­nen. Pro­bieren Sie es aus, es haut hin. 🙂

 Bleibt als drit­tes Prüfele­ment noch der Werk­surlaub, die Betrieb­s­fe­rien. Hier muss ge­prüft wer­den, ob Da­tum inner­halb ei­nes Zeit­raums liegt oder nicht. Zu­ge­ge­ben, das geht mit der klas­sis­chen Meth­ode auch ganz gut und über­sichtlich: Sie prü­fen, ob Da­tum >= ers­ter Tag des Ur­laubs und <= let­zter Feri­en­tag ist. Wenn ja, dann ist an dem Tag das Werk ge­schlos­sen. Es geht aber auch um ei­ni­ges ele­gan­ter. 

 Zur Vor­bere­itung ha­ben wir für das ak­tu­el­le und das Fol­ge­jahr jew­eils vier Urlaub­swochen Werks­fe­rien einge­tra­gen. Und bei­de Jah­re ha­ben auch ei­nen Bere­ich­sna­men ver­liehen bekom­men: BFJahr1 und BFJahr2. Dass „BF für Betrieb­s­fe­rien ste­ht ha­ben Sie vielle­icht schon erkan­nt. Die wirk­lich gran­dio­se For­mel, wel­che wir dort in Spal­te A einge­set­zt ha­ben, ist von Franz Pölt / Exel­for­meln.de. Da­mit wird im­mer der Mon­tag ein­er de­fi­nier­ten Kalen­der­woche ei­nes bes­timmten Jah­res berech­net.

Wie­der­um mit ei­nem Wahr­heits­wert als Rück­gabe soll­ten Sie für das lau­fen­de Jahr fol­gende, ganz kurz gehal­tene For­mel ver­wen­den: =MEDIAN(BFJahr1;A1)=A1. Ers­paren Sie uns die detail­lierte Erk­lärung, die Excel-Hil­fe (F1) so­wie das Inter­net kön­nen hier hil­fre­ich sein.

Meist wird die Ab­fra­ge auf 1 Jahr rei­chen. Wol­len Sie je­doch Da­tum für das ak­tu­el­le und das Fol­ge­jahr mit den Betrieb­s­fe­rien ver­gle­ichen, dann kön­nte die For­mel so laut­en:
=ODER(MEDIAN(BFJahr1; A1)=A1; MEDIAN(BFJahr2; A1)=A1)

Die ODER()-For­mulierung für alle drei Bedin­gun­gen soll­ten Sie nun in Eigenini­tia­tive fer­tig bekom­men. Sie ist ein­fach nur eine Er­wei­te­rung der Zweierkom­bi­na­tion, wel­che Sie bere­its ken­nen. Im Ein­zel­fall wer­den wir auch bere­it sein, Ih­nen zu hel­fen.

▲ nach oben …

Zeit

Angenom­men, ein Fehler­pro­tokoll soll aus­gew­ertet wer­den. Es wird zu je­dem Vor­fall die Uhr­zeit aus­gegeben. In der Über­sicht soll nun aus­gegeben wer­den, ob es nun die Früh- die Spät- oder die Nacht­schicht gewe­sen ist. – Das geht natür­lich auch über die altherge­brachte Me­tho­dik mit ein­er ex­trem lan­gen WENN() und ODER()-Kom­bi­na­tion. Aber was ger­ade eben bei den kalen­darischen Dat­en ge­klappt hat, das geht ge­nau so gut mit Zeit­en. Die ME­DI­AN()-Funk­tion greift auch hier. Und noch ei­nes: In A1 ste­ht nun kein Da­tum son­dern die zu ver­gle­ichende Zeit.

Natür­lich ist auch jede der Schicht­en mit ei­nem Bere­ich­sna­men verse­hen. Da­bei ist „ei­nem” nicht so ganz kor­rekt. Die Nacht­schicht geht ja über Mit­ter­nacht hin­aus. Für und Men­schen ist zwar klar, dass ein Zeit­raum von beispiel­sweise 23:00 Uhr bis 02:00 Uhr drei Stun­den sind, aber Ex­cel kann das nicht so ohne weit­eres erken­nen. dar­um ist die Nacht­schicht hier auch in zwei Parts un­ter­teilt und hat ent­spre­chend auch zwei Bere­ich­sna­men. Bei der Gele­gen­heit: Schau­en Sie sich ger­ne ein­mal die For­meln an. Sie sind nicht über­wälti­gend aber ganz prag­ma­tisch. – Um auf die Früh­schicht zu prü­fen, ge­ben Sie die­se For­mel ein:
=MEDIAN(Frühschicht;A1)=A1

Die Lo­gik ist die gle­iche, wie beim Betrieb­surlaub. Nur dass hier die bei­den Eck­w­erte untere­inan­der ange­ord­net sind. Und das Ergeb­nis stimmt auch sekun­den­ge­nau. Apro­pos Sekun­den: Sie kön­nen die For­matierung der Zeit­en ger­ne dahinge­hend än­dern, dass nur hh:mm an­ge­zeigt wer­den; aber nur die For­matierung, die In­hal­te der Zel­len müs­sen (natür­lich) erhal­ten blei­ben.

 Für die Spät­schicht gilt das eben ge­sag­te zu prak­tisch 100%; die ein­zi­ge Aus­nahme ist na­tur­ge­mäß, dass die Spät­schicht abge­fragt wird. Und auch die Nacht­schicht wird so ver­glichen. Nur dass es hier zwei Zeitrah­men gibt, ei­nen bis und ei­nen ab Mit­ter­nacht. Nun gilt es nur noch, das Gan­ze in eine WENN()-Funk­tion zu pack­en:

=WENN(MEDIAN(Frühschicht; A1)=A1; "Frühschicht"; WENN(MEDIAN(Spätschicht; A1)=A1; "Spätschicht"; "Nachtschicht"))

Ge­wiss wird Ih­nen aufge­fall­en sein, dass gar nicht expliz­it auf „Nacht­schicht” abge­fragt wor­den ist und so­mit der zwei­te Block in der Nacht­schicht ei­gent­lich nicht erforder­lich ist. Un­ser Kom­men­tar dazu: „Jein” ;-). In die­sem Fall hät­ten wir die Zeit­en für die Nacht­schicht gar nicht an­ge­ben müs­sen. Aber neh­men Sie doch ein­fach ein­mal an, dass nur ge­prüft wer­den soll, ob eine ge­ge­be­ne Zeit in die Nacht­schicht fällt oder nicht. Als Bei­spiel sei die Zeit 6:45 (wie­der­um in A1).  Dann wäre die Ab­fra­ge auf <7:00 kor­rekt und ziel­füh­rend. Als näch­stes ste­ht dort aber 23:44 als Uhr­zeit drin. Und das ist kei­nes­wegs klein­er als 7:00 Uhr. Dar­um bie­tet sich hier fol­gende For­mel an: 
=ODER(MEDIAN(Nacht1;A1)=A1;MEDIAN(Nacht2;A1)=A1)
wom­it ganz ein­deutig die Nacht­schicht iden­ti­fiziert wird.