Tipps für Datum und Zeit
Da dieser Bereich innerhalb von Excel mit vielen Problemen behaftet ist, wird diesem eine Extraseite gewidmet.

Schnellsuche:

Datum

Microsoft Excel speichert Datumsangaben als sogenannte fortlaufende Zahlen und Uhrzeitangaben als Dezimalbrüche, da diese als Bruchteile eines Tages angesehen werden können. Datums- und Uhrzeitangaben sind Werte und können daher addiert, subtrahiert und in anderen Berechnungen verwendet werden. Um beispielsweise den Abstand zwischen zwei Datumsangaben zu ermitteln, können Sie die Daten voneinander subtrahieren. Sie können eine Datums- oder Uhrzeitangabe als fortlaufende Zahl oder als Dezimalbruch anzeigen, indem Sie das Format der Zelle, die das Datum oder die Uhrzeit enthält, in das Format Standard ändern.

Microsoft Excel 97 unterstützt zwei Datumswertesysteme: das 1900- und das 1904-Datumswertesystem. In Microsoft Excel 97 für Windows wird standardmäßig das 1900-Datumswertesystem verwendet. Um zum 1904-Datumswertesystem zu wechseln, klicken Sie im Menü Extras auf Optionen, klicken Sie auf die Registerkarte Berechnen, und aktivieren Sie dann das Kontrollkästchen 1904-Datumswerte.
Die folgende Tabelle enthält für beide Datumswertesysteme das erste und das letzte Datum sowie die entsprechenden seriellen Datumswerte.

Datumswertesystem Erstes Datum    Letztes Datum
1900 1. Januar 1900
(serieller Wert 1)
31. Dezember 9999
(serieller Wert 2958525)
1904 2. Januar 1904
(serieller Wert 1)
31. Dezember 9999
(serieller Wert 2957063)

Achtung: Bei Verknüpfungen von Tabellen mit unterschiedlichen Datumswertesystemen muss  das Datum aus dem 1904-Datumswertesystem um 1462 Tage korrigiert werden.

Kontrollmöglichkeit im VBA-Editor ob die 1904-Datumswerte aktiviert sind:


Anmerkung:
Wenn Sie bei der Eingabe eines Datums in Microsoft Excel 97 für die Jahreszahl nur zwei Ziffern eingeben, interpretiert Microsoft die Jahresangabe folgendermaßen:

·    als Jahreszahl des Zeitraums 2000 bis 2029, wenn Sie für das Jahr die Werte 00 bis 29 eingeben. Beispielsweise interpretiert Microsoft Excel die Eingabe 28.05.19 als den 28. Mai 2019.
·    als Jahreszahl des Zeitraums 1930 bis 1999, wenn Sie für das Jahr die Werte 30 bis 99 eingeben. Beispielsweise interpretiert Microsoft Excel die Eingabe 28.05.91 als den 28. Mai 1991.

Problem Formel Bemerkungen
Zeiten über den Tageswechsel berechnen 01.01.1999 22:00  -  02.01.1999 02:00 Mit Datum eingeben
=A1+1-B1 Start+1-Ende
=wenn(A1<B1;A1+1-B1;A1-B1) Wenn(Start<Ende;Start+1-Ende;Start-Ende)
Negative Stundenanzahl - Nur im 1904-Datumswertesystem darstellbar
Das aktuelle Datum eintragen (statisch) - Shortcut: Strg und .  (Punkt)
Die aktuelle Zeit  eintragen (statisch) - Shortcut: Strg und (Doppelpunkt)
oder:
Shortcut: Strg & Umschalttaste & Punkt
Das aktuelle Datum eintragen (aktualisiert sich) =heute()  
Die aktuelle Zeit eintragen (aktualisiert sich) =jetzt()  
Ort und Datum wie in Brief ="Rottweil, den " &TEXT(HEUTE();"TT.MM.JJJJ")  
Datum Anhand der KW bestimmen =KWDatum(A1;HEUTE()) Benutzerdefinierte Funktion:
Function KWDatum(Kalenderwoche As Integer, Jahr As Date) As Date
tYear = DateSerial(Year(Jahr), 1, 1)
KWDatum = tYear + 1 - Application.WeekDay(tYear, 2) + (Kalenderwoche - 1) * 7
End Function
Jahre hinzuzählen =DATUM(JAHR(A1)+B1;MONAT(A1);TAG(A1)) Ausgangsbasis:
Startdatum in Zelle A1
zu addierender Betrag in Zelle B1

Der Trick bei den Zeiten (siehe unten) funktioniert beim Datum nicht.

Monate hinzuzählen =DATUM(JAHR(A1);MONAT(A1)+B1;TAG(A1))
Tage hinzuzählen =DATUM(JAHR(A1);MONAT(A1);TAG(A1)+B1)
Stunden hinzuzählen =ZEIT(STUNDE(A1)+B1;MINUTE(A1);SEKUNDE(A1)) Ausgangsbasis:
Startzeit in Zelle A1
zu addierender Betrag in Zelle B1

Die kurze Formel funktioniert beim Datum nicht immer richtig!

=A1+ZEIT(B1;;)
Minuten hinzuzählen =ZEIT(STUNDE(A1);MINUTE(A1)+B1;SEKUNDE(A1))
=A1+ZEIT(;B1;)
Sekunden hinzuzählen =ZEIT(STUNDE(A1);MINUTE(A1);SEKUNDE(A1)+B1)
=A1+ZEIT(;;B1)
Ostern ermitteln =DM((TAG(MINUTE(Jahr/38)/2+55 )&".4."&Jahr)/7;)*7-6 Hetterich-Formel
Buß- und Bettag ermitteln =DATUM(Jahr;12;25)-WOCHENTAG(DATUM(Jahr;12;25);2)-4*7-4 Suche den Mittwoch vor dem 0. Advent.
Jubiläumsdatum ermitteln =WENN(JAHR(HEUTE())-JAHR(A1)=16;DATUM(JAHR(A1)+16;MONAT(A1);TAG(A1));"keine Ehrung") Es werden nur die Jubilare dieses Jahres angezeigt, der Rest erhält den Text in Anführungszeichen   
Mit Komma falsch geschriebene Zeiten (Minute,Sekunde) umformen in Sekunden =KÜRZEN(A1)*60+(A1-KÜRZEN(A1))*100  
=GANZZAHL(A1)*60+(A1-GANZZAHL(A1))*100
=ABRUNDEN(A1;0)*60+(A1-ABRUNDEN(A1;0))*100
=100*A1-40*KÜRZEN(A1)
Umwandeln
Industrieminuten in richtige Minuten
=(GANZZAHL(A1)&":"&(A1-GANZZAHL(A1))*60)*1  
Umwandeln
richtige Minuten in Industrieminuten
=STUNDE(A1)+MINUTE(A1)/60  
Uhrzeit auf 15 Minuten runden =RUNDEN(A1*96;)/96 auf-/abrunden nach Rundungsregeln
=AUFRUNDEN(A1*96;)/96 rundet immer auf die nächsten 15 Minuten
Datumsdifferenzen =DATEDIF(A1;B1;"d") Tage Undokumentierte Funktion

=DATEDIF(Start;Ende;"d")
Das 3. Argument als Zeichenfolge legt die Art des Resultates fest. Z.B.: y, "m", "d", "ym", oder "yd". Die'ersten 3 sind klar. "ym" ergibt (Zahl der Monate MOD 12), "yd" ergibt (Zahl der Tage MOD 365).

=DATEDIF(A1;B1;"m") Monate
=DATEDIF(A1;B1;"y") Jahre
=DATEDIF(A1;B1;"yd") Tage im Jahr
=DATEDIF(A1;B1;"ym") Monate im Jahr
Lebensalter berechnen =DATEDIF(A1;HEUTE();"y")  
=WENN(WENN(DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1))>HEUTE();DATEDIF(HEUTE();DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1));"d");DATEDIF(HEUTE();DATUM(JAHR(HEUTE())+1;MONAT(A1);TAG(A1));"d"))<7;"Nur noch "&WENN(DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1))>HEUTE();DATEDIF(HEUTE();DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1));"d");DATEDIF(HEUTE();DATUM(JAHR(HEUTE())+1;MONAT(A1);TAG(A1));"d"))&" Tage bis zum Geburtstag";"Mit dem Geburtstagsgeschenkeinkauf hat's noch "&WENN(DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1))>HEUTE();DATEDIF(HEUTE();DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1));"d");DATEDIF(HEUTE();DATUM(JAHR(HEUTE())+1;MONAT(A1);TAG(A1));"d"))&" Tage Zeit") Lebensalter + Tage bis zum Geburtstag
Aus Zahlen ohne Trennung Zeit bilden =ZEIT(WENN(A1>999;LINKS(A1;2);LINKS(A1));RECHTS(A1;2);0) z.B. 702 ergibt 7:02
=ZEIT(GANZZAHL(A1);REST(A1;1)*60;)
Wochentag berechnen =WAHL(WOCHENTAG(A1);"So";"Mo";"Di";"Mi";"Do";"Fr";"Sa") Geht auch mit Formatierung "TTT", dann kann aber nicht gerechnet werden
Tabellen nach Geburtstag sortieren =DATUM(0;MONAT(A1);TAG(A1)) In Hilfsspalte Formel eingeben und nach dieser sortieren
Ort aus Zelle A1 und Datum =A1 &", den " & TEXT(HEUTE();"T. MMMM JJJJJ") Wenn Ort in A1 steht
Kalenderwoche =DINKw(A1) Die in den  Analysefunktionen  enthaltene Funktion "Kalenderwoche" entspricht nicht der deutschen DIN KW
Abhilfe:
Benutzerdefinierte Funktion:
Function DINKw(Tag)
    DINKw = DatePart("ww", Tag, vbMonday, vbFirstFourDays)
End Function
Schaltjahr feststellen =WENN(REST(JAHR(A1);400)=0;"Schaltjahr";WENN(UND(REST(JAHR(A1);4)=0;REST(JAHR(A1);100)<>0);"Schaltjahr";"365 Tage")) Hier wird das Datum in Zelle A1 Geprüft
=Schaltjahr() benutzerdefinierte Funktion:

Function Schaltjahr(Jahr)
J = Year(Jahr)
If (J Mod 4) = 0 And (J Mod 100) <> 0 Or (J Mod 400) = 0 Then
Schaltjahr = "Schaltjahr"
Else
Schaltjahr = "kein Schaltjahr"
End If
End Function

 

Wie viele Tage hat der Monat =DATUM(1;MONAT(A1)+1;JAHR(A1))-DATUM(1;MONAT(A1);JAHR(A1))  
     

Beispieldatei: Spielereien rund um die Datumsberechnung (mit Makro wegen automatischer Geburtsdatumseingabeeingabe)



Meine aktuellsten Exceltipps finden Sie hier: