$$ Arbeitszeit über 0:00 Uhr hinaus mit PQ berechnen (1)

Zeiten mit PQ über Mitternacht hinaus ausrechnen, Standard-Möglichkeit

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

In diesem zweiten Teil der Zeitberechnung mittels Power Query über die Mitternachtsgrenze hinaus werden sie einen weiteren Weg kennenlernen, das Ergebnis durch eine etwas “straffere“ Form zu realisieren. Und dazu laden Sie diese kleine Datei von unserem Server herunter und importieren die Tabelle in den Power Query-Editor.

Im ersten Schritt werden Sie den drei Spalten den Datentyp Zeit statt der automatisch zugewiesenen Dezimalzahl zuweisen. Ich selbst habe mir angewöhnt, bereits an dieser Stelle die Abfrage über den Weg Schließen & laden in… zu speichern, damit im Eifer des Gefechts keine unerwünschten Tabellen in Excel gespeichert werden. Das lässt sich zwar auch später wieder korrigieren (siehe hier), aber so ist es aus meiner Sicht einfacher. Öffnen Sie nun gleich wieder die Abfrage und erstellen entweder ein Duplikat oder einen Verweis, damit die importierte Abfrage mit den Original-Daten unverändert bleibt.

Erstellen Sie in dieser (kopierten) Abfrage erst einmal eine neue Benutzerdefinierte Spalte, vergeben als Neuer Spaltenname die Bezeichnung Arbeitszeit und tragen anschließend dort die auf den ersten Blick durchaus logische Formel ein:

Von der Idee her ist die Formel richtig, aber …

Das Ergebnis ein Mal null und zwei Mal Error ist nicht wirklich überzeugend. Das Ergebnis 0 kann offensichtlich nur durch den Wert in der Spalte Pause erzeugt worden sein. Aus dem Grunde werden Sie im nächsten Schritt die ersten 3 Spalten markieren (mindestes aber Pause) und ersetzen Sie den Wert null durch die Ziffer/Zahl 0.

Und wieder einmal geht etwas nicht wie gewollt und gewiss auch erwartet. Wenn Sie Erfahrung mit Hochsprachen wie beispielsweise C++, Java, etc. haben dann wissen Sie höchstwahrscheinlich, wie sorgfältig Sie in Sachen „unterschiedliche Datentypen“ vorgehen müssen. Sie hatten der Spalte Pause das Datenformat: Zeit zugewiesen und die Zahl Null ist nun einmal ein Zahlen- und kein Zeitformat. 💡 

Machen Sie den Arbeitsschritt des Ersetzens rückgängig. Wiederholen Sie den Ersetzen-Vorgang an exakt der Seitenfenster-Position (Angewendete Schritte) noch einmal, geben Sie aber dieses Mal als zu ersetzenden Wert den Text 0:00 ein, also eine Uhrzeit. Und ja, jetzt ist der Wert null aus der Spalte Arbeitszeit verschwunden und konsequenterweise steht auch hier jetzt Error. Steht immer noch der Wert null in der Spalte? Dann ist in dem Seitenfenster die Zeile mit dem Ersetzen gewiss an letzter Stelle „gelandet“. In dem Fall ziehen Sie einfach diese Zeile auf die Position direkt über Hinzugefügte benutzerdefinierte… und wenn Sie dann auf die letzte Zeile bei Angewendete Schritte klicken, ist erst einmal alles so, wie es sein soll.

Und um diese Fehlerursache etwas einzugrenzen klicken Sie in eine der Zellen mit dem Fehlerwert. Prinzipiell ist die erste Zeile mit der Beschreibung der Fehlerursache doch recht eindeutig:

Die Fehlermeldung, nachdem Sie in eine Zelle mit der Error-Wert geklickt haben

Übersetzt bedeutet das: Die einzelnen Komponenten (Argumente) der Formel konnten in der Berechnung nicht in den Datentyp Duration (Dauer) umgewandelt werden und dann folgt der Hinweis, dass Daten des Typs Duration nicht subtrahiert werden können. Für Sie bedeutet das, dass die Formel vom Grundsatz her zwar richtig ist jedoch umformuliert bzw. ergänzt werden muss, damit eine Berechnung ohne Fehlermeldung erfolgt. In der folgenden Abbildung erkennen Sie, wie die Formel nach der Korrektur aussieht und wie sich das Ergebnis anschließend darstellt:

Keine Fehler mehr sichtbar, aber das Ergebnis ist eine Dezimalzahl

Sie können die Formel direkt in dem hier oben gezeigten (und erweiterten) Editierbereich ändern oder aber die entsprechende Zeile per Doppelklick im rechten Seitenfenster öffnen und anschließend im Dialog die Anpassungen vornehmen. Ich ziehe übrigens trotz des etwas längeren Weges meist die Dialog-Variante vor. 😉

Ganz offensichtlich sind die Ergebnisse ja Dezimalzahlen, also die Darstellung als Industriezeit. Aber genauso offensichtlich ist, dass in der ersten und der dritten Zeile der Spalte Arbeitszeit dieser Wert negativ ist. Und nach Adam Riese kann kein Minus-Wert dabei herauskommen, wenn ich von abends 21:25 Uhr bis Mitternacht arbeite (Zeile 1). Und wie schon im ersten Teil beschrieben muss bei Uhrzeiten, welche die Mitternachtsgrenze überschreiten (oder erreichen) der Wert 1 (also ein Tag) hinzugerechnet, addiert werden. Ergänzen Sie die Formel für die Berechnung der Arbeitszeit nun um diesen Passus:

+ (if Number.From([Ende]) < Number.From([Start]) then 1 else 0)
So dass die komplette Formel sich so im Dialogfenster darstellt:

= Number.From([Ende])-Number.From([Start])-Number.From([Pause]) 
+ (if Number.From([Ende]) < Number.From([Start]) then 1 else 0)

Der Erfolg ist klar erkennbar und sofort gegeben. Die berechneten Werte sind alle positiv. Wenn sie der Spalte Arbeit nun den Datentyp: Zeit zuweisen, werden Ihnen in die korrekt berechneten Arbeitszeiten angezeigt. Damit ist die Aufgabe ohne jegliche Hilfsspalte gelöst worden. 😎


Eine weitere Möglichkeit der Berechnung, wo der verstärkte Einsatz der Duration-Funktion zum Tragen kommt, können Sie in diesem $$-Beitrag zu den gleichen Konditionen wie Sie diesen Artikel bekommen haben, nachlesen.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein veröffentlicht. Setze ein Lesezeichen auf den Permalink.