PQ: Nächtliche Arbeitszeit auf kalendarische Daten splitten

In diesem kleinen Exper­i­ment habe ich mich auf die Basics beschränkt. So wer­den geset­zliche Regelun­gen, dass beispiel­sweise nach 6 Stun­den Arbeit­szeit eine Pause ein­gelegt wer­den muss, nicht beachtet. Auch Woch­enen­den und Feiertage wer­den wie ganz nor­male Tage behan­delt.

Der Aus­gangspunkt für diese Übung ist diese kleine, gewiss selb­sterk­lärende Tabelle:

Die Dat­en bitte von Hand eingeben… 😉

Am Beispiel des ersten Ein­trags ergibt sich, dass auf den 22. Mai 2 Stun­den ent­fall­en (22:00 Uhr bis 24:00 Uhr) und für den 23. Mai sollen 3 Stun­den in Anrech­nung kom­men, entspricht der Zeit von 00:00 Uhr bis 03:00 Uhr. Und da am Fol­ge­tag (näch­ste Zeile) auch (beispiel­sweise durch einen anderen Mitar­beit­er oder durch eine andere Mas­chine) zu berech­nende Stun­den anfall­en, sollen diese selb­stre­dend im Endergeb­nis kumuliert wer­den. Hin­weis: Ich weiß, dass Excel kein „24:00” ken­nt, ist aber Sprachge­brauch und somit ein­deutiger. 💡 

▲ nach oben …

Einstieg

Begin­nen Sie damit, in ein leeres Arbeits­blatt die hierüber im Bild gezeigten Dat­en einzu­tra­gen. Anschließend for­matieren Sie den Bere­ich als „Intel­li­gente“ Tabelle (StrgT oder StrgL). Sie acht­en natür­lich darauf, dass die Dat­en nicht als Text son­dern als Datum bzw. Zeit eingegeben wer­den. Ich gebe dieser Tabelle auch gle­ich den „sprechen­den“ Namen Arbeit­szeit­en, welch­er dann auch automa­tisch beim anschließen­den Import in den Pow­er Query-Edi­tor über­nom­men wird.

Ich stelle Ihnen hier eine von mehreren Möglichkeit­en ein­er Vorge­hensweise vor. Für mich ste­ht im Vorder­grund, dass eine Lösung auch durch den „typ­is­chen“ lern­willi­gen User nachvol­lziehbar ist und somit bei Bedarf auch selb­st­ständig erweit­ert oder angepasst wer­den kann. Und ich muss nicht damit „angeben“, dass ich so schicke, kurze und wenig Schritte umfassende Lösung gefun­den habe, die vielle­icht wenige Sekun­den­bruchteile schneller ist als die von mir jew­eils vorgestellte. Und User, die tief in der Excel- oder sog­ar Pro­gram­mi­er-Welt ver­wurzelt sind, wer­den rasch die anderen Möglichkeit­en find­en und vielle­icht nutzen.

▲ nach oben …

Die Berechnung

Mein erste Ver­such ein­er Lösung hat zwar pri­ma geklappt, war aber doch zu stark an die Pro­gram­mierung wie in VBA angelehnt und ich habe mir über­legt, dass solch eine Lösung nicht unbe­d­ingt für meine Ziel­gruppe „Engagierte Excel-Anwen­der” gut nachvol­lziehbar ist. Darum habe ich mit eini­gen Schrit­ten (und 2 Abfra­gen) mehr einen deut­lich bess­er ver­ständliche Lösungsweg erar­beit­et.

Fol­gende Über­legung ist für mein Vorge­hen maßge­blich: Für jeden der aufge­führten kalen­darischen Dat­en in der Spalte Datum muss eine weit­ere Zeile gener­iert wer­den, wo eventuelle „Über­hangzeit­en“ nach Mit­ter­nacht einge­tra­gen wer­den; falls an dem Tag (der Zeile) nur zwis­chen 0:00 Uhr und 24:00 Uhr zu berech­nende Zeit­en ange­fall­en sind, wird in der zweit­en Daten­zeile (die automa­tisch das Datum des Fol­ge­tages haben wird) der Wert 00:00 (in einem eventuell anderm Daten­for­mat) einge­tra­gen, denn es wurde ja nicht naht­los an den in der 1. Spalte ste­hen­den Tag gear­beit­et.

Sie haben nach dem Import die typ­is­che und für PQ-Ein­steiger etwas gewöh­nungs­bedürftig Ansicht, dass das Datum automa­tisch vom Daten­typ Datum und Zeit dargestellt wird und die bei­den Spal­ten von und bis, welche in Plain Excel deut­lich als Uhrzeit for­matiert sind, haben hier das Daten­for­mat Dez­i­malzahl. Wenn es sie nicht stört, kön­nten (!) Sie es hier­bei belassen. Ich per­sön­lich ziehe es aus ver­schiede­nen Grün­den vor, die Spalte Datum als (nur) Datum zu for­matieren. Und wenn Sie eher visuell als pro­grammtech­nisch ori­en­tiert sind empfehle ich Ihnen, die bei­den Uhrzeit-Spal­ten auch als Zeit zu for­matieren. Und genau darauf bauen auch die weit­eren Schritte und vor allen Din­gen die Formeln dieses kleinen Sem­i­nars auf. Auch wenn es wenige Mil­lisekun­den län­gere Laufzeit des Pro­gramms bedeutet.

Nun stellt sich gewiss die Frage, wie mit gut nachvol­lziehbaren Schrit­ten und ohne große pro­grammtech­nis­che Ein­griffe jede der Zeilen 1 bis Zeile 4 in jew­eils zwei Zeilen aufgeteilt wer­den kann also für den aktuellen und den Fol­ge­tag. Ich biete Ihnen dazu dieses Vorge­hen an:

• Duplizieren Sie die bis­lang einzige Abfrage Arbeit­szeit­en zwei mal.
• Benen­nen Sie die Query Arbeit­szeit­en (2) zu Tag 1 um.
• Benen­nen Sie die Query Arbeit­szeit­en (3) zu Tag 2 um.
• Wech­seln Sie zu Abfrage Tag 1.
• Menü Spalte hinzufü­gen | Benutzerdefinierte Spalte.
• Vergeben Sie als Neuer Spal­tenname von und bei Benutzerdefinierte Spal­tenformel übernehmen Sie per Dop­pelk­lick aus dem Bere­ich Ver­füg­bare Spal­ten den Wert von:

Die erste Benutzerdefinierte Spalte

• Nun noch eine weit­ere neue Spalte mit dem Wert aus bis anle­gen.

Spätestens jet­zt wird Ihnen aufge­fall­en sein, dass die eben neu angelegten Spal­ten bei den Über­schriften eine Änderung erfahren haben, es wurde automa­tisch .1 hinzuge­fügt. Und im näch­sten Schritt wer­den Sie eine weit­ere Benutzerdefinierte Spalte hinzufü­gen, wo die Berech­nung der Dif­ferenz bis zum Tage­sende oder ‑falls Mit­ter­nacht nicht über­schrit­ten wird- der Ende-Zeit des gle­ichen Tages erfol­gen soll. In Plain Excel würde die Formel (in der DE-Ver­sion) so ausse­hen:
=WENN([@bis]>=[@von];[@bis]-[@von];1-[@von])
…und auch das kor­rek­te Ergeb­nis brin­gen. Vom Prinzip her ließe sich diese Formel in angepasster Form auch hier ver­wen­den. Und mit eini­gen Tricks und Umwe­gen würde das auch in PQ klap­pen. Aber ich möchte Ihnen einen anderen, im End­ef­fekt leichter ver­ständlichen Weg aufzeigen; auch wenn er im ersten Anlauf nicht zum gewün­scht­en Erfolg führt. Aber ich finde, dass Fehler dazu da sind, gemacht zu wer­den und daraus zu ler­nen.

Okay, duplizieren Sie erst ein­mal die Spalte von, indem Sie über das Menü Spalte hinzufü­gen eine Benutzerdefinierte Spalte erzeu­gen und als Neuer Spal­tenname eben­falls von vergeben. In den Bere­ich Benutzerdefinierte Spal­tenformel fügen Sie per Dop­pelk­lick die Spalte von aus dem recht­en Bere­ich ein. Nach einem OK wer­den Sie erken­nen, dass die Über­schrift der neuen Spalte automa­tisch auf von.1 angepasst wurde, da in eine Tabelle bzw. Abfrage jede Über­schrift ein­ma­lig sein muss. Die gle­ich Vorge­hensweise nun auch für die Spalte bis durch­führen. Hin­weis: Und ja, Sie hät­ten natür­lich auch die bei­den Spal­ten ein­fach duplizieren und dann umbe­nen­nen kön­nen, das wäre der gle­iche Effekt gewe­sen. 😉

Markieren Sie nun zuerst die Spalte bis.1 und anschließend mit Strg oder Shift von.1. Immer noch im Menü Spalte hinzufü­gen Klick­en Sie auf Zeit | Sub­trahieren, um die bei­den Zeit­en voneinan­der abzuziehen. Und das im vollen Bewusst­sein, dass die Mit­ter­nacht-Gren­ze bei der Berech­nung nicht beachtet wird, wie eigentlich gefordert. Aber so bekom­men Sie einen kleinen Ein­druck, was PQ in solchen Sit­u­a­tio­nen macht:

Auch Zeit­en kön­nen sub­trahiert wer­den

Auf den allerersten Blick sieht das ja ganz vernün­ftig aus, beim genaueren Hin­se­hen wird Ihnen aber rasch dieses oder jenes auf­fall­en: Die Daten­typen sind unter­schiedlich (Dauer/Duration und Zeit), Pow­er Query ken­nt neg­a­tive Zeit­en (!) und PQ rech­net tat­säch­lich in der Rei­hen­folge, wie Sie die Spal­ten markiert haben. Ach ja, die berech­neten Zeit­en, wo Mit­ter­nacht nicht über­schrit­ten wor­den ist, stim­men sog­ar! Merke: Wenn bei­de Zeit­en am gle­ichen Tag sind, ist das eine cle­vere Möglichkeit der Berech­nung. Ein Ergeb­nis in der Dura­tion-For­matierung ist für Pow­er Query kor­rekt.

Nun ja, wie vorher schon angedeutet: Das Ergeb­nis ist ja nicht opti­mal, darum löschen Sie die Schritte, begin­nend mit den Dup­likat­en der bei­den Spal­ten von und bis. Ach ja, wenn Sie so faul sind wie ich 😎, dann löschen Sie stattdessen gle­ich zu Beginn die Abfrage Tag 1, duplizieren die Query Tag 2 und bene­nen diese gle­ich zu Tag 1 um. Dann haben Sie eben­falls die gewün­schte Aus­gangslage.

Im Grunde genom­men müssen Sie doch nur fol­gende Bedin­gung schaf­fen: Wenn bis klein­er ist als von dann muss in der Spalte bis.1 der Wert von 0:00 Uhr des Fol­ge­tages ste­hen, anson­sten reicht natür­lich wie gehabt die Sub­trak­tion der bei­den Zeit­en. Damit das ganze „sauber“ abläuft, wer­den Sie etwas tricky arbeit­en. 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 kom­binieren. Die Über­schrift ist Zusam­menge­führt und der Typ der Spalte ist Datum/Uhrzeit. Ändern Sie den Spal­tenna­men auf von.1.

Für die Spalte bis.1 gilt ja die Regel, dass bei Zeit­en klein­er 24:00 Uhr (eigentlich 00:00h) der Wert aus bis über­nom­men wird und im anderen Fall 0:00 Uhr des kom­menden Tages einge­tra­gen wer­den muss. Dazu gehen Sie über Benutzerdefinierte Spalte, tra­gen bei Neuer Spal­tenname den Wert bis.1 ein und im Bere­ich der Formel geben Sie fol­gen­den Code ein:

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

Damit erre­ichen Sie das besproch­ene Ziel. Bedenken Sie, dass sie die Schreib­weise ins­beson­dere in Bezug auf Groß- / Klein­schrei­bung exakt so übernehmen müssen. Die bei­den ver­wen­de­ten Funk­tio­nen Date.AddDays() und Time.From() sind in der Hil­fe gut und ver­ständlich doku­men­tiert. Wenn Sie nun die bei­den Spal­ten mit den Datum/Zeit – Werten Sub­trahieren, bekom­men Sie die kor­rek­ten Werte für den ersten Tag der Zeile im For­mat Dauer/Dura­tion. Die bei­den Spal­ten Zusam­menge­führt und bis.1 kön­nen Sie nun löschen; wenn Sie strin­gent auf Dat­en-Sparsamkeit acht­en, dann brauchen Sie auch nur noch die Spal­ten Datum und Sub­trak­tion zu belassen, was auch mein­er Empfehlung entspricht.

Im Grunde genom­men kön­nen Sie dieses Ergeb­nis in dieser Darstel­lungsweise so belassen. Spe­ich­ern Sie gerne diesen Zwis­chen­schritt in einem neuen Tabel­len­blatt und for­matieren sie dann die Spalte  Sub­trak­tion  im Zahlen­for­mat Zeit und das Ergeb­nis kön­nte nicht bess­er ausse­hen. 😎 Soll­ten Sie jedoch auch im Pow­er Query-Edi­tor den Daten­typ Zeit haben wollen, dann ver­suchen Sie gerne, diesen Typ direkt anzuwen­den. Sie sehen, das führt zu einem Fehler. Aber mit einem kleinen Umweg ist das dur­chaus mach­bar: Wählen Sie als erstes für die Spalte Sub­trak­tion den Daten­typ Dez­i­malzahl und direkt im Anschluss den Daten­typ Zeit. Nun sieht das auch so aus, wie sie es wahrschein­lich erwartet haben. 💡 

Ob Sie nun das For­mat Dauer oder Zeit in der Spalte ste­hen haben, spielt keine wesentliche Rolle; Sie soll­ten in jedem Falle die Spal­tenüber­schrift auf Arbeit­szeit ändern. Damit ist die Abfrage Tag 1 fer­tig bear­beit­et.

▲ nach oben …

Wech­seln Sie zur Query Tag 2. Auch hier ist es naturgemäß wichtig festzustellen, ob die Arbeit­szeit tagüber­greifend ist oder nicht. Wenn die 00:00 – Gren­ze über­schrit­ten wor­den ist, ist es ja keines­falls die Dif­ferenz-Zeit zwis­chen bis und von, denn das wurde bere­its in der Query Tag 1 berech­net; es ist die von-Zeit 00:00 (des Fol­ge­tages) bis zum Wert in bis.1; anson­sten sind es natür­lich 0:00 Stun­den, die nach Mit­ter­nacht gear­beit­et wur­den. Der Weg zum Ziel ist hier (auch ohne den vorheri­gen, nur der Erken­nt­nis wegen gemacht­en Umweg) etwas kürz­er. In ein­er neuen, benutzerdefinierten Spalte geben Sie gle­ich Arbeit­szeit als Spal­tenname und darunter beispiel­sweise diesen Code ein:

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

Wie auch in vie­len anderen Sprachen der IT leit­en die bei­den // einen Kom­men­tar ein, der so aus­gelegt ist, dass die Zeile ab hier im Pro­gramm nicht aus­gewetet, beachtet wird. Und ein Wort zur Funk­tion #time: Ja, Klein­schrei­bung! Und die 3 numerischen Werte (hier: Nullen) ste­hen für Stunde, Minute, Sekunde; also ana­log zur Excel-Funk­tion ZEIT().

Entsprechend der Spal­ten-Reduk­tion in der Query Tag 1 soll­ten Sie auch hier die Spal­ten „ein­dampfen“. Und noch ein Punkt ist wichtig: Die Spal­tenüber­schriften der bei­den Tag #-Abfra­gen müssen abso­lut iden­tisch sein! Und es ist mehr als hil­fre­ich, wenn die Spalte Arbeit­szeit in bei­den Queries den gle­ichen Daten­typ hat (ich bevorzuge Dauer/Dura­tion).

▲ nach oben …

Endspurt

Ihnen liegen nun 3 Abfra­gen vor: Arbeit­szeit­en, Tag 1 und Tag 2. Um später ein unnötiges Auf­blähen der Arbeitsmappe zu ver­mei­den, soll­ten Sie nun die Query Arbeit­szeit­en markieren und als Nur Verbindung spe­ich­ern. Dadurch wer­den automa­tisch auch die restlichen Abfra­gen in diesem Modus gesichert.

Wech­seln Sie in das Menü Start. Markieren Sie die Abfrage Tag 1 und Kom­binieren | Abfra­gen anfü­gen ▼ | Abfra­gen als neu anfü­gen. Es bleibt bei Zwei Tabellen und als Zweite Tabelle wählen Sie Tag 2. Je nach endgültiger Zielset­zung kön­nen Sie nun a) die Spalte Datum auf­steigend sortieren und es dann dabei belassen oder b) die Spalte Datum so wie sie ist Grup­pieren nach… und die Spalte Arbeit­szeit direkt als Summe berech­nen (aggregieren). So war es ja auch zu Beginn gefordert…

Da Sie ger­ade das Zusam­men­führen als Neue Abfrage durchge­führt haben genügt ein „ganz nor­males“ Datei | Schließen & laden, um das Ergeb­nis in ein neues Tabel­len­blatt zu schreiben. Eventuell noch als Zahlen­for­mat Zeit darstellen und vielle­icht noch an die gewün­schte Posi­tion ver­schieben (sofern Sie nicht schon gle­ich per Schließen & laden in… die Wun­sch­po­si­tion bes­timmt haben). – Das wars dann auch!

Rück­mel­dun­gen / Feed­back 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 Ihrer­seits z.B. 3,00 € freuen … (← Klick mich!)