Problem bei Formatierung [hh]:mm im Excel?
Ich habe in der Spalte B meine Soll Stunden und in der C Spalte die Ist Stundenanzahl. Nun möchte ich die Stundenabweichung in der Spalte D berechnen indem ich die Ist- von den Sollstunden abziehe. Wichtig wäre mir das Format [hh]:mm beizubehalten. Wenn ich diese Formatierung auswähle kommen aber nur diese # Zeichen. Kann Excel die Abweichung in diesem Format garnicht anzeigen?
Danke schon mal!!
3 Antworten
Ich kann das Problem nicht nachstellen. Die Zellformatierung scheint doch bei Deinem Beispiel überall gleich zu sein und in den anderen Zellen wird ja richtig gerechnet -> oder ist das manuell?
Ist das h auch über die Formatierung erzeugt oder steht das wirklich hinter der Zahl? Dann wäre klar, warum die Berechnung schiefgeht.
Zellen formatieren > Benutzer definiert > Typ hh:mm "h" erzeugt das H quasi nur an der Oberfläche, damit bleibt es eine Zahl und die Berechnung sollte gehen.
Hier ein Ereignis-µ, welches das Format immer hin-und-herschaltet, allerdings unter realer Wertänderung von Zeitstunden [Einheit: 1 Tag = 24h = 1] und Industriestunden [Einheit: 1 Stunde = 1]:
Sub Worksheet_selectionchange(ByVal Target As Range) 'Wechselt zwischen NumberFormat 0,00 und [h]:mm
If Not Intersect(Target, Range("e11:e15")) Is Nothing Then
Debug.Print Target.NumberFormat ' î Î î Bereich anpassen!
If InStr(1, Target.NumberFormat, "[h]") > 0 Then Target.Value = Target.Value * 24: Target.NumberFormatLocal = "0,00 ""Std""": End
If InStr(1, Target.NumberFormat, "0.") > 0 Then Target.Value = Target.Value / 24: Target.NumberFormatLocal = "[h]:mm ""h""": End
MsgBox "falsches Format?"
End If: End Sub ' nicht Worksheet_Change: das ruft sich immer wieder selbst auf durch die Aktion /24 bzw *24
Für Berechnungen ist das sehr gefährlich, aber handhabbar. Evtl die Ergebniszelle mit in den Bereich der umzuwandelnden (Einzel!-)Zellen einbeziehen. (i.F. wird auf E11 bezogen:)
Evtl kann eine zusätzliche Kontrollfunktion noch etwas mehr Sicherheit geben:
=ZELLE("Format";E11)
Diese Formel in einem extra Bereich (exBer) für jede einzelne an der Rechnung beteiligte Zelle setzen. Das liefert -entgegen den Angaben in der Hilfe!-
S, wenn Zeitformat, und
F2, wenn Industriestundenformat.
Kontroll-Fo:
=Wenn(Und(Zählenwenn(exBer;"S")>0;Zählenwenn(exBer;"F2")>0;" inkompatible Formate ! ";" alle Zellen haben gleiches Format")
Das zweite und dritte if sind -wie bei mir häufig- das einzeilige if, das kein else benötigt und deshalb kein end if braucht. Mehrere Befehle sind jeweils mit : aneinandergehängt (Dabei kein Zeilenumbruch erlaubt vor : End ! (wenn kürzere Zeilen zur Darstellung gewünscht, dann mit Leerzeichen, dann underbar, dann Enter,
also so: ... Then ...: ... _
...: ...: End) (anstatt End geht auch Exit Sub oder in einer Schleife zB Exit FOR
"...indem ich die Ist- von den Sollstunden abziehe."
Du rechnest Ist - Soll. Das ist das Eine.
Das Andere ist, dass selbst wenn du Soll - Ist rechnen würdest, negative Werte heraus kommen könnten, nämlich dann, wenn du über das Soll hinaus gearbeitet hast.
Dann bekommst du den dir bekannten Fehler (#). Diesen umgehst du, in dem du die Excel-Optionen veränderst:
Datei → Optionen → Erweitert → Beim Berechnen der Arbeitsmappe → (x) 1904 Datumswerte verwenden
In den "funktionierenden" Zellen ist ein Dezimal-Format, kein Uhrzeitenformat. Excel kann in der Standardeinstellung (1900-Datumsformat) keine negativen Zeiten darstellen (außer mit Umwegen via Textfunktionen).