Wenn mir irgendjemand irgendwie helfen kann, warum ich negative Zahlen statt 40 habe, was eigentlich der Fall sein sollte, dann helft mir bitte. Ich wäre so dankbar. Danke!
Antwort1
Sie haben für die Arbeitsmappe unter Datei > Erweitert > Abschnitt „Beim Berechnen dieser Arbeitsmappe“ (bis zum Ende nach unten scrollen) korrekt „Datensystem 1904 verwenden“ ausgewählt.
Dies ist erforderlich, damit Zeiten als negative Werte berechnet werden können. (Versuchen Sie, es auszuschalten, und Sie werden sehen, dass die negativen Werte durchgängig in Zahlen umgewandelt werden.)
Aber in B16 und C16 wird beispielsweise die Zeit einfach um 18:00 und 01:00 eingegeben, ohne Datum. 01:00 - 18:00 ist tatsächlich eine negative Zeit. Ungefähr -0,71, wenn sie als Zahl angezeigt wird.
Die Lösung besteht darin, jede einzelne End-Start-Zeitberechnung so zu ändern, dass sie auch nach Mitternacht möglich ist. Ersetzen Sie also „C16-B16“ durch „IF(C16-B16<0,C16-B16+1,C16-B16)“.
Sie müssten dies für jeden Tag der Woche tun, was die Formel ziemlich lang macht.
Erwägen Sie, zwischen den Tagen eine zusätzliche Spalte hinzuzufügen, um die an diesem Tag gearbeiteten Stunden anzuzeigen. Dann muss die Summe nur noch aus diesen Zellen bestehen.
Edit: war schneller!
Nochmals bearbeiten: Sie sollten +1 wie in meinem Beispiel verwenden und nicht +24 wie im vorherigen Beitrag, da die Einheit Tage und nicht Stunden ist.
Letzte Änderung: Eine viel kürzere Lösung besteht darin, „C16-B16“ durch „MOD(C16-B16,1)“ zu ersetzen. Dies funktioniert, indem nur der Bruchteil der Zeit beibehalten wird. Bei Zeiten entspricht eine Dezimalzahl 1 24 Stunden.
Antwort2
Die übliche Formel zum Überschreiten von 24 lautet:
=EndTime - StartTime +(EndTime < StartTime)
All das in einer Formel zu kombinieren, um alle Wochentage zu summieren, ist zwar umständlich, aber machbar. Es erfordert eine Formel mit Array-Eingabe, da Sie jedes Paar einzeln testen müssen.
Die folgende Formel nutzt Ihr Setup, bei dem sich alle Endzeiten in Spalten mit geraden Zahlen und die Startzeiten in Spalten mit ungeraden Zahlen befinden.
Beachten Sie, dass sich die beiden erstellten Arrays um eine Spalte unterscheiden. In Excel-Versionen ab 2007 können Sie mit der Funktion ISODD/ISEVEN direkt auf ODD/EVEN testen.
Diese Formel mussArray-Eintrag:
=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24
oder mit ISODD und ISEVEN:
=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24
Oder noch kürzer, aber schwerer zu verstehen, da wir die MOD-Funktion verwenden, um nur die Bruchteile beizubehalten:
=SUM(
MOD(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
1))
*24
ZuArray-Eingabeeine Formel, nachdem Sie die Formel in die Zelle oder die Formelleiste eingegeben haben, halten Sie Strg-Umschaltbeim SchlageneingebenWenn Sie dies richtig gemacht haben, setzt Excel Klammern {...} um die Formel.
Ich habe die Formel für die Zeile in Ihrem Screenshot berechnet. Das Ergebnis ist 37,9833 unter Verwendung Ihrer Zahlen
Antwort3
Ihr Problem entsteht, wenn Sie nach Mitternacht gehen. Nehmen Sie zum Beispiel die erste Zeile am Freitag, Samstag und Sonntag. Jeder Tag endet um 2:00 Uhr. Wenn Sie beispielsweise am Freitag die Zeiten subtrahieren, erhalten Sie Freitag 18:00 Uhr minus 2:00 Uhr FREITAG, was vor 18:00 Uhr liegt.
Um das Negative zu vermeiden, aber dennoch das ansprechende Erscheinungsbild Ihres Diagramms beizubehalten, sollten Sie eine IF-Anweisung verwenden. Um für Freitag zu berechnen, verwenden Sie:
=IF(K3 < J3, K3 - J3 + 24, K3 - J3)
Die IF-Anweisung besteht aus drei Abschnitten.
(1) Die Voraussetzung
K3 < J3
Dies prüft, ob K3 (die Endzeit) kleiner als J3 (die Startzeit) ist. Wenn ja, wird eine negative Zahl zurückgegeben (die Zeit 2:00 Uhr ist „kleiner als“ 18:00 Uhr).
(2) wenn Bedingung erfüllt ist
K3 - J3 + 24
Wenn die Bedingung erfüllt ist, verwendet Excel diese Gleichung. Ich habe sie so geschrieben, dass 24 addiert wird, um eventuell auftretende negative Werte auszugleichen.
(3) wenn Bedingung falsch ist
K3 - J3
Wenn die Bedingung erfüllt ist, verwendet Excel diese Gleichung.
Zusammenfassung
Verwenden Sie die bereitgestellte IF-Anweisung anstelle der Gleichung, die Sie zum Berechnen der Stunden für jeden Tag verwendet haben.
Antwort4
Ich hatte ein ähnliches Problem beim Berechnen von Zeiten, die manchmal über Mitternacht hinausgingen, ohne dass Tages- oder Datumsinformationen vorhanden waren. Meine Startzeiten waren abends und zogen sich manchmal bis in den nächsten Tag hinein, also habe ich ein paar Formeln kombiniert, damit es für mich richtig ausging:
=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))
(Beachten Sie, dass ich aus Formatierungsgründen gezwungen war, hier nach dem Kleiner-als-Zeichen ein Leerzeichen einzufügen – in meiner Formel verwende ich an dieser Stelle kein Leerzeichen.)
Es ist ein einfaches „Wenn“, wobei in meinem Fall D4 die Endzeit und C4 die Startzeit ist. Wenn die Endzeit kleiner als die Startzeit ist, muss es nach Mitternacht beendet sein. Die mittlere Anweisung gibt die gesamte Auftragszeit an, wobei die Zeit nach Mitternacht (+24) korrigiert wird. Das Ende zeigt die Auftragszeit, wenn Sie nur die normale Subtraktion benötigen. Ich habe festgestellt, dass ohne „TEXT“ und Zeitformat die +24-Mathematik als Dezimalwert angezeigt würde.