Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Datum/Zeit

Beitrag: Eine negative Stundenanzahl darstellen

Aufgabe
Wie kann man eine negative Stundenanzahl anzeigen, ohne die 1904-Datumseinstellung zu aktivieren, die ja bekanntlich beim Datenaustausch mit anderen System zu Fehlern führen kann.
Weiterhin eine Stundenanzahl von mehr als 24 Stunden und eine Zeitspanne über 24:00 Uhr hinaus.

       A               B               C       
1 08:15  08:15  21:30  
2 19:00  19:00  02:15  
3      
4 ##########  03:15   
5      
6 gewünscht:      
7 -10:45   27:15  04:45  
8      
9 Differenz  Summe  Zeitspanne 


A1-A2 ergibt den Gartenzaun.


Lösung
Das Wunschergebnis erhältst Du mit:
=WENN(A1 < A2;TEXT(A2-A1;"-hh:mm");A1-A2)
oder
=WENN(A1 < A2;"-";)&TEXT(ABS(A1-A2);"hh:mm")


Dieses Ergebnis ist nun im Textformat, um damit rechnen zu können, nimmst Du als Operator (A1-A2). Die Alternative benötigt eine Funktion mehr - dafür werden beide Zeiten (minus und plus) im Uhrzeitformat dargestellt.

Die Zeitsumme größer 24 erhältst Du mit dem benutzerdefinierten Zahlenformat
[hh]:mm

Die Zeit von - bis erhältst Du mit:
=WENN(C2 < C1;1-C1+C2;C2-C1)
oder kürzer:
=REST(C2-C1;1)


Erläuterung
Das Ergebnis von A1-A2 ist negativ und steht in H1 als Text mit obiger Formel.
Dazu willst Du z.B. Stunden in H2 addieren.
H1+H2 ergibt #WERT!
Dann also A1-A2+H2
bzw. wenn das Ergebnis auch negativ sein kann; - obige Formel analog:
=WENN(A1+H2-A2<0;TEXT(A2-A1-H2;"-[hh]:mm");A1-A2+H2)

Ergänzung von Christian Hapke:
Mit negativem Uhrzeiten weiterrechnen:


In Spalte B stehen positive und negative Uhrzeiten, die z. B. über obige Formeln ermittelt wurden. Nun soll mit diesen weitergerechnet werden.

{=TEXT(ABS(SUMME(WENN(LINKS(B1:B10)="-";TEIL(B1:B10;2;99)*-1;B1:B10*1)));WENN(SUMME(WENN(LINKS(B1:B10)="-";TEIL(B1:B10;2;99)*-1;B1:B10*1))<0;"-";)&"[hh]:mm:ss")}
Addiert alle Uhrzeiten aus B1:B10. Diese können als Text vorliegen (z. B. durch die obige TEXT-Funktion erzeugt) oder aber auch als Uhrzeit formatierte Zahlenwerte (also richtige Exceluhrzeiten) vorliegen! Zudem dürfen Leerzellen in dem zu summierenden Bereich vorhanden sein