Der Umgang mit Datumsangaben und Uhrzeiten ist ein häufiger Anwendungsfall in Excel. Ob es sich um die Berechnung von Arbeitszeiten handelt oder um die Ermittlung der noch verbleibenden Arbeitstage bis zur Rente: Vieles davon lässt sich bequem mit Excel berechnen.
Unter dem Motto „Zeit und Datum“ stehen auch die heutigen Excel-Quickies. Im diesem Beitrag zeige ich dir Tipps zu folgenden Themen:
- Uhrzeiten umrechnen (von Zeit in Dezimal und umgekehrt)
- Arbeitstageberechnung mit eigendefinierten Wochenenden
- Sommerzeit und Winterzeit
Wenn dich das interessiert, dann lies weiter. Wenn nicht, dann auch.
Excel-Quickie Nr. 85: Uhrzeiten umrechnen
Zeitangaben werden in Excel üblicherweise auch im Zeitformat dargestellt. Also z.B. 10:34, 24:00 usw. Wenn man jedoch Arbeitslöhne auf Basis geleisteter Arbeitsstunden berechnen möchte, benötigt man die Stunden im Dezimalformat. Also statt 8:30 eben 8,5.
Die Umrechnung vom Zeitformat ins Dezimalformat ist denkbar einfach, wenn man berücksichtigt, dass ein Tag aus 24 Stunden besteht:
=Uhrzeit*24
Und anschließend noch das Zellenformat auf „Zahl“ ändern, fertig.
Zeiten in Dezimalzahlen umrechnen
Und der Weg zurück von Dezimal zurück ins Zeitformat geht so:
=Dezimalzahl/24
Und dann eben wieder die Zellformatierung auf „Uhrzeit“ ändern.
Dezimalzahlen in Uhrzeiten verwandeln
Eine zweite Möglichkeit wäre die TEXT-Funktion:
=TEXT(A1/24; "hh:mm")
Noch eine Alternative
Sieht zwar nach einer Uhrzeit aus, ist aber tatsächlich noch Text, wie der geübte Anwender an der linksbündigen Ausrichtung erkennt. Aber oftmals reicht das ja auch aus.
Excel-Quickie Nr. 86: ARBEITSTAG.INTL mit eigendefinierten Wochenenden
Mit der Funktion ARBEITSTAG.INTL lässt sich das Datum berechnen, das von einem Ausgangsdatum eine angegebene Zahl von Arbeitstagen entfernt liegt. Wochenenden werden dabei automatisch ausgeklammert.
Hinweis: Diese Funktion ist erst ab Excel 2010 verfügbar!
Die allgemeine Syntax lautet:
=ARBEITSTAG.INTL(Ausgangsdatum;Arbeitstage;Freie Tage)
Über den dritten Parameter kann man der Funktion mitteilen kann, welche Wochentage als freie Tage zu berücksichtigen sind:
Die Funktion ARBEITSTAGE.INTL
Und erhält für den Wert 1 (= Samstag und Sonntag) z.B. folgendes Ergebnis:
Die Funktion ARBEITSTAGE.INTL (Forts.)
Wenn du aber etwas exotischere Arbeitszeiten hast, kannst du auch ein eigenes Muster mit freien Tagen definieren. Eine Vier-Tage-Woche mit freiem Montag sieht z.B. so aus:
=ARBEITSTAG.INTL(Ausgangsdatum;Arbeitstage;"1000011")
Jede Ziffer 0 und 1 entspricht – beginnend ab dem Montag – einem Wochentag. Dabei bedeutet 0 = Arbeitstag und 1 = freier Tag. Im Beispiel oben sind also der Montag, Samstag und Sonntag freie Tage.
Und dann ergibt sich eben folgendes Bild:
Eigene Freitage definieren
Excel-Quickie Nr. 87: Sommerzeit und Winterzeit
Ich sage es ja wirklich ungern, aber die Sonnwende ist schon eine Weile vorbei und wir bewegen uns unaufhaltsam auf den Winter zu.
Wer sich die Termine für die Zeitumstellungen auf Winter- bzw. Sommerzeit für die nächsten 100 Jahre ausrechnen möchte, kann dies jetzt mit folgenden einfachen Formeln tun.
Sommerzeit:
=DATUM(Jahr;4;0) - WOCHENTAG(DATUM(Jahr;4;0);11)
Winterzeit:
=DATUM(Jahr;11;0) - WOCHENTAG(DATUM(Jahr;11;0);11)
Die Regel ist ja folgende:
Die Umstellung findet immer von Samstag auf Sonntag des letzten Märzwochenendes bzw. des letzten Oktoberwochenendes statt.
=DATUM(2016;4;0)
berechnet den „nullten“ Tag im April diesen Jahres = der letzte Tag im März.
=DATUM(2016;11;0)
liefert den „nullten“ Tag im November, was dem letzten Tag im Oktober entspricht.
Mit Hilfe der WOCHENTAG-Funktion bestimmen wir noch die laufende Nummer des Wochentags des 31.03. bzw. 31.10.
Aufgrund des Parameters „11“ in der WOCHENTAG-Funktion wird der Wochenbeginn auf den Montag gelegt. Wenn wir nun also die eben berechneten Wochentage vom 31.03 bzw. 31.10 abziehen, landen wir somit immer beim letzten Sonntag und damit beim Tag der Zeitumstellung.
Sommerzeit und Winterzeit
So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Mir gefällt besonders der Tipp zu Sommer- und Winterzeit. Danke.
Hallo Martin,
eine feine Lösung zur Ermittlung der Tage der Zeitumstellung.
Ohne mich „reinzudenken“ habe ich diese Lösung mal aus dem Internet übernommen:
=DATUM(Jahr;3;31)-(WOCHENTAG(DATUM(Jahr;3;31))-1)
Durch Deinen Artikel habe ich [endlich] nicht nur den Ansatz verstanden, ich kann es jetzt auch eleganter ermitteln.
Gruß
Michael
Hallo Michael,
freut mich, wenn der Artikel ein wenig geholfen hat.
Schöne Grüße,
Martin
Hallo Martin,
manchmal erstaunt mich Excel (das ich hier im Blog immer einmal wieder etwas Neues wahrnehme ist ja schon Normalzustand) in der Handhabe von Wochentagen.
Normalerweise beginnt doch die Woche in Excelformeln immer mit Sonntag (bspw. in der Formel WOCHENTAG) umso mehr verwundert es mich, dass nun die von dir beschriebene Formel Excel-Quickie Nr. 2: ARBEITSTAG.INTL mit Montag eine Woche beginnen lässt. Aber das Beispiel für flexible Arbeitszeitmodelle finde ich wirklich schön gewählt.
Viele Grüße
Andreas
Hallo Andreas,
danke für das Lob! Du hast Recht, Microsoft ist da nicht so richtig konsequent/konsistent beim Wochenbeginn. Vielleicht liegt es ja an dem „.INTL“, dass jetzt der Montag als Wochenbeginn definiert wurde.
Schöne Grüße,
Martin
Hallo zusammen,
die Idee, den letzten Tag eines Monats – der ja immer ein anderer ist – als den nullten Tag des Folgemonats zu verstehen, ist – gelinde gesagt GENIAL. Was hab‘ ich da immer für eine Gehirnakrobatik veranstaltet!
Das „.INTL“ bei der Fkt. »Arbeitswoche« steht ja vielleicht für „international“ – in USA beginnt die Woche am Sonntag, der Rest der Welt startet am Montag. 😉
Ein ähnliches Problem hatten wir ja bei der Fkt. »Kalenderwoche«. Erst durch das Argument »21« weiß die Fkt. nun, dass es bei uns eine besondere Regelung für die 1. Kalenderwoche eines Jahres gibt.
Hallo Elke,
mir geht es mit der Gehinrakrobatik manchmal genauso und immer wieder sehe ich den Wald vor lauter Bäumen nicht. Freut mich, dass ich da nicht der einzige bin 🙂
Schöne Grüße,
Martin
Hallo Martin,
tolle Seite mit vielen guten Tipps. Allerdings enthält Nr. 87 (Zeitumstellung) aus meiner Sicht einen kleinen Fehler. Die Wochentagsfunktion (mit Parameter 11 aufgerufen) liefert Werte von 1 (Montag) bis 7 (Sonntag). Fällt nun der letzte des Monats (z.B. der 31.03.2019) auf einen Sonntag, so werden entsprechend 7 Tage abgezogen, obwohl die Umstellung am 31.03.2019 erfolgt.
Richtig wäre z.B.:
=DATUM(Jahr; 4; 0) – (WOCHENTAG(DATUM(Jahr; 4; 0); 1) – 1)
=DATUM(Jahr; 11; 0) – (WOCHENTAG(DATUM(Jahr; 11; 0); 1) – 1)
Viele Grüße
Gunnar
Hallo Gunnar,
vielen Dank, das ist mir noch gar nicht aufgefallen und Du hast Recht: Für Jahre, bei denen der letzte Tag im März oder November auf einen Sonntag fällt, liefert meine Lösung das falsche Ergebnis.
Ich fürchte nur, in deiner Lösung hat sich auch ein kleiner Wurm eingeschlichen, bei mir kommt hier auch ein falsches Ergebnis.
Eine mögliche Variante wäre, über eine einfache WENN-Abfrage zu prüfen, ob der letzte Tag des Monats ein Sonntag ist.
=DATUM(Jahr;4;0)-WENN(WOCHENTAG(DATUM(Jahr;4;0);11)=7;0;WOCHENTAG(DATUM(Jahr;4;0);11))
bzw.
=DATUM(Jahr;11;0)-WENN(WOCHENTAG(DATUM(Jahr;11;0);11)=7;0;WOCHENTAG(DATUM(Jahr;11;0);11))
Trotzdem Danke nochmal für deinen Hinweis. Das sollte jetzt hoffentlich passen.
Schöne Grüße,
Martin
Hallo Martin,
so sollte es natürlich funktionieren.
Warum meine Lösung bei dir nicht korrekt arbeitet, kann ich aber nicht verstehen.
WOCHENTAG(..;1) – 1 sollte Werte von 0 (Sonntag) bis 6 (Samstag) liefern und damit genau die Anzahl Tage, die abzuziehen ist!? Wenn ich die Formel aus meinem Beitrag kopiere, wird sie von Excel allerdings überhaupt nicht akzeptiert. Die Minuszeichen sind nicht mehr richtig und müssen durch korrekte Minuszeichen ersetzt werden. Liegt es nur daran oder verhält sich die Funktion bei dir tatsächlich anders und liefert andere Werte (ich verwende Excel 2010)?
Viele Grüße
Gunnar
Hallo Gunnar,
nein, es liegt nicht an den Minuszeichen. Im folgenden Screenshot habe ich für jedes Jahr die letzten 10 Tage im März berechnet. In Zeile 13 steht deine Formel, und es wird damit immer der Freitag berechnet.
Ich verwende Excel 2016, aber das Ergebnis ist unter Excel 2010 das gleiche (habe es gerade nochmal getestet). Warum die Formel bei dir funktioniert, ist mir ein Rätsel…
Schöne Grüße,
Martin
Hallo Martin,
danke für das Beispiel, in der von dir verwendeten Formeln fehlen die Klammern um den Teil (WOCHENTAG(..) – 1). Dadurch wird dann natürlich ein Tag zuviel abgezogen statt addiert und somit kommt immer der Freitag raus.
Viele Grüße
Gunnar
DAS war mein Fehler! Jetzt klappt es auch bei mir. Wer lesen kann, ist klar im Vorteil 🙂
Hiermit nehme ich natürlich alles zurück und behaupte das Gegenteil. Dein Lösung funktioniert einwandfrei.
Danke nochmal und schöne Grüße,
Martin
Hallo, ich bin gerade über den Excel-Quickie Nr. 87 gestolpert. Ich glaube dass die hier angegebenen Formeln zur Berechnung des Beginns von Sommer- und Winterzeit fehlerhaft sind. Wenn nämlich der letzte Tag im März oder Oktober ein Sonntag sind, liefern die Formeln ein Datum, das eine Woche vor dem Richtigen liegt.
Die korrekten Formeln lauten:
Beginn der Sommerzeit:
=DATUM(Jahr;3;31) – (WOCHENTAG(DATUM(Jahr;3;31)) – 1)
Beginn der Winterzeit:
=DATUM(Jahr;10;31) – (WOCHENTAG(DATUM(Jahr;10;31)) – 1)
Dabei habe ich auch (um unnötige Verwirrung zu vermeiden) in der Datums-Funktion den 31.3. bzw. 31.10. verwendet anstatt der hier überflüssigen Verwendung von „4;0“ bzw. 11;0“.
Hallo Uli,
du hast vollkommen recht, meine Formeln liefern für den beschriebenen Fall falsche Ergebnisse!
Danke für diesen Hinweis und vor allem für die korrekten Formeln! Gut, wenn einer mitdenkt 😉
Schöne Grüße,
Martin