Schlagwort: Feiertage

Aller Unfug ist schwer

Excel rechnet falsch

Claudia kommt in der Pause zu mir. Zwei Stunden habe sie den Fehler gesucht und nicht gefunden. Ob ich ihr mit einer Formel helfen könne, die nicht richtig rechne:

Der Bereich A2:A13 trägt den Namen Feiertage_BY, B2:B10 heißt Feiertage_HE.

Darin befinden sich die bayrischen und die hessischen Feiertage.

Markus arbeitet in Bayern; Boris in Hessen.

Claudia möchte mit der Funktion NETTOARBEITSTAGE ausrechnen, wie viele Tage Markus und wie viele Boris arbeiten muss. Für Markus erhält sie die korrekte Zahl 248; Boris muss drei Tage länger arbeiten – eigentlich 251 Tage. Excel jedoch berechnet 253.

Ich kontrolliere die Namen, die Formatierungen, die Formel – alles ist korrekt. Ich sehe nach, ob alle Feiertage auf einen Wochentag fallen. Stimmt auch. Dann prüfe ich die Feiertage.

Haben Sie es gesehen? In der Liste Feiertage_HE befinden sich zwei Mal die Tage 07.04.2023 und 10.04.2023. Allerdings fehlen die Weihnachtsfeiertage. Wahrscheinlich ein Kopierfehler …

Und dann klappt es auch. Und Claudia ist glücklich.

Kommentar hinterlassen ,

Nur noch zwei Mal joggen, dann ist wieder Weihnachten.

Merkwürdige Formeln

Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:

„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.

Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.

Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.

Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:

Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.

Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.

Die Formel

=WENN(UND(ZÄHLENWENN($B$2:$P$13;A21)>0;WOCHENTAG(A21;2)<=5);"F";"")

prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):

Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:

Dazu verwende ich die Formel:

=WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A21;-WOCHENTAG(A21;3);1;7;1);"F")>0;"F";"")

Der Rest ist trivial, oder? Man sucht ein Datum in der ersten Datumsspalte und prüft, ob in der Wochenspalte ein „F“ steht oder nicht:

=WENN(SVERWEIS(H20;A21:C5501;3;FALSCH)="F";25%;0%)

Beispiel: der 01. Mai 2021 fiel auf einen Samstag, also ist für den 29. April kein Zuschlag erforderlich.

Pfingstmontag war in diesem Jahr am 24. Mai – also muss für den 26. Mai 2021 ein Zuschlag bezahlt werden.

Eine hübsche Knobelaufgabe – ich habe ein bisschen überlegen müssen.

4 Kommentare , ,

Mami – ist das Abendessen oder Dschungelprüfung?

Outlook nervt auch

In Outlook-Schulungen bin ich immer wieder verblüfft, was Teilnehmer gerne hätten. Oder umgekehrt: was Outlook nicht kann.

Ein Teilnehmer wollte seine Ordner farblich kennzeichnen. Oder mit Bildern versehen, damit er sie schneller findet.

Geht natürlich nicht. Geht auch nicht im Windows-Explorer. Auch wenn ich seinen Wunsch verstehe.

Ich verstehe auch folgenden Wunsch der Teilnehmerin: Wenn man im Outlook-Kalender Feiertage importiert, sind sie immer „frei“. Schön wäre es, wenn diese Feiertage für anderen Kollegen und Kolleginnen als gebucht gekennzeichnet wären.

Das kann man weder beim Import einstellen noch im Nachhinein mit einem Klick über die Listenansicht oder ähnliches umwandeln. Schade!

2 Kommentare , , , , ,

Realität ist was für Menschen, die Angst vor Einhörnern haben.

Zellformate

Ich weiß, dass ich nicht zwei Datenüberprüfungen verketten kann oder – wie bei der bedingten Formatierung – nacheinander anwenden kann. Trotzdem – es wäre doch schön, wenn der Anwender aus einer Liste bestimmte Werte eintragen darf (Liste) – jedoch nur, wenn kein Feiertag und kein Wochenende ist:

Datenüberprüfung / Liste

Datenüberprüfung / Liste

Die Antwort: Wenn Sie mit der Datenüberprüfung eine benutzerdefinierte (Formel) eingeben, dann funktioniert es. Beispielsweise so:

=UND(ZÄHLENWENN(Feiertage!$B$1:$J$20;DATUM($B$2;$C$2;D$5))=0;WOCHENTAG(D$5;2)<=5;ODER(D7=“U“;D7=“M“;D7=“S“;D7=“K“;D7=“TZ“))

Das Ergebnis:

geht doch!

geht doch!

Zur Erklärung:

ZÄHLENWENN überprüft die Anzahl der berechneten Datumsangaben

DATUM($B$2;$C$2;D$5)

auf dem Tabellenblatt Feiertage. Die Zahl muss 0 sein, das heißt, sie ist nicht vorhanden – also ist das Datum kein Feiertag.

WOCHENTAG ermittelt, ob es sich um einen Tag von 1 – 5, also um einen Tag von Montag bis Freitag handelt.

ODER(D7=“U“;D7=“M“ …

prüft, ob wirklich nur einer der Texte eingegeben wurde.

Da alle drei Bedingungen gleichzeitig erfüllt sein müssen, werden mit der Funktion UND verkettet.

Kommentar hinterlassen , , ,

Feiertage wären klasse

Merkwürdige Formeln

Vor ein paar Tagen erreichte mich folgende Anfrage:

Sehr geehrte Damen und Herren,

zu dem in Betreff genannten Thema haben wir noch eine Frage:  Die Videoanleitung  zum Erstellen eines Internationalen Kalenders konnten wir gut nutzen. In dieser Anleitung wird u.a. geschildert, wie man Feiertage durch eine bedingte Formatierung farblich hervorhebt. Ganz schick wäre es noch, wenn zu diesem farblich markierten Feiertage auch automatisch der Feiertagsname mit angezeigt werden könnte. Ein entsprechendes Tabellenblatt mit diesen Informationen wurde im Verlaufe der Anleitung angelegt. Bei festen Feiertagen wie Neujahr. 1. Mai etc, könnte man dies händisch lösen, doch bei variablen Feiertagen wie Ostern und Pfingsten etc. wäre es wünschenswert, wenn diese gleich automatisch mit angezeigt werden. Leider wurde in dieser Videoanleitung nicht darauf eingegangen. Mit welcher Funktion kann der Feiertagsname automatisch angezeigt werden? Danke schon vorab für Ihre Hilfe.

Mit freundlichen Grüßen / with best regards

#####

Zur Info: Ich habe einen Kalender erstellt, der – nach Änderung des Jahres die Feiertage farblich kennzeichnet. Das klappt mit der bedingten Formatierung und der Funktion ZÄHLENWENN gut und einfach. Die Feiertage (hier: die bayrischen) habe ich auf ein zweites Tabellenblatt ausgelagert.

Die Feiertagsliste

Die Feiertagsliste

Der Kalender

Der Kalender

Ich habe zirka eine halbe Stunde benötigt, damit die Feiertage angezeigt werden – eine hübsche kleine Fingerübung:

Das Ergebnis

Das Ergebnis

Wer knobelt mit? Den ersten Kalender könnt Ihr unter Kalender herunterladen.

Viel Spaß im neuen Jahr mit Excel.

Rene

2 Kommentare , , ,