Liegt ein Datum oder eine Zeit in einem definiertem Zeitraum?
Nicht selten stellt sich die Frage, ob ein bestimmtes Datum oder eine gegebene Uhrzeit in einem zu vergleichenden Zeitrahmen liegt. Die Frage kann lauten, ob der 25.12. ein Feiertag ist (ja klar 😉 ) oder ob am 11.August des aktuellen Jahres Betriebsferien sind. Auch für Zeitangaben kann solch ein Ergebnis gefordert werden, beispielsweise in welche Schicht ein Vorfall um 17:15 Uhr fällt.
Um mit einigen kleinen Beispielen arbeiten zu können, haben wir Ihnen eine Musterdatei erstellt. Anhand dieses Tabellenblatts zeigen wir Ihnen auf, wie Sie zielgerichtet eine Antwort auf die Frage bekommen, ob dieser bestimmte Termin in einem definierten Zeitraum (Datum oder Uhrzeit) liegt.
Datum
Gegeben sei irgend ein berechnetes oder von Hand eingegebenes (kalendarisches) Datum. Es gibt verschiedene Kriterien, auf welche dieser Tag überprüft werden kann. Wir versuchen, hier möglichst viele Fragestellungen einzubinden … Wir verwenden prinzipiell die Zelladresse A1, wo das abzufragende Datum oder die Uhrzeit drinnen steht.
Ist Datum ein Wochenende (Samstag oder Sonntag)?
Das lässt sich recht einfach mit einer Funktion beantworten:
=WOCHENTAG(A1; 21)>5
gibt das Ergebnis WAHR oder FALSCH zurück. Beispiel für eine Funktion mit einer „vernünftigen” Textausgabe:
="Der " & TEXT(A1; "T. MMMM JJJJ") & " ist " & WENN(WOCHENTAG(A1; 2)<6; "kein"; "ein") & " Wochenende."
Beachten Sie bitte hier und auch den weiteren Formeln, dass diese in 1 Zeile der Bearbeitungsleiste gehören. Die Darstellung hier im Blog ist mitunter mehrzeilig.
Ist Datum ein Feiertag?
Excel „weiß” natürlich nicht, ob ein eingegebenes Datum ein Feiertag ist oder oder nicht. Woher auch schon in der EU sind diverse Tage in einem Staat Feiertage und im anderen nicht. Selbst in Deutschland ist es nicht immer einheitlich. Versuchen Sie einmal am Rosenmontag in Köln eine geöffnete Bank zu finden, das wird gewiss schwierig. Darum haben wir in der Muster-Mappe einen Bereich geschaffen, wo alle Feiertage des aktuellen und des Folgejahres vermerkt sind. Sie werden natürlich den einen oder anderen Tag löschen oder lokale Feiertage hinzufügen. In diesen Fällen achten Sie bitte darauf, dass Sie erforderlichenfalls den Bereichsnamen anpassen. Sie werden gleich sehen, dass das arbeiten mit Bereichsnamen viel angenehmer ist und dadurch eine Formel wesentlich transparenter wird. Zuerst die WAHR/FALSCH – Formulierung:
=NICHT(ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH)))
Wenn die Grundregel für den SVERWEIS(), dass die Suchwerte aufsteigend sortiert sein müssen beachtet wurde (wichtig bei Änderungen), dann ist das Ergebnis auch richtig. Und der Vollständigkeit halber noch eine Formel, wie sie durchaus vorkommen könnte:
="Der " & TEXT(A1; "TT.MM.JJJJ") & " ist "& WENN(ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH)); "k"; "") & "ein Feiertag."
Zwei kleine Hinweise zu der Formel: Wenn Datum nicht im Namensbereich Feiertage gefunden wird, dann gibt die Funktion einen Fehlerwert zurück. Und dieser wird mit ISTFEHLER() abgefragt. In der Excel-Versionen ab 2007 gibt es eine etwas einfachere Funktion: WENNFEHLER(); diese ist hier aber nicht wirklich hilfreich.
Ist Datum ein Arbeitstag?
Was auf den ersten Blick ganz einfach zu beantworten scheint, entpupPT sich rasch als etwas komplexer. Montag bis Freitag sind Arbeitstage, klar. Außer wenn der Tag gerade ein Feiertag ist. OK, stimmt auch. Und natürlich auch auch dann nicht, wenn da gerade Betriebsferien sind. Sie sehen, der erste Anschein täuscht mitunter.
OK, die Sache mit dem Wochentag ist ganz leicht:
=WOCHENTAG(A1; 2)<6
gibt das logische Ergebnis zurück. Auch den Feiertag haben wie gerade vorher abgecheckt. Um das Ganze Schritt für Schritt zu erarbeiten, belassen wir es erst einmal bei dieser Konstellation und „behaupten”, dass es keine Werksferien gibt. Dann müsste die Funktion im Umgangsdeutsch so aussehen: „Wenn Datum im Bereich Montag .. Freitag liegt und auch kein Feiertag ist, dann haben wir einen Arbeitstag, sonst nicht”.
Bei solch verschachtelten Bedingungen empfehlen wir stets, erst einmal jede einzelne Bedingung in einer getrennten Zelle auszuwerten und dann in einer weiteren Zelle die Gesamtauswertung zu formulieren. Beispiel: in F1 steht die Formel zur Berechnung des Wochentages: =WOCHENTAG(A1; 2)<6. Das Ergebnis ist ein Bool’scher Wert. In F2 kommt die Funktion in Sachen Feiertag:
=ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH))
Auch hier wird ein Wahrheitswert zurück gegeben. Und in F3 schreiben Sie nun diese Formel: =UND(F1;F2). Naturgemäß ist das Ergebnis auch ein WAHR oder FALSCH, welches aussagt, ob es sich bei dem Datum in A1 um einen Arbeitstag handelt oder nicht. Und in F4 noch einmal als „sprechendes” Ergebnis:
="Der " & TEXT(A1; "TT.MM.JJJJ") & " ist " & WENN(UND(F1; F2); "ein"; "kein") & " Arbeitstag."
Nun ist das Mit den Hilfs-Zellen nicht immer der Hit.Und natürlich lassen sich die beiden Formeln so zusammenfassen, dass eine einzige, wenn auch komplexere (sprich: längere) Formel in der Eingabezeile steht und in der entsprechenden Zelle das Ergebnis ausgibt:
=WENN(UND(WOCHENTAG(A1; 2)<6; ISTFEHLER(SVERWEIS(A1; Feiertage; 1; FALSCH))); ""; "Kein ") & "Arbeitstag"
Sinnvollerweise wird im Endeffekt auf einen Arbeitstag geprüft, weil ja verschiedene Parameter wahr oder falsch sein können. Probieren Sie es aus, es haut hin. 🙂
Bleibt als drittes Prüfelement noch der Werksurlaub, die Betriebsferien. Hier muss geprüft werden, ob Datum innerhalb eines Zeitraums liegt oder nicht. Zugegeben, das geht mit der klassischen Methode auch ganz gut und übersichtlich: Sie prüfen, ob Datum >= erster Tag des Urlaubs und <= letzter Ferientag ist. Wenn ja, dann ist an dem Tag das Werk geschlossen. Es geht aber auch um einiges eleganter.
Zur Vorbereitung haben wir für das aktuelle und das Folgejahr jeweils vier Urlaubswochen Werksferien eingetragen. Und beide Jahre haben auch einen Bereichsnamen verliehen bekommen: BFJahr1 und BFJahr2. Dass „BF für Betriebsferien steht haben Sie vielleicht schon erkannt. Die wirklich grandiose Formel, welche wir dort in Spalte A eingesetzt haben, ist von Franz Pölt / Exelformeln.de. Damit wird immer der Montag einer definierten Kalenderwoche eines bestimmten Jahres berechnet.
Wiederum mit einem Wahrheitswert als Rückgabe sollten Sie für das laufende Jahr folgende, ganz kurz gehaltene Formel verwenden: =MEDIAN(BFJahr1;A1)=A1. Ersparen Sie uns die detaillierte Erklärung, die Excel-Hilfe (F1) sowie das Internet können hier hilfreich sein.
Meist wird die Abfrage auf 1 Jahr reichen. Wollen Sie jedoch Datum für das aktuelle und das Folgejahr mit den Betriebsferien vergleichen, dann könnte die Formel so lauten:
=ODER(MEDIAN(BFJahr1; A1)=A1; MEDIAN(BFJahr2; A1)=A1)
Die ODER()-Formulierung für alle drei Bedingungen sollten Sie nun in Eigeninitiative fertig bekommen. Sie ist einfach nur eine Erweiterung der Zweierkombination, welche Sie bereits kennen. Im Einzelfall werden wir auch bereit sein, Ihnen zu helfen.
Zeit
Angenommen, ein Fehlerprotokoll soll ausgewertet werden. Es wird zu jedem Vorfall die Uhrzeit ausgegeben. In der Übersicht soll nun ausgegeben werden, ob es nun die Früh- die Spät- oder die Nachtschicht gewesen ist. – Das geht natürlich auch über die althergebrachte Methodik mit einer extrem langen WENN() und ODER()-Kombination. Aber was gerade eben bei den kalendarischen Daten geklappt hat, das geht genau so gut mit Zeiten. Die MEDIAN()-Funktion greift auch hier. Und noch eines: In A1 steht nun kein Datum sondern die zu vergleichende Zeit.
Natürlich ist auch jede der Schichten mit einem Bereichsnamen versehen. Dabei ist „einem” nicht so ganz korrekt. Die Nachtschicht geht ja über Mitternacht hinaus. Für und Menschen ist zwar klar, dass ein Zeitraum von beispielsweise 23:00 Uhr bis 02:00 Uhr drei Stunden sind, aber Excel kann das nicht so ohne weiteres erkennen. darum ist die Nachtschicht hier auch in zwei Parts unterteilt und hat entsprechend auch zwei Bereichsnamen. Bei der Gelegenheit: Schauen Sie sich gerne einmal die Formeln an. Sie sind nicht überwältigend aber ganz pragmatisch. – Um auf die Frühschicht zu prüfen, geben Sie diese Formel ein:
=MEDIAN(Frühschicht;A1)=A1
Die Logik ist die gleiche, wie beim Betriebsurlaub. Nur dass hier die beiden Eckwerte untereinander angeordnet sind. Und das Ergebnis stimmt auch sekundengenau. Apropos Sekunden: Sie können die Formatierung der Zeiten gerne dahingehend ändern, dass nur hh:mm angezeigt werden; aber nur die Formatierung, die Inhalte der Zellen müssen (natürlich) erhalten bleiben.
Für die Spätschicht gilt das eben gesagte zu praktisch 100%; die einzige Ausnahme ist naturgemäß, dass die Spätschicht abgefragt wird. Und auch die Nachtschicht wird so verglichen. Nur dass es hier zwei Zeitrahmen gibt, einen bis und einen ab Mitternacht. Nun gilt es nur noch, das Ganze in eine WENN()-Funktion zu packen:
=WENN(MEDIAN(Frühschicht; A1)=A1; "Frühschicht"; WENN(MEDIAN(Spätschicht; A1)=A1; "Spätschicht"; "Nachtschicht"))
Gewiss wird Ihnen aufgefallen sein, dass gar nicht explizit auf „Nachtschicht” abgefragt worden ist und somit der zweite Block in der Nachtschicht eigentlich nicht erforderlich ist. Unser Kommentar dazu: „Jein” ;-). In diesem Fall hätten wir die Zeiten für die Nachtschicht gar nicht angeben müssen. Aber nehmen Sie doch einfach einmal an, dass nur geprüft werden soll, ob eine gegebene Zeit in die Nachtschicht fällt oder nicht. Als Beispiel sei die Zeit 6:45 (wiederum in A1). Dann wäre die Abfrage auf <7:00 korrekt und zielführend. Als nächstes steht dort aber 23:44 als Uhrzeit drin. Und das ist keineswegs kleiner als 7:00 Uhr. Darum bietet sich hier folgende Formel an:
=ODER(MEDIAN(Nacht1;A1)=A1;MEDIAN(Nacht2;A1)=A1)
womit ganz eindeutig die Nachtschicht identifiziert wird.