PQ: Nächtliche Arbeitszeit auf kalendarische Daten splitten

Xtract: Arbeitszeiten, welche über Mitternacht hinaus gehen, sollen per Power Query um 00:00 gesplittet und entsprechend auf 2 Tage aufgeteilt werden.

  Wissensstand: Level 3 ⇒  Excel GUT!, mindestens Basis-Kenntnisse in Power Query   

In einem Excel-Forum wurde die Frage gestellt, wie eine Mitternacht überschreitende Arbeitszeit so auf die beiden entsprechenden Tage aufgeteilt werden kann, dass die Zeit bis Mitternacht zum Tag des Arbeitsbeginns berechnet und die restliche Zeit für den Folgetag ausgegeben wird. Was sich im ersten Moment ganz profan anhört ist in der Realisierung vielleicht doch etwas komplexer.

In diesem kleinen Experiment habe ich mich auf die Basics beschränkt. So werden gesetzliche Regelungen, dass beispielsweise nach 6 Stunden Arbeitszeit eine Pause eingelegt werden muss, nicht beachtet. Auch Wochenenden und Feiertage werden wie ganz normale Tage behandelt.

Der Ausgangspunkt für diese Übung ist diese kleine, gewiss selbsterklärende Tabelle:

Die Daten bitte von Hand eingeben… 😉

Am Beispiel des ersten Eintrags ergibt sich, dass auf den 22. Mai 2 Stunden entfallen (22:00 Uhr bis 24:00 Uhr) und für den 23. Mai sollen 3 Stunden in Anrechnung kommen, entspricht der Zeit von 00:00 Uhr bis 03:00 Uhr. Und da am Folgetag (nächste Zeile) auch (beispielsweise durch einen anderen Mitarbeiter oder durch eine andere Maschine) zu berechnende Stunden anfallen, sollen diese selbstredend im Endergebnis kumuliert werden. Hinweis: Ich weiß, dass Excel kein „24:00″ kennt, ist aber Sprachgebrauch und somit eindeutiger. 💡 

▲ nach oben …

Einstieg

Beginnen Sie damit, in ein leeres Arbeitsblatt die hierüber im Bild gezeigten Daten einzutragen. Anschließend formatieren Sie den Bereich als „Intelligente“ Tabelle (StrgT oder StrgL). Sie achten natürlich darauf, dass die Daten nicht als Text sondern als Datum bzw. Zeit eingegeben werden. Ich gebe dieser Tabelle auch gleich den „sprechenden“ Namen Arbeitszeiten, welcher dann auch automatisch beim anschließenden Import in den Power Query-Editor übernommen wird.

Ich stelle Ihnen hier eine von mehreren Möglichkeiten einer Vorgehensweise vor. Für mich steht im Vordergrund, dass eine Lösung auch durch den „typischen“ lernwilligen User nachvollziehbar ist und somit bei Bedarf auch selbstständig erweitert oder angepasst werden kann. Und ich muss nicht damit „angeben“, dass ich so schicke, kurze und wenig Schritte umfassende Lösung gefunden habe, die vielleicht wenige Sekundenbruchteile schneller ist als die von mir jeweils vorgestellte. Und User, die tief in der Excel- oder sogar Programmier-Welt verwurzelt sind, werden rasch die anderen Möglichkeiten finden und vielleicht nutzen.

▲ nach oben …

Die Berechnung

Mein erste Versuch einer Lösung hat zwar prima geklappt, war aber doch zu stark an die Programmierung wie in VBA angelehnt und ich habe mir überlegt, dass solch eine Lösung nicht unbedingt für meine Zielgruppe „Engagierte Excel-Anwender“ gut nachvollziehbar ist. Darum habe ich mit einigen Schritten (und 2 Abfragen) mehr einen deutlich besser verständliche Lösungsweg erarbeitet.

Folgende Überlegung ist für mein Vorgehen maßgeblich: Für jeden der aufgeführten kalendarischen Daten in der Spalte Datum muss eine weitere Zeile generiert werden, wo eventuelle „Überhangzeiten“ nach Mitternacht eingetragen werden; falls an dem Tag (der Zeile) nur zwischen 0:00 Uhr und 24:00 Uhr zu berechnende Zeiten angefallen sind, wird in der zweiten Datenzeile (die automatisch das Datum des Folgetages haben wird) der Wert 00:00 (in einem eventuell anderm Datenformat) eingetragen, denn es wurde ja nicht nahtlos an den in der 1. Spalte stehenden Tag gearbeitet.

Sie haben nach dem Import die typische und für PQ-Einsteiger etwas gewöhnungsbedürftig Ansicht, dass das Datum automatisch vom Datentyp Datum und Zeit dargestellt wird und die beiden Spalten von und bis, welche in plain Excel deutlich als Uhrzeit formatiert sind, haben hier das Datenformat Dezimalzahl. Wenn es sie nicht stört, könnten (!) Sie es hierbei belassen. Ich persönlich ziehe es aus verschiedenen Gründen vor, die Spalte Datum als (nur) Datum zu formatieren. Und wenn Sie eher visuell als programmtechnisch orientiert sind empfehle ich Ihnen, die beiden Uhrzeit-Spalten auch als Zeit zu formatieren. Und genau darauf bauen auch die weiteren Schritte und vor allen Dingen die Formeln dieses kleinen Seminars auf. Auch wenn es wenige Millisekunden längere Laufzeit des Programms bedeutet.

Nun stellt sich gewiss die Frage, wie mit gut nachvollziehbaren Schritten und ohne große programmtechnische Eingriffe jede der Zeilen 1 bis Zeile 4 in jeweils zwei Zeilen aufgeteilt werden kann also für den aktuellen und den Folgetag. Ich biete Ihnen dazu dieses Vorgehen an:

  • Duplizieren Sie die bislang einzige Abfrage Arbeitszeiten zwei mal.
  • Benennen Sie die Query Arbeitszeiten (2) zu Tag 1 um.
  • Benennen Sie die Query Arbeitszeiten (3) zu Tag 2 um.
  • Wechseln Sie zu Abfrage Tag 1.
  • Menü Spalte hinzufügen | Benutzerdefinierte Spalte.
  • Vergeben Sie als Neuer Spaltenname von und bei Benutzerdefinierte Spaltenformel übernehmen Sie per Doppelklick aus dem Bereich Verfügbare Spalten den Wert von:

Die erste Benutzerdefinierte Spalte

  • Nun noch eine weitere neue Spalte mit dem Wert aus bis anlegen.

Spätestens jetzt wird Ihnen aufgefallen sein, dass die eben neu angelegten Spalten bei den Überschriften eine Änderung erfahren haben, es wurde automatisch .1 hinzugefügt. Und im nächsten Schritt werden Sie eine weitere Benutzerdefinierte Spalte hinzufügen, wo die Berechnung der Differenz bis zum Tagesende oder -falls Mitternacht nicht überschritten wird- der Ende-Zeit des gleichen Tages erfolgen soll. In plain Excel würde die Formel (in der DE-Version) so aussehen:
=WENN([@bis]>=[@von];[@bis]-[@von];1-[@von])
…und auch das korrekte Ergebnis bringen. Vom Prinzip her ließe sich diese Formel in angepasster Form auch hier verwenden. Und mit einigen Tricks und Umwegen würde das auch in PQ klappen. Aber ich möchte Ihnen einen anderen, im Endeffekt leichter verständlichen Weg aufzeigen; auch wenn er im ersten Anlauf nicht zum gewünschten Erfolg führt. Aber ich finde, dass Fehler dazu da sind, gemacht zu werden und daraus zu lernen.

Okay, duplizieren Sie erst einmal die Spalte von, indem Sie über das Menü Spalte hinzufügen eine Benutzerdefinierte Spalte erzeugen und als Neuer Spaltenname ebenfalls von vergeben. In den Bereich Benutzerdefinierte Spaltenformel fügen Sie per Doppelklick die Spalte von aus dem rechten Bereich ein. Nach einem OK werden Sie erkennen, dass die Überschrift der neuen Spalte automatisch auf von.1 angepasst wurde, da in eine Tabelle bzw. Abfrage jede Überschrift einmalig sein muss. Die gleich Vorgehensweise nun auch für die Spalte bis durchführen. Hinweis: Und ja, Sie hätten natürlich auch die beiden Spalten einfach duplizieren und dann umbenennen können, das wäre der gleiche Effekt gewesen. 😉

Markieren Sie nun zuerst die Spalte bis.1 und anschließend mit Strg oder Shift von.1. Immer noch im Menü Spalte hinzufügen klicken Sie auf Zeit | Subtrahieren, um die beiden Zeiten voneinander abzuziehen. Und das im vollen Bewusstsein, dass die Mitternacht-Grenze bei der Berechnung nicht beachtet wird, wie eigentlich gefordert. Aber so bekommen Sie einen kleinen Eindruck, was PQ in solchen Situationen macht:

Auch Zeiten können subtrahiert werden

Auf den allerersten Blick sieht das ja ganz vernünftig aus, beim genaueren Hinsehen wird Ihnen aber rasch dieses oder jenes auffallen: Die Datentypen sind unterschiedlich (Dauer/Duration und Zeit), Power Query kennt negative Zeiten (!) und PQ rechnet tatsächlich in der Reihenfolge, wie Sie die Spalten markiert haben. Ach ja, die berechneten Zeiten, wo Mitternacht nicht überschritten worden ist, stimmen sogar! Merke: Wenn beide Zeiten am gleichen Tag sind, ist das eine clevere Möglichkeit der Berechnung. Ein Ergebnis in der Duration-Formatierung ist für Power Query korrekt.

Nun ja, wie vorher schon angedeutet: Das Ergebnis ist ja nicht optimal, darum löschen Sie die Schritte, beginnend mit den Duplikaten der beiden Spalten von und bis. Ach ja, wenn Sie so faul sind wie ich 😎, dann löschen Sie stattdessen gleich zu Beginn die Abfrage Tag 1, duplizieren die Query Tag 2 und benenen diese gleich zu Tag 1 um. Dann haben Sie ebenfalls die gewünschte Ausgangslage.

Im Grunde genommen müssen Sie doch nur folgende Bedingung schaffen: Wenn bis kleiner ist als von dann muss in der Spalte bis.1 der Wert von 0:00 Uhr des Folgetages stehen, ansonsten reicht natürlich wie gehabt die Subtraktion der beiden Zeiten. Damit das ganze „sauber“ abläuft, werden Sie etwas tricky arbeiten. Sie sind immer noch im Menü Spalte hinzufügen. Markieren Sie erst die Spalte Datum und danach mit Shift oder Strg von. Zeit | Datum und Uhrzeit kombinieren. Die Überschrift ist Zusammengeführt und der Typ der Spalte ist Datum/Uhrzeit. Ändern Sie den Spaltennamen auf von.1.

Für die Spalte bis.1 gilt ja die Regel, dass bei Zeiten kleiner 24:00 Uhr (eigentlich 00:00h) der Wert aus bis übernommen wird und im anderen Fall 0:00 Uhr des kommenden Tages eingetragen werden muss. Dazu gehen Sie über Benutzerdefinierte Spalte, tragen bei Neuer Spaltenname den Wert bis.1 ein und im Bereich der Formel geben Sie folgenden Code ein:

if [bis]<[von] then
   Date.AddDays([Datum],1) & Time.From("0:00")
else
   [Datum] & [bis]

Damit erreichen Sie das besprochene Ziel. Bedenken Sie, dass sie die Schreibweise insbesondere in Bezug auf Groß- / Kleinschreibung exakt so übernehmen müssen. Die beiden verwendeten Funktionen Date.AddDays() und Time.From() sind in der Hilfe gut und verständlich dokumentiert. Wenn Sie nun die beiden Spalten mit den Datum/Zeit – Werten Subtrahieren, bekommen Sie die korrekten Werte für den ersten Tag der Zeile im Format Dauer/Duration. Die beiden Spalten Zusammengeführt und bis.1 können Sie nun löschen; wenn Sie stringent auf Daten-Sparsamkeit achten, dann brauchen Sie auch nur noch die Spalten Datum und Subtraktion zu belassen, was auch meiner Empfehlung entspricht.

Im Grunde genommen können Sie dieses Ergebnis in dieser Darstellungsweise so belassen. Speichern Sie gerne diesen Zwischenschritt in einem neuen Tabellenblatt und formatieren sie dann die Spalte  Subtraktion  im Zahlenformat Zeit und das Ergebnis könnte nicht besser aussehen. 😎 Sollten Sie jedoch auch im Power Query-Editor den Datentyp Zeit haben wollen, dann versuchen Sie gerne, diesen Typ direkt anzuwenden. Sie sehen, das führt zu einem Fehler. Aber mit einem kleinen Umweg ist das durchaus machbar: Wählen Sie als erstes für die Spalte Subtraktion den Datentyp Dezimalzahl und direkt im Anschluss den Datentyp Zeit. Nun sieht das auch so aus, wie sie es wahrscheinlich erwartet haben. 💡 

Ob Sie nun das Format Dauer oder Zeit in der Spalte stehen haben, spielt keine wesentliche Rolle; Sie sollten in jedem Falle die Spaltenüberschrift auf Arbeitszeit ändern. Damit ist die Abfrage Tag 1 fertig bearbeitet.

▲ nach oben …

Wechseln Sie zur Query Tag 2. Auch hier ist es naturgemäß wichtig festzustellen, ob die Arbeitszeit tagübergreifend ist oder nicht. Wenn die 00:00 – Grenze überschritten worden ist, ist es ja keinesfalls die Differenz-Zeit zwischen bis und von, denn das wurde bereits in der Query Tag 1 berechnet; es ist die von-Zeit 00:00 (des Folgetages) bis zum Wert in bis.1; ansonsten sind es natürlich 0:00 Stunden, die nach Mitternacht gearbeitet wurden. Der Weg zum Ziel ist hier (auch ohne den vorherigen, nur der Erkenntnis wegen gemachten Umweg) etwas kürzer. In einer neuen, benutzerdefinierten Spalte geben Sie gleich Arbeitszeit als Spaltenname und darunter beispielsweise diesen Code ein:

if [bis]<[von] then //über Mitternacht
   Duration.From([bis]-#time(0,0,0))
else Duration.From("0:00")

Wie auch in vielen anderen Sprachen der IT leiten die beiden // einen Kommentar ein, der so ausgelegt ist, dass die Zeile ab hier im Programm nicht ausgewetet, beachtet wird. Und ein Wort zur Funktion #time: Ja, Kleinschreibung! Und die 3 numerischen Werte (hier: Nullen) stehen für Stunde, Minute, Sekunde; also analog zur Excel-Funktion ZEIT().

Entsprechend der Spalten-Reduktion in der Query Tag 1 sollten Sie auch hier die Spalten „eindampfen“. Und noch ein Punkt ist wichtig: Die Spaltenüberschriften der beiden Tag #-Abfragen müssen absolut identisch sein! Und es ist mehr als hilfreich, wenn die Spalte Arbeitszeit in beiden Queries den gleichen Datentyp hat (ich bevorzuge Dauer/Duration).

▲ nach oben …

Endspurt

Ihnen liegen nun 3 Abfragen vor: Arbeitszeiten, Tag 1 und Tag 2. Um später ein unnötiges Aufblähen der Arbeitsmappe zu vermeiden, sollten Sie nun die Query Arbeitszeiten markieren und als Nur Verbindung speichern. Dadurch werden automatisch auch die restlichen Abfragen in diesem Modus gesichert.

Wechseln Sie in das Menü Start. Markieren Sie die Abfrage Tag 1 und Kombinieren | Abfragen anfügen ▼ | Abfragen als neu anfügen. Es bleibt bei Zwei Tabellen und als Zweite Tabelle wählen Sie Tag 2. Je nach endgültiger Zielsetzung können Sie nun a) die Spalte Datum aufsteigend sortieren und es dann dabei belassen oder b) die Spalte Datum so wie sie ist Gruppieren nach… und die Spalte Arbeitszeit direkt als Summe berechnen (aggregieren). So war es ja auch zu Beginn gefordert…

Da Sie gerade das Zusammenführen als Neue Abfrage durchgeführt haben genügt ein „ganz normales“ Datei | Schließen & laden, um das Ergebnis in ein neues Tabellenblatt zu schreiben. Eventuell noch als Zahlenformat Zeit darstellen und vielleicht noch an die gewünschte Position verschieben (sofern Sie nicht schon gleich per Schließen & laden in… die Wunschposition bestimmt haben). – Das wars dann auch!

Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00  freuen … (← Klick mich!)

Dieser Beitrag wurde unter Daten zusammenführen, Datentyp anpassen, Datum & Zeit, Datum und Zeit, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), Rechnen / Berechnungen, Stundenabrechnung, Zeit-Funktionen abgelegt und mit , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.