Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, keine/kaum Erfahrung   

Minuten-Gewurschtel  🙄

Es gibt Zeit­er­fas­sungs-Sys­teme, welche ein sehr eigen­williges For­mat in ein File aus­geben. Schauen Sie sich ein­mal die fol­gende Tabelle an, wo in der Spalte Erfas­sung die maschinell erfassten Zeit­en ste­hen und in der Spalte Reale Zeit die Zeit­en, wie sie durch Excel „vernün­ftig” ver­ar­beit­bar sind:

Nochmals zur Verdeut­lichung: Zu Beginn existieren auss­chließlich die Werte aus der Spalte Erfas­sung, die zweite Spalte soll erst noch berech­net wer­den. Beson­ders in der ersten Daten­zeile unter den Über­schriften ist die Diskrepanz zwis­chen dem typ­is­chen Ver­ständ­nis durch die Excel-Nutzer und dem eigentlichen, kor­rek­ten Ergeb­nis sicht­bar; der erfasste Wert 3,5 entspricht keineswegs 3 ½ Stun­den, was ja 3:30 wäre. Nein, der ganz­zahlige Teil entspricht tat­säch­lich den Stun­den, der Nachkom­ma-Anteil soll die realen Minuten darstellen. Wobei das auch nicht so wirk­lich der Excel-Welt entspricht, denn eigentlich müssten die erfassten Zahlen immer mit 2 Nachkom­mas­tellen dargestellt, berech­net wer­den. Also, bei 3,5 „Stun­den” ist dann die Idee, daraus 3,50 (also 2‑stellige Nachkom­mazahl) zu gener­ieren, die Ganz­zahl und das Kom­ma zu ent­fer­nen und die verbleibende Zahl 50 sind dann die Minuten. Wenn Sie unter diesem Aspekt die Tabelle hierüber betra­cht­en, ist das Ganze hof­fentlich etwas ver­ständlich­er.

Lösung per VBA

Dieses Ziel ließe sich recht gut mit ein­er UDF (benutzerdefinierten Funk­tion) lösen. Aber es gibt in vie­len Unternehmen gute Gründe, Makros/VBA nicht zu ermöglichen. Für alle, die es pro­bieren wollen hier ein möglichst trans­par­enter Beispiel-Code:

Wie und wo Sie eine UDF erstellen und ein­fü­gen, kön­nen Sie hier nach­le­sen. Die Anwen­dung wäre dann beispiel­sweise so, dass Sie in B2 diese Formel schreiben: =RealZeit(A2) und das Ergeb­nis dann als Zeit im gewün­scht­en For­mat darstellen lassen. Diese Formel ziehen Sie dann so weit wie erforder­lich nach unten. Und noch ein Hin­weis: Wenn Sie diese UDF ern­sthaft ein­set­zen wollen, dann soll­ten Sie noch für eine Fehler­be­hand­lung sor­gen…

Lösung mit Formeln

Diese „Auf­gabe” stammt im Grund­satz aus einem Forum. Und dort wur­den auch einige Formeln vorgestellt, welche mehr oder weniger zielführend waren. Da sie alle­samt nicht „auf meinem Mist gewach­sen sind”, gehe ich auch nicht expliz­it darauf ein. Suchen Sie gerne im Netz, die Über­schrift lautet „Zeit­en richtig addieren” und der Thread wächst, und wächst … Ich habe zu Beginn der 2. Seite aufge­hört, weit­er mitzule­sen und mich mein­er, der hier vorgestell­ten Lösung gewid­met.  😎 

Modern mit Power Query

Seit Excel 2010 ist Pow­er Query ja als Add-In ver­füg­bar bzw. begin­nend mit der Ver­sion 2016 fest inte­gri­ert. Hier brauchen Sie in den meis­ten Fällen keine Pro­gram­mierken­nt­nisse, so gut wie immer ist alles mit eini­gen Mausklicks zu schaf­fen. Auch diese Auf­gabe ließe sich nur mit „ohne eine einzige Formel” lösen, aber ich bin aus­nahm­sweise ein­mal der Überzeu­gung, dass der Aufwand über diesen Weg zu groß wäre. Es wären zu viele Schritte, welche die Trans­parenz bei eventuellen späteren Kor­rek­turen enorm ein­schränken wür­den. Die 3 Formeln, welche Sie hier bei dem von mir vorgeschla­ge­nen Weg eingeben wer­den, ver­schlanken das Pro­jekt doch enorm. In erster Lin­ie aber zählt aus mein­er Betra­ch­tungsweise, dass die Über­sichtlichkeit erhe­blich bess­er gegeben ist; den­noch gilt aus­nahm­s­los fol­gende Fest­stel­lung: Ich finde Aus­sagen wie „Der Code ist bess­er, weil er kürz­er ist” ein­fach nur dumm. 👿 

Begin­nen Sie damit, dass Sie in einem Excel Arbeits­blatt begin­nend in A1 und den fol­gen­den Zeilen der Spalte A die Werte aus der Spalte Erfas­sung (siehe weit­er oben) ein­schließlich der Über­schrift eingeben. Aus­nahm­sweise gibt es hier ein­mal keine Muster-*.xlsx, denn die paar Zahlen in ein Tabel­len­blatt einzugeben stellt ja nun wirk­lich keinen großen Aufwand dar. Acht­en Sie darauf, dass eine beliebige Zelle inner­halb der Dat­en markiert ist und auf beliebige Weise daraus eine Intel­li­gente Tabelle zu erstellen. Ich mache das meis­tens über StrgL. Anschließend vergebe ich im Feld Tabel­len­name: die Beze­ich­nung Arbeit­szeit­en:

Hier vergeben Sie den Namen für die Tabelle

Wählen Sie nun Dat­en | Aus Tabelle bzw. Pow­er Query | Von Tabelle und die Liste bzw. Tabelle wird in den Pow­er Query-Edi­tor importiert:

Die Dat­en nach dem Import in den Pow­er Query-Edi­tor

Im recht­en Seit­en­fen­ster ist der Name der Tabelle automa­tisch als Name für die Abfrage über­nom­men wor­den und darunter sind die ersten bei­den aufgeze­ich­neten Schritte des Imports (Angewen­dete Schritte) dargestellt:

Der Bere­ich des recht­en Seit­en­fen­sters

Hier noch ein­mal der Hin­weis, dass es auch ohne die Formeln geht… 😉 Wech­seln Sie zum Reg­is­ter Spalte hinzufü­gen und im Menüband in der Gruppe All­ge­mein ein Klick auf Benutzerdefinierte Spalte. In das Textfeld Neuer Spal­tenname geben Sie Stun­den als die kün­ftige Über­schrift ein. Darunter in das große Textfeld Benutzerdefinierte Spal­tenformel: geben Sie nach dem vorgegebe­nen Gle­ich­heit­sze­ichen diese Formel ein:
= Number.RoundDown([Erfassung], 0)

Acht­en Sie auf die Groß- Klein­schrei­bung, denn die Sprache M ist Case-sen­si­tiv. Übri­gens: Sie kön­nen jeden Spal­tenna­men, hier [Erfas­sung] ganz ein­fach durch einen Dop­pelk­lick auf den entsprechen­den Namen im recht­en Kas­ten übernehmen; die eck­i­gen Klam­mern wer­den dann automa­tisch durch den Edi­tor hinzuge­fügt. Nach einem OK wird die Spalte Stun­den erzeugt und die Anzahl der ganzen Stun­den wird berech­net. Ich denke dass Sie erkan­nt haben, dass dort das Äquiv­a­lent des Excel-ABRUNDEN() in der M‑Sprache angewen­det wurde.

In den näch­sten bei­den Schrit­ten sollen die Nachkom­mas­tellen so angepasst wer­den, dass bei ein­stel­li­gen Darstellungen/Werten eine Null zuge­fügt wird und dadurch die real zu berech­nen­den Minuten erzeugt wer­den. Also aus 2,1 sollen 10 Minuten gener­iert wer­den. Anschließend wer­den Stun­den und Minuten zu ein­er „kor­rek­ten” Zeit zusam­menge­führt.

Erstellen Sie wiederum eine Benutzerdefinierte Spalte und vergeben Sie den Spal­tenna­men Minuten. Als Spal­tenformel tra­gen Sie ein:
= Int16.From(Number.Mod([Erfassung], 1) * 100)

Eine kurze Erk­lärung kön­nte hier hil­fre­ich sein… In Excel wäre das in etwa die Funk­tion
REST(Erfassung; 1) * 100

Wie erwartet wird eine Spalte Minuten erzeugt und die berech­neten Minuten wer­den dort als Zahl einge­fügt. Und in der let­zten Formel wer­den Sie dafür sor­gen, dass aus Stun­den und Minuten eine „echte” Zeit gener­iert wird. Natür­lich ist das wieder der Weg über eine Benutzerdefinierte Spalte und fol­gende Formel ist zielführend, nach­dem Sie als neuen Spal­tenna­men beispiel­sweise Reale Zeit eingegeben haben:
= Time.From(Text.From([Stunden]) & ":" & Text.From([Minuten]))

Das Ergeb­nis wird Sie vielle­icht überzeu­gen, mich aber noch nicht ganz. Klick­en Sie ein­mal in die Über­schrift der let­zten Spalte und schauen sich im Menüband des Reg­is­ters Start den Daten­typ an. Es ist Beliebig:

Der Daten­typ ist als ‘Beliebig’ aus­gewiesen wor­den …

Auch wenn die Aus­rich­tung der Zei­tangaben rechts ist und das Ausse­hen tat­säch­lich der typ­is­chen Zei­tangabe entspricht, spätestens beim spe­ich­ern als Tabelle (Schließen & laden) wer­den Sie sehen, dass dann ein kor­rek­ter numerisch­er Wert als serielle Zahl dargestellt wird aber keine (for­matierte) Zeit. Und das müssten Sie dann bei Aktu­al­isierun­gen wieder anpassen, was gewiss nicht so opti­mal ist. Darum ändern Sie hier in PQ vorzugsweise den Daten­typ auf Zeit. In jedem Fall kön­nen Sie nun die bei­den Spal­ten Stun­den und Minuten löschen, indem Sie jew­eils oder zusam­men­fassend in die Über­schrift Klick­en und dann beispiel­sweise Entf oder per Recht­sklick in die Über­schrift und dann im Kon­textmenü Spal­ten ent­fer­nen auswählen. Und wenn Sie auss­chließlich die realen Zeit­en behal­ten wollen, dann löschen Sie auch noch die Spalte Erfas­sung. Jet­zt noch Start, Schließen & laden oder Datei, Schließen & laden in… und bes­tim­men Sie die Posi­tion, wo die Tabelle gespe­ichert wer­den soll. Natür­lich kön­nen Sie auch die nach einem „nor­malen” Schließen & laden in einem neuen Blatt gespe­icherte Tabelle kom­plett auss­chnei­den und an Ihrer Wun­sch­po­si­tion platzieren.

▲ nach oben …