Tipps & Tricks zu Microsoft Office Testbericht

**********
kurzes Vorwort, muss ja schließlich sein ...
Tja, Eigenlob stinkt zwar, wie man so sagt, aber es wird mir immer wieder gesagt: ?Nicht verzagen, Uli fragen?. Damit will ich nur ausdrücken, dass ich bislang (Gott sei Dank) immer noch eine pfiffige Lösung gefunden habe, die das Problem ?vom Tisch fegt?.
Hier ?kommt? nun eine Information, die hoffentlich für viele eine Hilfe sein soll; wenigstens aber einen Anhaltspunkt darstellen kann.
Es geht um die Stundenberechnung in MS Excel.
Jeder, der schon einmal mit Stunden und Tagen in MS Excel gerechnet hat, kennt die diversen Fallstricke, die da z. B. wären:
- MS Excel kann nicht vor mit Zeiten und Datumswerten vor dem 01.01.1900 rechnen
(bevor ich jetzt hier Nachrichten und Kommentare in Massen bekommen sollte; ja, es gibt besondere Tricks, wie man es doch bewerkstelligen kann,
und
ja, es gibt die Möglichkeit, das Datum umzustellen (01.01.1904 Datumswert)
- Intern rechnet MS Excel ja nur mit den Werten o bis 1, wobei 0 / 1 den Uhrzeiten 0:00 Uhr / 24:00 Uhr entsprechen, 12:00 Uhr z. B. dem Wert 0,5 entspricht usw.

**********
Gliederung:
1. Die Problemstellung zur Stundenberechnung allgemein
2. allgemeine Vorüberlegungen, wie an das Problem heranzugehen ist:
3. eine / die grundsätzlich mögliche Lösung:
4. so habe ich das ?Problemchen? gelöst ? (mit den Erklärungen zu einzelnen Rechenschritten)
a) Infos zum Nachvollziehen
b) Rechenschritt 1 (zur Verdeutlichung)
c) Rechenschritt 2 (zur Verdeutlichung)
d) Rechenschritt 3 (zur Verdeutlichung)
e) eine mögliche Verfeinerung der Berechnung ?

**********
1. Infos zur Silbentrennung allgemein ...
die Problemstellung:
Ein Kollege kam vor einigen Tagen ziemlich am Boden zerstört zu mir und bat mich um Hilfe. Er hatte eine Kalkulation für gewisse Rückstellungen in MS Excel vorzunehmen, die auf der Grundlage der täglichen Arbeitsstunden, ggf. vorhandener Überstunden und dem bestehenden (Rest)-Urlaub basiert.
Es dürfen maximal 5 Urlaubstage berücksichtigt werden. Minusstunden bleiben unberücksichtigt. Probleme bereiteten ihm die Zeiten, da - bei der Aufsummierung ja mehr als 24 Stunden auftreten können; die gesamten Überstunden auch über 24 Stunden betragen können.
Sei es wie es ist, er bekam jedenfalls nur kuriose Werte als Endergebnis heraus, die offensichtlich auf den ersten Blick schon nicht stimmen konnten.
Hier zur Verdeutlichung die Eckwerte, die er mir als Vorgabe für seinen Musterfall mitgeteilt hatte:
Die tägliche Arbeitszeit / Sollstundenzeit beträgt 8:12 Std. / täglich.
Der ?Muster?-Kollege hat noch 5 Resturlaubstage (à 8:12 Std.)
Der ?Muster?-Kollege besitzt insgesamt noch 20:00 Std. die als Überstunden auf seinem Konto gutgeschrieben sind. Der Stundensatz für die Kalkulation beträgt 20,00 Euro / Std.
Als Ergebnis müsste hier nach der Berechnung dann der Wert von 1.220,00 Euro herauskommen (5 Tage x 8:12 Std. / täglich + 20:00 Überstunden)
Die Berechnung detailliert aufgeschlüsselt (entspricht nachher auch den Formel / Funktionen) 5 x 492 Minuten=2.460 Minuten + 20:00 Überstunden= 1.200 Minuten = Gesamt: 3.660 Minuten oder (dividiert durch 60) = 61 Stunden x 20,00 ? = 1.220,00 Euro.

**********
2. allgemeine Vorüberlegungen, wie an das Problem heranzugehen ist:
Bekanntermaßen kann MS Excel ja nicht so ohne Weiteres mit Stunden und Minuten umgehen, wenn diese über 24 Stunden hinausgehen.
Hier, kurz zum Verständnis:
Excel intern ist die Uhrzeit eine natürliche Zahl. So entspricht der Wert 0:00 Uhr bzw. 24:00 Uhr den Werten 0 bzw. 1, 6:00 Uhr entspricht danach der Zahl 0,25, 12:00 Uhr der Zahl 0,5 und 18:00 Uhr der Zahl 0,75.
Sämtliche Uhrzeitwerte dazwischen sind entsprechende Nachkommazahlen. Nun gibt es natürlich den Trick, mit Hilfe einer Zellformatierung auch Uhrzeitwerte über 24:00 Uhr hinaus darzustellen (benutzerdefiniertes Zahlenformat -> in eckige Klammern gestellt wird das h für Stunden, die Formatierung sähe danach wie folgt aus: [hh]:mm).
Es lassen sich auch in gewisser Weise weitere Berechnungen durchführen.
Im vorliegenden Falle musste aber schlussendlich auch noch mit Eurowerten kalkuliert werden, und da war dann einfach nichts mehr zu machen. In diesen und auch ähnlich gelagerten Fällen wende ich regelmäßig einen ?kleinen Trick? an.
Ich rechne die gesamten Werte einfach in Zahlen um.
Erst das Endergebnis wird wieder als Uhrzeitformat dargestellt.

**********
3. eine / die grundsätzlich mögliche Lösung:
Wie ja bereits erwähnt muss lediglich eine einheitliche Zahlenvorgabe verwendet werden, um eine korrekte Lösung zu erhalten. Im vorliegenden Fall bieten sie die Minuten natürlich als Basiswert geradezu an.

**********
4. so habe ich das ?Problemchen? gelöst ? (mit den Erklärungen zu einzelnen Rechenschritten)
----------
a) Infos zum Nachvollziehen
Die Eckwerte stehen in folgenden Zellen:
- der tägliche Stundensatz wurde als Uhrzeitwert ( 8:12 ) in Zelle C 2 eingetragen
- der restliche Urlaubsanspruch als normale Zahl befindet sich in Zelle D 2
- die gesamten Überstunden - ebenfalls als BESONDERER Uhrzeitwert - sind in Zelle E 2
- der kalkulierte Stunden je Mitarbeiter wurde in Zelle F 2 eingegeben
- das Endergebnis schließlich soll in Zelle G 2 ausgegeben werden
----------
b) Rechenschritt 1 (zur Verdeutlichung)
In Hilfszelle H 2 werden folgende Funktionen / Formeln genutzt: Stunde(), Minute(), sowie die Wenn-Dann-Sonst-Anweisungen für die Prüfung der vorhandenen Urlaubstage.
Die Formel lautet danach: =WENN(ODER(D2<=0;D2="");0;
WENN(D2>5;5*(STUNDE(C2)*60+
MINUTE(C2));(STUNDE(C2)*60+MINUTE(C2))*D2))
Mit der ersten, verschachtelten Wenn-Dann-Sonst-Anweisung im ersten Bereich wird geprüft ob Urlaubstage vorliegen.
Wenn der Eintrag kleiner oder gleich 0 bzw. der Eintrag leer ist, wird als Ergebnis mit 0 gerechnet. Mit der zweiten Wenn-Dann-Sonst-Anweisung wird geprüft ob mehr als 5 Urlaubstage eingegeben worden sind. Ist dies der Fall, wird auf 5 Tage gekürzt und damit gerechnet, andernfalls werden die eingegebenen Tage berücksichtigt.
Nun erfolgt auf dieser Basis die Umrechnung der Zeiten. Das Ergebnis sollte jetzt den als Zahl nutzbaren Wert für 8:12 Std., nämlich 492 (Minuten) aufweisen. Dieser wird mit 5 Tagen direkt multipliziert. Als Zwischenwert sollte nun 2.460 angezeigt werden.
Achtung: auf korrekte Klammersetzungen achten !!
----------
c) Rechenschritt 2 (zur Verdeutlichung)
In Hilfszelle I 2 erfolgt die Umrechnung der Überstunden und die Addition des zuvor ermittelten Wertes. Die Formel lautet danach: =(STUNDE(E2)*60+MINUTE(E2))+B20
Mittlerweile ist der Gesamtwert - allerdings lediglich in ?Minuten? - verfügbar. Er sollte bei richtiger Formeleingabe den Wert 3.660 anzeigen. Ggf. ist eine Nachformatierung als reine Zahl noch erforderlich.
----------
d) Rechenschritt 3 (zur Verdeutlichung)
In Zelle G 2 erfolgt jetzt die Umrechnung wieder zurück in Stunden, sowie die Multiplikation mit dem Stundenwert von 20,00 Euro.
Die Formel lautet danach: =H2/60*F2
Das korrekte Ergebnis sollte nun auf 1.220,00 Euro lauten. Diese Werte können nun - einschließlich der Hilfszellen - in der Auflistung entsprechend oft nach unten kopiert werden.
----------
e) eine mögliche Verfeinerung der Berechnung ?
Versiertere Excelnutzer bzw. Hilfszellenhasser packen diese Berechnung natürlich direkt in die Ergebniszelle und verfeinern das Ganze ggf. noch ein wenig mehr, indem mit Wenn-Dann-Sonst-Abfragen auch noch geprüft wird, ob überhaupt Werte enthalten sind.
Die Formel ausschließlich in der Ergebniszelle sähe danach wie folgt aus:
=RUNDEN(((WENN(ODER(D2<=0;D2="");0;
WENN(D2>5;5*(STUNDE(C2)*60
+MINUTE(C2));(STUNDE(C2)*60
+MINUTE(C2))*D2)))+(STUNDE(E2)*60
+MINUTE(E2)))/60*F2;2)
(Die Formeln musste ich hier aus Gründen der Lesbarkeit und Übersichtlichkeit auf mehrere Zeilen aufteilen. Korrekt müssen sie natürlich direkt hintereinander gesetzt werden, ohne Leerzeichen etc..)
----------
Erklärung:
Sieht vielleicht ziemlich kompliziert aus, ist sie aber eigentlich gar nicht.
Im ersten Bereich ** beginnt mit: WENN(ODER(D2 ....) ** erfolgen lediglich Prüfungen, ob Urlaub vorhanden ist, und wenn ja , wie viel Urlaub berechnet werden darf.
Im zweiten Bereich ** beginnt mit: (STUNDE(C2)*60+MINUTE(C2) ** werden die Werte dann einheitlich auf Minuten umgerechnet.
Im dritten Bereich ** beginnt mit +(STUNDE(E2)*60+MINUTE(E2) ** wird der Überstundenwert hinzugerechnet.
Im vierten und letzten Bereich ** beginnt mit /60*F2 ** wird das berechnete Zwischenergebnis einfach wieder durch 60 geteilt, um Stunden zu bekommen.
Diese werden dann mit dem Stundensatz einfach multipliziert.
Die ganze Berechnung wird noch mit einer Rundung auf 2 Nachkommastellen umschlossen ** beginnt mit RUNDEN **
Eine nette Gedankenspielerei, die man noch um die Prüfung von vorhanden Überstunden / Fehlstunden erweitern könnte.
Nur würde dies hier sicherlich zu weit führen.
Viel Spaß beim Erarbeiten / Bearbeiten / Nachbearbeiten. Ich würde mich über entsprechendes Feedback (als Nachricht o.ä.) freuen.
So, das war es ´mal wieder von mir ...
Ich hoffe, ich konnte der / dem einen oder anderen ein wenig helfen, zumindest aber Anhaltspunkte geben, und freue mich natürlich über jedes Statement und natürlich über jede andere, gute Idee.

Über entsprechendes Feedback (als GB-Eintrag etc.) freue ich mich natürlich ganz besonders.
Sollten Fragen bestehen, sollten ggf. Probleme existieren; ich versuche zu helfen, wenn ich kann. Man lasse mir also ggf. einfach eine entsprechende Anfrage / Nachricht zukommen.
**********
Zum Schluss:
Mir wünsche ich viele Kommentare und natürlich möglichst viele positive Bewertungen.
(Aber nur dann, wenn Ihnen der Bericht wirklich gefallen hat. Frei nach dem Motto: der Bericht hat Ihnen gefallen, dann sagen Sie es weiter. Der Bericht hat Ihnen nicht gefallen, dann sagen Sie es mir!).
Bis zum nächsten Bericht, man liest sich ?!
PS: meine Berichte erscheinen / erschienen auch auf anderen Plattformen.