16.04.2014, 18:26
Hallo,
ein Thema das immer wieder für Probleme sorgt: Die Berechnung und Darstellung von negativen Zeiten oder Minusstunden.
Im vorstehenden Beispiel haben wir eine typische, stark vereinfachte, Arbeitszeitberechnung.
Die Spalten A bis E sind als Uhrzeit, die Spalte F und G als Zahl formatiert.
In Spalte A wird der Arbeitsbeginn und in Spalte B das Arbeitsende eingetragen. In Spalte C wird die Anwesenheitszeit berechnet und in Spalte D steht die vorgegebene Anwesenheitszeit. In Zeile 2 ist die tatsächliche Arbeitszeit geringer, in Zeile 3 sind Überstunden angefallen, so dass in der Summe 3 Überstunden geleistet worden sind.
In E2 sehen wir den berühmt-berüchtigten Gartenzaun. Excel kann diese negative Zeit nicht darstellen, die Summenberechnung in E5 zeigt aber, dass Excel mit diesem Wert ohne Probleme rechnen kann.
Welche Möglichkeiten haben wir um dieses optische Problem zu vermeiden?
Möglichkeit 1 ist in Spalte F dargestellt. Die Zeiten werden, durch Multiplikation mit 24, einfach in eine Dezimalzahl umgerechnet. Wir rechnen hier mit der sogenannten Industriezeit.
Möglichkeit 2 ist in Spalte G dargestellt. Durch eine Formel erreichen wir eine korrekte Darstellung. Bei der Formel wird die Funktion Text() verwendet und diese Funktion, gibt wie der Name schon andeutet, Text zurück. Mit Text kann nicht gerechnet werden und deswegen ist die Summe der Spalte G auch 0.
Man kann jetzt natürlich hingehen und für Berechnungen die Spalte E heranziehen, diese Spalte ausblenden und die Spalte G für die Darstellung verwenden:
Gegenüber dem obigen Beispiel wurde lediglich die Formel in G5 geändert.
Eine dritte Möglichkeit möchte ich noch erwähnen:
Es gibt in Excel in den erweiterten Optionen die Möglichkeit 1904-Datumswerte zu verwenden. Diese Umstellung hat aber gravierende Folgen und will gut überlegt sein.
Standardmäßig wird dem Datum 01.01.1900 (mit diesem Datum beginnt die Microsoft-Zeitrechnung) der Wert 1 zugewiesen, im 1904-System bekommt der 01.01.1904 diesen Wert. Das bedeutet, daß alle Daten des Standarddatumssystems um 4 Jahre und 1 Tag verschoben werden, d.h. durch das Verwenden der 1904-Option werden auch alle in der Mappe bereits enthaltenen Daten um 4 Jahre und 1 Tag verschoben.
Bevor man also auf das 1904-Datumssystem umstellt, sollte man sich Gedanken darüber machen, ob man dieses wirklich benötigt oder besser einen anderen Weg wählt.
Die Unterschiede zwischen dem 1900-Datumsystem und dem 1904-Datumsystem würden den Rahmen dieses Tipps sprengen.
Für weiterführende Informationen empfiehlt sich dieser KB-Artikel
ein Thema das immer wieder für Probleme sorgt: Die Berechnung und Darstellung von negativen Zeiten oder Minusstunden.
| Arbeitsblatt mit dem Namen 'Tabelle2' | |||||||
| A | B | C | D | E | F | G | |
| 1 | Beginn | Ende | Ist-Std | Soll-Std | Differenz | Industriezeit | Text |
| 2 | 08:00 | 11:00 | 03:00 | 04:00 | ########## | -1,00 | -01:00 |
| 3 | 08:00 | 16:00 | 08:00 | 04:00 | 04:00 | 4,00 | 04:00 |
| 4 | |||||||
| 5 | 03:00 | 3,00 | 00:00 | ||||
| Zelle | Formel |
| C2 | =REST(B2-A2;1) |
| E2 | =C2-D2 |
| F2 | =E2*24 |
| G2 | =TEXT(ABS(C2-D2);WENN(C2<D2;"-";) &"hh:mm") |
| C3 | =REST(B3-A3;1) |
| E3 | =C3-D3 |
| F3 | =E3*24 |
| G3 | =TEXT(ABS(C3-D3);WENN(C3<D3;"-";) &"hh:mm") |
| E5 | =SUMME(E2:E3) |
| F5 | =SUMME(F2:F3) |
| G5 | =SUMME(G2:G3) |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Im vorstehenden Beispiel haben wir eine typische, stark vereinfachte, Arbeitszeitberechnung.
Die Spalten A bis E sind als Uhrzeit, die Spalte F und G als Zahl formatiert.
In Spalte A wird der Arbeitsbeginn und in Spalte B das Arbeitsende eingetragen. In Spalte C wird die Anwesenheitszeit berechnet und in Spalte D steht die vorgegebene Anwesenheitszeit. In Zeile 2 ist die tatsächliche Arbeitszeit geringer, in Zeile 3 sind Überstunden angefallen, so dass in der Summe 3 Überstunden geleistet worden sind.
In E2 sehen wir den berühmt-berüchtigten Gartenzaun. Excel kann diese negative Zeit nicht darstellen, die Summenberechnung in E5 zeigt aber, dass Excel mit diesem Wert ohne Probleme rechnen kann.
Welche Möglichkeiten haben wir um dieses optische Problem zu vermeiden?
Möglichkeit 1 ist in Spalte F dargestellt. Die Zeiten werden, durch Multiplikation mit 24, einfach in eine Dezimalzahl umgerechnet. Wir rechnen hier mit der sogenannten Industriezeit.
Möglichkeit 2 ist in Spalte G dargestellt. Durch eine Formel erreichen wir eine korrekte Darstellung. Bei der Formel wird die Funktion Text() verwendet und diese Funktion, gibt wie der Name schon andeutet, Text zurück. Mit Text kann nicht gerechnet werden und deswegen ist die Summe der Spalte G auch 0.
Man kann jetzt natürlich hingehen und für Berechnungen die Spalte E heranziehen, diese Spalte ausblenden und die Spalte G für die Darstellung verwenden:
| Arbeitsblatt mit dem Namen 'Tabelle2' | |||||
| A | B | C | D | G | |
| 1 | Beginn | Ende | Ist-Std | Soll-Std | Text |
| 2 | 08:00 | 11:00 | 03:00 | 04:00 | -01:00 |
| 3 | 08:00 | 16:00 | 08:00 | 04:00 | 04:00 |
| 4 | |||||
| 5 | 03:00 | ||||
| Zelle | Formel |
| G5 | =E5 |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Gegenüber dem obigen Beispiel wurde lediglich die Formel in G5 geändert.
Eine dritte Möglichkeit möchte ich noch erwähnen:
Es gibt in Excel in den erweiterten Optionen die Möglichkeit 1904-Datumswerte zu verwenden. Diese Umstellung hat aber gravierende Folgen und will gut überlegt sein.
Standardmäßig wird dem Datum 01.01.1900 (mit diesem Datum beginnt die Microsoft-Zeitrechnung) der Wert 1 zugewiesen, im 1904-System bekommt der 01.01.1904 diesen Wert. Das bedeutet, daß alle Daten des Standarddatumssystems um 4 Jahre und 1 Tag verschoben werden, d.h. durch das Verwenden der 1904-Option werden auch alle in der Mappe bereits enthaltenen Daten um 4 Jahre und 1 Tag verschoben.
Bevor man also auf das 1904-Datumssystem umstellt, sollte man sich Gedanken darüber machen, ob man dieses wirklich benötigt oder besser einen anderen Weg wählt.
Die Unterschiede zwischen dem 1900-Datumsystem und dem 1904-Datumsystem würden den Rahmen dieses Tipps sprengen.
Für weiterführende Informationen empfiehlt sich dieser KB-Artikel
Gruß
Peter
Peter