Excel-Quickies im Juli 5

Ein paar schnelle Excel-Tipps zu Datums- und Zeitberechnungen
 

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. 1: 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

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

Dezimalzahlen in Uhrzeiten verwandeln

Eine zweite Möglichkeit wäre die TEXT-Funktion:
=TEXT(A1/24; "hh:mm")

Noch eine Alternative

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. 2: ARBEITSTAG.INTL mit eigendefinierten Wochenenden

Mit der Funktion ARBEITSTAG.INTL lässt sich das Datum berechnen, dass 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

Die Funktion ARBEITSTAGE.INTL

Und erhält für den Wert 1 (= Samstag und Sonntag) z.B. folgendes Ergebnis:

Die Funktion ARBEITSTAGE.INTL (Forts.)

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

Eigene Freitage definieren

Excel-Quickie Nr. 3: 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

Sommerzeit und Winterzeit

So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

5 Gedanken zu “Excel-Quickies im Juli

  • Michael

    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

  • Andreas Unkelbach

    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

    • Martin Weiß Beitragsautor

      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