In einem Kundenprojekt war es vor kurzem notwendig, in einer bestehenden Tabelle die Differenz zwischen zwei Datumsspalten in Tagen zu kalkulieren und dies ohne Wochenenden und gesetzlichen Feiertagen. Im aktuellen Beitrag führe ich Dich Schritt für Schritt zu Nettoarbeitstagen mit Power Query.
Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!
Die Problemstellung
Die Problemstellung ist simpel: Es soll die Anzahl an Tagen ermittelt werden, die zwischen dem Datum in Spalte Datum bis und Datum von liegen.
Ein einfaches Number.From( [Datum von] - [Datum von] ) + 1 könnte hier also die Lösung sein, wenn da nicht die beiden folgenden Zusatzbedingungen wären:
- Wochenenden sollen nicht berücksichtigt werden,
- Feiertage sollen ebenfalls nicht mit einberechnet werden.
Der Bedingung mit den Feiertagen soll hier besondere Aufmerksamkeit widerfahren, da gesetzliche Feiertage zwischen Bundesländern variieren. Schauen wir uns exemplarisch die Kalenderwoche 10 im Jahr 2019 an, so handelt es sich hierbei um eine gewöhnliche Kalenderwoche mit 5 Werktagen und 2 Tagen am Wochenende. Sofern man diese Woche jedoch für das Bundesland Berlin betrachtet, war hier der Freitag, 08.03.2019 ein gesetzlicher Feiertag: Nämlich der Frauentag. Wußtest Du, dass dieser in Deutschland ausschließlich in Berlin ein gesetzlicher Feiertag ist?!
Aus diesen Betrachtungen lässt sich also für die zu berechnenden Tage (die Nettoarbeitstage) eine simple Formel entwerfen…
Formel zur Berechnung der Nettoarbeitstage
Nettoarbeitstage = Endedatum - Anfangsdatum + 1 - Wochenendtage - Feiertage
Im weiteren Beitrag setze ich dies mit Power Query um…
Die Lösung
Ich arbeite im Wesentlichen mit List-Funktionen. Falls Du hierzu mehr wissen möchtest, habe ich darüber einen sehr detaillierten (englischsprachigen) Beitrag geschrieben. Ich beginne mit der Ermittlung aller Tage, die zwischen dem Datum von und dem Datum bis liegen.
Liste aller Tage
Um die tatsächlichen Tage, die zwischen diesen beiden Datumswerten liegen, als Datum (und nicht als Anzahl an Tagen) zu ermitteln, nutze ich die List.Dates()-Funktion, mit den folgenden Argumenten:
List.Dates([Datum von], Number.From([Datum bis] - [Datum von]) + 1, #duration(1,0,0,0))
[Datum von] fungiert hier als Startdatum. Das zweite Argument möchte wissen, wie viele Listen-Elemente, ausgehend vom Startdatum, hinzugefügt werden sollen. Dies lässt sich durch die Differenzkalkulation aus [Datum bis] - [Datum von] +1 ermitteln. Das dritte Argument möchte jetzt wissen, in welcher Einheit zusätzliche Listen-Elemente hinzugefügt werden sollen. #duration(1,0,0,0) fügt Tage hinzu, während #duration(0,1,0,0) Stunden, #duration(0,0,1,0) Minuten und #duration(0,0,0,1) Sekunden hinzufügen würde. Möchte ich hier beispielsweise die Liste in Zweitages-Sprüngen wachsen lassen, würde ich hier #duration(2,0,0,0) angeben. Auch Kombinationen aus Tag, Stunde, Minute und Sekunde sind möglich, wenn auch vermutlich selten sinnvoll: #duration(2,1,5,10)
Es ist an dieser Stelle wichtig, die tatsächlichen Tage als Datum zu ermitteln, damit diese Liste an ermittelten Tagen im Rahmen der nächsten Schritte sukzessive weiter eingegrenzt werden kann. Im Anschluss kann dann die Anzahl der übrig gebliebenen Tage gezählt werden. Nachdem ich nun die Tage zwischen Datum von und Datum bis ermittelt habe, grenze ich diese im nächsten Schritt auf Wochentage (also ohne Wochenenden) ein.
Das Zwischenergebnis sieht wie folgt aus:
Nachdem ich nun die Tage zwischen Datum von und Datum bis ermittelt habe, grenze ich diese im nächsten Schritt auf Wochentage (also ohne Wochenenden) ein.
Liste aller Tage abzüglich Wochenenden
Um eine Liste von Werten auf bestimmte Items einzuschränken, bietet sich die Nutzung der List-Select()-Funktion an. Die folgende Funktion schränkt die Liste Liste aller Tage als Datum dahingehend ein, dass nur Wochentage akzeptiert werden, für die die Funktion Date.DayOfWeek() einen Wert kleiner < 5 zurückgibt, es sich also um Wochentage handelt. Etwas verwirrend könnte hier für Dich sein, dass diese Funktion für den Sonntag nicht den Wert 7, sondern 6 zurückgibt, da der Montag mit dem Wert 0 startet und nicht mit dem Wert 1.
List.Select( [Liste aller Tage als Datum], each Date.DayOfWeek(_) < 5 )
Das Zwischenergebnis sieht wie folgt aus:
Was nun noch fehlt, ist die Berücksichtigung – oder besser Nicht-Berücksichtigung – gesetzlicher Feiertage.
Liste aller Tage abzüglich Wochenenden und Feiertage (des spezifizierten Bundeslandes)
Ich habe in einem meiner letzten Beiträge eine Funktion vorgestellt, die für ein übergebenes Kalenderjahr alle Feiertage je Bundesland in Deutschland als Tabelle zurückgibt. Für die vorliegende Problematik habe ich diese Funktion etwas angepasst.
Vorstellung der Funktion zur Ermittlung der Feiertage für ein spezielles Bundesland
Ich habe Anpassungen der Funktion in Bezug auf drei Gesichtspunkte vorgenommen:
- Der Funktion kann im ersten Parameter jetzt eine Liste von Jahren ( z. B.
{2019, 2020}) übergeben werden, so dass auch Feiertage in mehreren Jahren gleichzeitig abgerufen werden können. - Im zweiten Parameter muss nun ein Bundesland angegeben werden, für welches die Feiertage dann zurückgegeben werden. Die Auflistung der Bundesländer und ihrer notwendigen Schreibweise findest Du hier:
- baden-wuerttemberg,
- bayern,
- berlin,
- brandenburg,
- bremen,
- hamburg,
- hessen,
- mecklenburg-vorpommern,
- niedersachsen,
- nordrhein-westfalen,
- rheinland-pfalz,
- saarland,
- sachsen,
- sachsen-anhalt,
- schleswig-holstein,
- thueringen
- Die Funktion liefert jetzt keine Tabelle mehr zurück, sondern eine Liste, die die Feiertage als Datum beinhaltet.
Der Quell-Code meiner Funktion ist dieser:
Falls Du Dich fragst, wie Du diesen Code für Dich nutzbar machen kannst, schau hier vorbei.
Angewendet sieht die Funktion wie folgt aus:
Nutzung der Funktion fnGetDeutscheFeiertageAuswahlBundesland()
In einem nächsten Schritt möchte ich die Liste der Datumswerte, bereinigt um die Wochenenden nun auch von den Feiertagen befreien. Hierfür wähle ich die Funktion List.RemoveMatchingItems(). Das erste Argument der Funktion ist die Liste, die um übereinstimmende Elemente aus der zweiten Liste bereinigt werden soll. In meinem Beispiel ist dies die Liste Liste ohne Wochenenden. Das zweite Argument muss eine Liste der Feiertage sein. Um diese zu erhalten, rufe ich an dieser Stelle meine eigens dafür erzeugte Funktion fnGetDeutscheFeiertageAuswahlBundesland() auf. Wie bereits erwähnt, kann ich hier im ersten Argument eine Liste von Jahren übergeben, für die die Feiertage ermittelt werden sollen. Im zweiten Argument übergebe ich das Bundesland (in meinem Beispiel „berlin“), für welches die gesetzlichen Feiertage ermittelt werden sollen. In Summe stellt sich das Ganze dann wie folgt dar:
List.RemoveMatchingItems([Liste ohne Wochenenden], fnGetDeutscheFeiertageAuswahlBundesland({2019, 2020}, "berlin"))
Das Zwischenergebnis sieht wie folgt aus:
Fehlt nur noch, die sich nun in der Liste von Datumswerten befindlichen Tage auch zu zählen.
Anzahl aller Tage der finalen Liste zählen
Um die Anzahl von Items einer Liste zu ermitteln, bietet sich die Funktion List.Count() an.
List.Count([Liste ohne Feiertage])
Das Ergebnis ist dann die Anzahl von Datumswerten in meiner Liste und zugleich die Lösung meiner Problematik:
Schlussbetrachtung
All dies kann natürlich kürzer gefasst werden, in weniger Schritten, und im Idealfall als Funktion gekapselt. Ich habe es jedoch aus didaktischen Gründen ausführlicher beschrieben. Falls Dich eine Lösung auf Basis einer benutzerdefinierten Funktion interessiert, kann ich Dich vertrauensvoll an Imke verweisen, die dies in ihrem Beitrag – wie gewohnt exzellent – gelöst hat 🙂
Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de
Viele Grüße aus Hamburg,
Lars
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
Melina meint
Hallo Lars,
danke für deine Erklärung. Das hat mir bereits sehr gut weitergeholfen. Ich stehe allerdings vor zwei Problemen, bei denen ich ein Error zurückbekomme
1. Das Enddatum liegt vor dem Startdatum, z.B. Startdatum = 21.06.2023, Enddatum = 21.04.2023. Ich würde hier im Ergebnis eigentliche einen negativen Wert erwarten.
2. Die Spalte mit dem Enddatum kann auch keinen Wert enthalten. Aktuell steht hier „null“ drin. Ich möchte allerdings, dass in diesem Fall einfach ein bestimmter Wert zurückgegeben wird, nämlich 0. Ich dachte an sowas wie if then else
Hast du eine Idee? Vielen Dank im Voraus.
Justus meint
Hallo Lars,
vielen Dank für deine großartigen Videos und Blogeinträge. Ich bin gerade dabei mir die Nettoarbeitstage anzeigen zu lassen. Die Eingabe für die Jahre und das Bundesland für die Feiertage soll dabei dynamisch sein. Sprich, ich habe in einem Excelblatt 2 Tabellen. In einem wird per Dropdown das Bundesland ausgewählt und in der anderen wird ein oder mehrere Kalenderjahre reingeschrieben (z.B. 2020,2021).
Wie muss ich deine Funktion abändern, um auch die Bundesländer als Spalte auswählen zu können? Ich habe die Zeile:
„Bundesland as (type text meta [Documentation.AllowedValues = {„baden-wuerttemberg“, „bayern“, „berlin“, „brandenburg“, „bremen“, „hamburg“, „hessen“, „mecklenburg-vorpommern“, „niedersachsen“, „nordrhein-westfalen“, „rheinland-pfalz“, „saarland“, „sachsen“, „sachsen-anhalt“, „schleswig-holstein“, „thueringen“}]) )“
schon in:
„Bundesland as (type list)“
umgewandelt und kann dann auch eine Spalte auswählen, allerdings bekomme ich dann folgende Fehlermeldung: „Expression.Error: Ein Wert vom Typ „List“ kann nicht in den Typ „Text“ konvertiert werden.
Details:
Value=[List]
Type=[Type]“
Wo liegt mein Fehler?
Vielen Dank!
Magdalena Brauer meint
Hallo Lars,
ich finde Deine Beiträge super und die haben mir schon weiter geholfen. Jetzt stehe ich aber vor einem Problem, das ich leider alleine nicht lösen kann. Ich habe eine Liste mit Startdatum, Entdatum und Bundesland je Mitarbeiter, also alle Bundesländer vorhanden. Ich habe ebenfalls eine Tabelle mit den Feiertagen je Bundesland. Ich möchte gerne die Anzahl der Arbeitstage zwischen den Tagen unter Berücksichtigung des Bundeslandes berechnen. Gibt es in Power Query eine Möglichkeit, ähnlich wie bei Excel zB. vergleiche Bundesland in der Mitarbeiterliste mit dem Bundesland Feiertage und zähle dann die Feiertage nicht!?
Besten Dank und viele Grüße
Magdalena
Lars Schreiber meint
Hallo Magdalena,
in diesem Video stelle ich meine Funktion zur Ermittlung von Arbeitstagen vor… je Bundesland. Ich denke, das könnte Dir helfen: https://www.youtube.com/watch?v=45TjgCtlT0k. Generell klingt das für mich eher nach einem Power BI-Datenmodell, als nach einer reinen Power Query-Lösung, aber meine Funktion sollte Dir in beiden Fällen helfen können.
LG,
Lars
Daniel Becker meint
Hi Lars,
vielen Dank für die sehr hilfreiche Erklärung der List.Dates Funktion.
Ich habe damit Listen bezogen auf Projektzeiträume erstellt um Budgets zu verteilen.
Gibt es einen einfachen Weg die Listen auf Monatserste zu beschränken, damit die erweiterte Tabelle nicht so riesig wird? Ich bekomme es mit vorangestelltem List.Select noch nicht hin.
Danke im Voraus und viel Erfolg weiterhin mit deinem YT-Kanal! 🙂
Gruß, Daniel
Lars Schreiber meint
Hallo Daniel,
der folgende Code sollte Dir helfen können… Der erste Schritt beinhaltet eine Liste von Datumwerten. Der zweite Schritt ist das, was für Dich interessant sein sollte:
let
Quelle = List.Transform( {Number.From(#date(2020,1,1))..Number.From(#date(2022,12,31))}, each Date.From(_)),
ListeVonMonatsErsten = List.Select(Quelle, each _ = Date.StartOfMonth(_))
in
ListeVonMonatsErsten
Danke für Deinen Kommentar und viele Grüße aus Hamburg,
Lars
Daniel Becker meint
Klasse, das funktioniert genau wie gewünscht!
Hier der für meinen konkreten Fall angepasste Code, falls jemand vor einer ähnlichen Herausforderung steht 😉
= Table.AddColumn(#“Geänderter Typ“, „ListeVonMonatsErsten“,
each List.Select(List.Dates([#“Projektstart“], Duration.Days([#“Projektende“] – [#“Projektstart“]) + 1, #duration(1,0,0,0)),
each _= Date.StartOfMonth(_)))
Viele Grüße aus Aachen,
Daniel
Thomas Reick meint
Wie kann ich denn aus einer Anzahl von Nettowerktagen (Mo-Sa) einen Rückschluss auf die davon abgedeckten Monate erreichen?
Situation: Es existieren Bestell und Lieferdaten. Als zusätzliche Komplikation hat hat der Lieferant allerdings Bedingungen, welche Anzahl von Werktagen er zur Erfüllung der Bestellung benötigt. Gegliedert sind diese nach leicht, mittel schwer. und sind zischen 36 und 116 Tagen.
Die Nettotage einer Überziehung = (Lieferdatum-Bestelldatum)-Erfüllungstage müsste ich in Monaten erhalten um Schadenersatz für entgangene Monatsentgelte berechnen zu können.
Gibt es da einen Trick zu, der auf der o.g. Anzahl der überzogenen Tage beruht?
Frank Book meint
Hallo, bin Neuling was dieses Tehma angeht. Lerne schnell und viel. Unter anderen durch Leuten wir Dir. Vielen lieben Dank dafür.
Toll geschriebener Artikel.
Aber leider sind mir meine Grenzen aufgezeigt worden.
Ich habe eine Excel PowerQuery Abfrage. Genauer gesagt sind da schon viele Anfragen drin. z.B. eine Datumstabelle und vor allem eben die Datentabelle.
Wenn ich nach Deiner Anleitung gehe, (die ersten beiden Schritte klappen gut und bringen das richtige Ergebnis) bekomme ich beim „rauslöschen“ der Feiertage foglende Fehlermeldung:
Formula.Firewall: Abfrage ‚Daten‘ (Schritt ‚Hinzugefügte benutzerdefinierte Spalte2‘) verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenquelle zugreifen. Erstellen Sie diese Datenkombination neu.
In der Abfrage werden bereits einiges an Schritten gemacht Bevor ich hierzu komme.
Hast Du so eine Ahnung was ich falsch mache?
Danke im voraus.
Gruß
Frank
Lars Schreiber meint
Hallo Frank,
mir fehlt gerade die Zeit, Deinen persönlichen Fehler zu ergründen, aber ich kann Dir empfehlen, meine neue Funktion für die Arbeitstage zu nutzen. Den Link zum Artikel + Video findest Du hier: https://ssbi-blog.de/blog/business-topics/arbeitstage-fuer-deutschland-oesterreich-und-luxemburg-ermitteln-mit-power-query/
Danke und LG,
Lars
Tali meint
Hi Lars
Vielen Dank für deinen Artikel, das ist genau die Lösung die ich gesucht habe. 🙂
Allerdings habe ich doch noch eine Frage dazu.
Ich habe eine separate Liste, einen Arbeitszeitkallender in dem ich die Daten mit einer Treu/False spalte ergänzt habe in der ich nicht nur die Feiertage sondern auch unsere Sommerferien eingetragen habe. Also alle Daten des Jahres und daneben ob gearbeitet wird oder nicht. Gibt es auch die Möglichkeit diese Liste einzupflegen?
Und wie kann ich in meinem Dashboard das Element nach einem Bestimmten Datum filtern?
Muss ich die List bei der Spalte «Liste ohne Feiertage» erweitern oder kann ich auch direkt auf die beinhalteten Daten zugreifen?
Lars Schreiber meint
Hi Tali,
Deine Liste mit dem true/false-Statement, ob gearbeitet wird oder nicht, kannst Du auf Basis des Datums in die Kalendertabelle joinen, oder habe ich Deine Szenario falsch verstanden. Das sollt leicht möglich sein. Das Filtern in Deinem Dashboard sollte dann auf Basis Deiner Kalendertabelle stattfinden. Z. B. auf Basis des Monats?! Die letzte Frage verstehe ich leider nicht. Kannst Du präzisieren?
Danke und viele Grüße,
Lars