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:
- Quickie 85: Uhrzeiten umrechnen (von Zeit in Dezimal und umgekehrt)
- Quickie 86: Arbeitstageberechnung mit eigendefinierten Wochenenden
- Quickie 87: 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;3;31)-WOCHENTAG(DATUM(Jahr;3;31))+1
Winterzeit:
=DATUM(Jahr;10;31)-WOCHENTAG(DATUM(Jahr;10;31))+1
Die Regel ist ja folgende:
Die Umstellung findet immer von Samstag auf Sonntag des letzten Märzwochenendes bzw. des letzten Oktoberwochenendes statt.
Mit Hilfe der WOCHENTAG-Funktion bestimmen wir die laufende Nummer des Wochentags des 31.03. bzw. 31.10.
Da ich in der Funktion keinen Parameter für den Typ angegeben habe, wird automatisch Typ 1 verwendet. Das bedeutet, Sonntag = 1, Montag = 2, Dienstag = 3 usw.
Wenn wir nun die eben berechneten Wochentage vom 31.03 bzw. 31.10 abziehen und dann nochmal 1 addieren, landen wir 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.
Wie kann ich in einer Tabelle mit Sonnenaufgangs- und -untergangszeiten den Tag herausfinden, der die gleichen Werte hat wie z.B. für heute Sonnenaufgang 6:31 und am 02.09. wieder. Sonnenuntergang heute 18:19 gibt es nicht nochmal, da wären der 16.10. mit 18:20 oder der 17.10. mit 18:18 am nächsten. Gibt es eine Formel, die das ergebnis findet?
Hallo Norbert,
ich fürchte, mit einer Formellösung dürfte das schwierig werden. Es müssten ja sowohl die Sonnenaufgänge als auch Sonnenuntergänge berücksichtigt werden. Und wenn es keine genaue Übereinstimmung gibt, dann die am nächsten liegenden Werte davor oder danach (und da will ich noch gar nicht davon anfangen, was im Zweifelsfall mehr Gewicht haben soll: Sonnenaufgang oder Sonnenuntergang).
Daher kann ich hier leider nicht mit einer Formel weiterhelfen und würde stattdessen ganz pragmatisch die Tabelle fallweise sortieren.
Schöne Grüße,
Martin