Datum & Uhrzeit |
|
|
Nachfolgend einige Beispiele zur Berechnung von Datums- und Uhrzeitwerten. Besonders für den Exceleinsteiger ist dieses Thema oft verwirrend. Dazu muss man wissen, was ein Datum oder eine Uhrzeit für Excel überhaupt ist.
Sieht man in einer Excelzelle ein Datum, so es nur ein Format, eine Darstellungsform. Tatsächlich steht in der Zelle lediglich eine Zahl.
|
|
Datum und Uhrzeit |
| A | B | C | 1 | | Format: TT.MM.JJJJ | Format: Standard | 2 | Excels erster Tag | 01.01.1900 | 1 | 3 | Weihnachten 2009 | 24.12.2009 | 40171 | |
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Excels Zeitrechnung beginnt am 01.01.1900. Dieses Datum ist für Excel die Zahl 1. Mit jedem weiteren Tag wird eine Eins dazugezählt. Heiligabend 2009 war beispielsweise der 40171zigste Tag seit Excels Zeitrechnung. Man kann dies sehen, indem man das Datumformat der Zelle in "Standard" ändert.
Aus dieser Erkenntnis ergeben sich nun einige Konsequenzen. Versuche ich beispielsweise aus einem Datum das Jahr zu extrahieren, indem ich die letzten 4 Stellen rechts davon abschneide, so ist dieser Versuch zum Scheitern verurteilt.
|
Datum und Uhrzeit |
| A | B | C | 14 | | Format: TT.MM.JJJJ | Versuch das Jahr auszulesen | 15 | Excels erster Tag | 01.01.1900 | 1 | 16 | Weihnachten 2009 | 24.12.2009 | 0171 | Formeln der Tabelle | C15 : =RECHTS(B15;4) C16 : =RECHTS(B16;4)
|
|
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Da in der Zelle ja nur eine Zahl steht, kann es so nicht gehen.
Gleiches gilt für die Uhrzeit. In der Excelzelle sehen wir eine Uhrzeit aber tatsächlich steht in der Zelle nur eine Zahl kleiner als 1.
|
Datum und Uhrzeit |
| A | B | C | 6 | | Format: hh:mm:ss | Format: Standard | 7 | Ein Tag beginnt | 00:00:00 | 0 | 8 | Mittag | 12:00:00 | 0,5 | 9 | Ein Tag endet | 23:59:59 | 0,999988426 | |
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Ein Zellwert mit Datum + Uhrzeitangabe ist also eine Zahl mit Nachkommastellen.
Aus 40171,75 wird mit Zellformat TT.MM.JJJJ hh:mm das Datum/Uhrzeit 24.12.2009 18:00.
Hier nun einige Formeln um das Jahr, den Monat und Tag aus einem Datum zu ermitteln. Anschließend wird aus Tag, Monat, Jahr wieder ein Datum zusammengesetzt.
|
Datum |
| A | B | C | D | 1 | Weihnachten 2009 | Jahr | Monat | Tag | 2 | 24.12.2009 | 2009 | 12 | 24 | 3 | | | | | 4 | | | | | 5 | | | | | 6 | Weihnachten 2009 | Jahr | Monat | Tag | 7 | 24.12.2009 | 2009 | 12 | 24 | Formeln der Tabelle | B2 : =JAHR(A2) C2 : =MONAT(A2) D2 : =TAG(A2) A7 : =DATUM(B7;C7;D7)
|
|
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Beim Zusammensetzen ist darauf zu achten, dass die Syntax = Datum(Jahr;Monat;Tag) ist, also anders herum.
Analog dazu die Zeitfunktionen
|
Uhrzeit |
| A | B | C | D | 1 | Uhrzeit | Stunden | Minuten | Sekunden | 2 | 12:15:30 | 12 | 15 | 30 | 3 | | | | | 4 | | | | | 5 | | | | | 6 | Uhrzeit | Stunden | Minuten | Sekunden | 7 | 12:15:30 | 12 | 15 | 30 | Formeln der Tabelle | B2 : =STUNDE(A2) C2 : =MINUTE(A2) D2 : =SEKUNDE(A2) A7 : =ZEIT(B7;C7;D7)
|
|
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Nachfolgend einige Formeln zur Berechnung von Datum & Uhrzeit
|
Datum_Uhrzeitfunktionen |
| A | B | C | D | E | 1 | Datumsfunktionen |
---|
2 | Wert [ TT.MM.JJJJ ] | Bemerkung | Wert2 [ TT.MM.JJJJ ] | Formel | Zellformat | 3 | | Erzeugt das aktuelle Datum | | 04.04.2010 | TT.MM.JJJJ | 4 | | Erzeugt das aktuelle Datum + Uhrzeit | | 04.04.2010 14:20 | TT.MM.JJJJ hh:mm | 5 | 04.04.2010 | Einen Tag dazu zählen | | 05.04.2010 | TT.MM.JJJJ | 6 | 04.04.2010 | Ermittelt den Wochentag aus einem Datum | | 7 | Standard | 7 | 04.04.2010 | Aus Datum Wochentag als Text anzeigen | | Sonntag | Standard | 8 | 04.04.2010 | Aus Datum Monat als Text anzeigen | | April | Standard | 9 | 04.04.2010 | Aus Datum Kalenderwoche ermitteln | | 13 | Standard | 10 | 04.04.2010 | Ermittelt die Differenz zwischen 2 Datumswerten in Tage | 04.04.2011 | 365 | Standard | 11 | 04.04.2010 | Ermittelt die Differenz zwischen 2 Datumswerten in Monate | 04.04.2011 | 12 | Standard | 12 | 04.04.2010 | Ermittelt die Differenz zwischen 2 Datumswerten in Jahre | 04.04.2011 | 1 | Standard | 13 | | | | | | 14 | | | | | | 15 | | | | | | 16 | Uhrzeitfunktionen |
---|
17 | Wert [ hh:mm ] | Bemerkung | Wert2 [ hh:mm ] | Formel | Zellformat | 18 | | Wandelt einen Text in eine Dezimalzahl/Uhrzeit | | 0,5 | Standard | 19 | 12:15 | Wandelt eine Uhrzeit in eine Dezimalzeit | | 12,25 | Standard | 20 | 12,50 | Wandelt eine Dezimalzeit in eine Uhrzeit | | 12:30 | mm:ss | 21 | 00:01 | Wandelt eine Minute in eine Industrieminute | | 1,67 | Zahl mit 2 Nachkommastellen | 22 | 01:00 | Wandelt eine Stunde in Industrieminuten | | 100,00 | Zahl mit 2 Nachkommastellen | 23 | 12:42 | Rundet jeweils auf die nächste 1/4 Stunde hoch | | 12:45 | hh:mm | 24 | 22:00 | Berechnet die Differenz zwischen 2 Uhrzeiten auch über die 24-Stundengrenze | 06:00 | 08:00 | hh:mm | Formeln der Tabelle | D3 : =HEUTE() D4 : =JETZT() D5 : =A5+1 D6 : =WOCHENTAG(A6;2) D7 : =TEXT(A7;"TTTT") D8 : =TEXT(A8;"MMMM") D9 : =KÜRZEN((A9-WOCHENTAG(A9;2)+11-("1/"&JAHR(A9+4-WOCHENTAG(A9;2))))/7) D10 : =DATEDIF(A10;C10;"d") D11 : =DATEDIF(A11;C11;"m") D12 : =DATEDIF(A12;C12;"y") D18 : =ZEITWERT("12:00") D19 : =A19*24 D20 : =A20/24 D21 : =A21*24*100 D22 : =A22*24*100 D23 : =OBERGRENZE(A23;"0:15") D24 : =REST(C24-A24;1)
|
|
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Abschließend noch etwas zum Thema "Minuszeiten". Excel kann keine Minusuhrzeiten darstellen. Unter Extras - Optionen - Berechnung läßt sich die Funktion "1904-Datumswerte" aktivieren. Von dieser Einstellung ist aber m.E nach abzuraten, da sich alle Datumswerte um 4 Jahre verschieben.
Bei folgendem Beispiel ergibt die Differenz einer Datumsberechnung ein Minusergebnis und Excel zeigt den "Gartenzaun" als Hinweis, dass ein Wert nicht dargestellt werden kann.
|
Minusstunden |
| A | B | C | 1 | Soll-Arbeitstunden | Ist-Abeitsstunden | Differenz | 2 | 160:00:00 | 130:00:00 | ################# | 3 | 160:00:00 | 200:00:00 | 40:00:00 | |
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Das Ergebnis in Zelle C3 ist -30 Stunden. Das heißt aber nicht, dass Excel nicht das korrekte Ergebnis berechnet hat. Formatiert man die Zelle in das Zellfomat "Standard", so zeigt Excel -1,25 an. Mit dieser Spalte läßt sich also weiter rechnen. Somit ist eine Möglichkeit mit Minuszeiten zu verfahren, der Einsatz einer Hilfsspalte, in der die Minuszeit im Textformat angezeigt wird. Die Spalte mit dem Gartenzaun, hier Spalte C kann dann ausgeblendet werden und für weitere Berechnungen dienen.
|
Minusstunden |
| A | B | C | D | 1 | Soll-Arbeitstunden | Ist-Abeitsstunden | Differenz | Differenz im Textformat | 2 | 160:00:00 | 130:00:00 | ################# | - 30:00 | 3 | 160:00:00 | 200:00:00 | 40:00:00 | 40:00 | 4 | | | | | 5 | | Saldo: | 10:00:00 | | Formeln der Tabelle | C2 : =B2-A2 D2 : =TEXT(ABS(A2-B2);WENN(B2<A2;"- ";" ") &"[hh]:mm") C3 : =B3-A3 D3 : =TEXT(ABS(A3-B3);WENN(B3<A3;"- ";" ") &"[hh]:mm") C5 : =SUMME(C2:C3)
|
|
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Zum Schluß noch eine häufige gestellte Frage. Es sollen mehrere Zeitwerte addiert werden, wobei das Ergebnis größer als 24 Stunden ist. Das Zellformat hh:mm kann nicht über 24-Stunden darstellen und führt somit zu einer falschen Anzeige. Hier muss das Format: [hh]:mm gewählt werden.
|
Tabelle1 |
| A | B | C | 1 | Name | Dauer | | 2 | Toni | 11:00 | | 3 | Tim | 10:00 | | 4 | Theo | 11:00 | | 5 | | | | 6 | Gesamtstunden: | 08:00 | <-- Zellformat hh:mm Fehlerhafte Anzeige | 7 | Gesamtstunden: | 32:00 | <-- Zellformat [hh]:mm Korrekte Anzeige | Formeln der Tabelle | B6 : =SUMME(B2:B4) B7 : =SUMME(B2:B4)
|
|
|
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download