Zeitdifferenz mit Power Query auch über Mitternacht hinweg berechnen
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Von Excel kennen Sie es gewiss, dass die Zeit-Berechnung von beispielsweise Nachtschichten etwas mehr Umstand bedarf, wenn die Möglichkeit besteht, dass das Arbeitsende nach Mitternacht liegt. Und Power Query hat ja sowieso seine eigene Vorgehensweise, was die Berechnung von Zeiten betrifft … 💡
Anhand eines kleinen Beispiels will ich Ihnen in diesem Beitrag einen und in zwei weiteren $$-Beiträgen weitere verschiedene Wege aufzeigen, die zum Ziel führen. Beginnen Sie doch einfach einmal damit, eine Mini-Exceltabelle zu erstellen: Beginn 8:45, Ende 17:20. Idealerweise sind das Überschriften in Zeile 1 und Werte in der Zeile darunter. In Excel berechnet kommen Sie rasch auf 8:35, wenn Sie Ende-Beginn berechnen. Löschen Sie nun das berechnete Ergebnis und importieren Sie die 4 Zellen in Power Query. Nachdem Sie den Datentyp auf Zeit angepasst und beide Spalten markiert haben, einfach im Register Spalte hinzufügen Gruppe Aus Datum und Zeit auf das Symbol Zeit Klicken und Subtrahieren auswählen. Und das Ergebnis sieht auf den allerersten Blick ganz gut aus:
Zeiten subtrahieren geht (eigentlich) recht einfach
Auf den zweiten Blick wird Sie gewiss die Zeichenfolge 0. vor der eigentlichen Uhrzeit irritieren. OK, es sind 0 Tage, aber das will Excel ja gar nicht wissen (und Sie gewiss auch nicht), Power Query aber offensichtlich sehr wohl. 😉 Und versuchen Sie nun einmal, den Datentyp dieser Spalte in Zeit umzuwandeln …
Des Rätsels Lösung: Löschen Sie im rechten Seitenfenster den zweiten, automatisch eingefügten Schritt Geänderter Typ und schon stimmt das Ergebnis. Anschließend können Sie noch (bzw. wieder) die beiden ersten Spalten als Zeit formatieren und alles ist gut.
Jetzt Schließen & laden, um den Power Query-Editor zu schließen und wieder zum Arbeitsblatt zu gelangen. Fügen Sie nun gerne noch eine weitere Zeile in die Excel-Tabelle ein, wo die Ende-Zeit nach Mitternacht liegt und rufen PQ erneut auf. Und es wird Sie nicht wirklich überraschen, dass Sie wieder einmal auf einen Fehler gestoßen sind. Aber löschen Sie nun die letzte Zeile, den letzten Arbeitsschritt im rechten Seitenfenster und siehe da, der Fehler ist raus.
Gemäß der Excel-Weisheit, dass bei Mitternacht überschreitenden Zeitberechnungen 1 Tag (also 1) addiert werden muss, generieren Sie eine weitere Spalte. Spalte hinzufügen | Bedingte Spalte und tragen Sie die hier gezeigten Werte ein:
Der ausgefüllte Dialog für Entscheidungen
Ich belasse es aus einer Faulheit die dem vorgegebenen Inhalt für Neuer Spaltenname, denn diese Spalte wird sowieso nach wenigen Schritten gelöscht. Jetzt markieren Sie Subtraktion und Benutzerdefiniert und anschließend Benutzerdefinierte Spalte und als Neuer Spaltenname vergeben Sie beispielsweise Differenz-Zeit. Die Formel ist wirklich nichts Besonderes:
Die Formel: [Subtraktion]+[Benutzerdefiniert]
Bleibt jetzt noch, die beiden Spalten Subtraktion und Benutzerdefiniert zu löschen und die verbliebenen Spalte als Zeit zu formatieren. Passt! 😉
Ich räume ein, dass es ein wenig aber auch deutlich „eleganter” geht. In Foren antworte ich manchmal bei einer Nachfrage, ob eine Lösung auch „eleganter” zu erreichen sei, dass Eleganz auf den Laufsteg gehört und wenn die Lösung auch in zeitlich sinnvollen Rahmen problemlos läuft, kein Grund zu einer Änderung besteht. Das sehe ich dann als mehr oder weniger deutlichen Hinweis auf die weit verbreitete Nur-Nehmer-Mentalität in diversen Excel-Foren, wo die Ansprüche verschiedener Fragesteller in zunehmendem Maße wachsen (im Netz ist ja doch alles umsonst und Forenhelfer sind dazu da, die eigenen Aufgaben zum Nulltarif zu lösen).
Und ja, ich selbst hätte die obige Aufgabe etwas „straffer” gelöst und wäre mehr über Benutzerdefinierte Spalte und den entsprechenden Formeln gegangen. Aber hier ist eine solide Lösung zum Nulltarif vorgestellt worden, die durchaus ihren Dienst erfüllt.
Als Einstieg in den weiterführenden Teil laden Sie diese Datei herunter. Es sind nur 3 Datensätze, aber es geht ja auch in erster Linie um das Prinzip, um den Weg. Was neu ist: die Spalte Pause. Probieren Sie Ihr Wissen gerne auch an dieser Tabelle aus. Sie werden höchstwahrscheinlich an einer Stelle auf einen Fehler stoßen, aber mit etwas Überlegung sollten Sie diesen beseitigen können.
Genau diese Datei wird auch die Basis für den zweiten (und dritten) Teil sein. Wenn Sie weitere Wege der Lösung kennenlernen wollen, dann finden Sie dort in bekannter Form entsprechende klare Beschreibungen der Wege zum Ziel. Beim Aufruf des Links werden Sie erkennen, dass der Titel mit $$ beginnt. Das bedeutet, dass ich eine kleine Spende von Ihnen erwarte, wenn Sie das Passwort für und damit den Zugriff auf den Beitrag erhalten möchten. Hier können Sie einige Erklärungen zum „warum” und zum Prozedere/Ablauf nachlesen (das Passwort ist $-loesung). Nutzen Sie den DONATE-Button oben rechts auf jeder dieser Seiten oder überweisen Sie mir einen Betrag ≥ 5,00 € mit dem Vermerk $$ PQ Zeitdifferenz 0:00 Uhr (1) oder $$ PQ Zeitdifferenz 0:00 Uhr (2) für die zweite $$-Lösung. Eine E‑Mail ist vorteilhaft, damit ich die Spende zuordnen und Ihnen das jeweilige Passwort senden kann.
Damit Sie sich einen ersten Eindruck über die erste $$-Lösung zu diesem „Problemfall” verschaffen können, habe ich eine Leseprobe/Preview erstellt. Und es ist gewiss verständlich, dass ich am entscheidenden Punkt die Schere angesetzt habe … 😳
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits (z.B. 2,00€) freuen …