☰

Formel zur Berechnung der Dauer für Zeiten, die sich über zwei Tage erstrecken können

tag-icon • tag-icon microsoft-excel worksheet-function
Formel zur Berechnung der Dauer für Zeiten, die sich über zwei Tage erstrecken können

Spalte A: Startzeit im 24-Stunden-Format (mithilfe der Formel eingegeben und mit + + =NOW()festgelegt ) Spalte : Endzeit im 24-Stunden-Format (Eingabe wie in Spalte ) Spalte : Dauer, formatiert als , mit Hilfe der FormelCtrlSHIFT;
BA
C[mm]=B1-A1

Wie berechne ich die Dauer, wenn die Daten in der Spalte BZeiten enthalten, die am folgenden Tag auftreten?

Aktuelles Arbeitsblatt:

Screenshot der tatsächlichen Arbeit

Antwort1

MöglicherweiseDie eleganteste und einfachste Lösung ist die indiese Antworterwähnt von fixer1234 indieser Kommentar:

=MOD(B1-A1,1)

Beachten Sie, dass davon ausgegangen wird, dass es sich bei beiden Werten nur um Zeitwerte handelt (oder dass der Datumsteil abgeschnitten werden soll), d. h. dass jeder kleiner als 1 ist und dass die Dauer weniger als 24 Stunden beträgt.

Erläuterung:

Dies funktioniert, weil bei einem seriellen Datums-/Uhrzeitwert eine 1 24 Stunden entspricht und ein reiner Zeitwert ein Datums-/Uhrzeitwert kleiner als 1 ist. Daher gilt:

  • Wenn B1 >= A1dann MOD(B1-A1,1)→ B1-A1wie erwartet
    oder
  • Wenn B1 < A1dann MOD(B1-A1,1)ist äquivalent zu 1-(A1-B1)(da MOD(-n,m)=m-MOD(n,m)), was die korrekte "Komplement"-Dauer ergibt


Eine alternative Lösung (Sie beurteilen, ob sie einfacher/eleganter ist ;-) ) ist:

=(B1<A1)+B1-A1

Erläuterung:

Dies funktioniert, weil TRUEWerte implizit zu einem 1und FALSEWerte zu einem gezwungen werden 0, wenn sie in arithmetischen Operationen verwendet werden. Daher:

  • Wenn B1 >= A1dann (B1<A1)→ 0und die Formel wird B1-A1
    oder
  • Wenn B1 < A1dann (B1<A1)→ 1und die Formel ist äquivalent zu1-(A1-B1)

Beachten Sie, dass diese Formel korrekt funktioniertauch wenn beide Werte Datums-/Uhrzeitwerte sindUndliegen mehr als 24 Stunden auseinander!Das istnichtDies ist bei der ersten Formel der Fall, daher ist die zweite Formel nützlicher/allgemeinerer.

Antwort2

Ctrl+ Shift+ ;trägt einfach die aktuelle Zeit ohne Datumsstempel in die Zelle ein und, wie @Akina in seiner Antwort anmerkt, wird dadurch nichts gesperrt.

NOW()fügt der Zelle einen Datumsstempel sowie die Uhrzeit hinzu. Um dies anzuzeigen, klicken Sie aufunformatierte leere Zelle oder eine beliebige Zelle im GeneralFormatund tippe =NOW(). Während ich das hier tippe, erhalte ich 21/08/2018 11:25.

Wenn Sie =NOW()in die Zelle A1und =NOW()+TIME(23,0,0)in die Zelle eingeben B1, wenn Sie dann =B1-A1in die Zelle eingeben C1und die Zelle formatieren, Timeerhalten Sie 23:00:00.

Wenn Sie Zeiten ohne Datumsstempel eingeben, müssen Sie eine IF()Formel verwenden, um zu ermitteln, ob die zweite Zeit niedriger ist als die erste (Zeitrahmen geht über Mitternacht hinaus). Im Folgenden wird die Zeitdifferenz zwischen der Zeit in Zelle B1und der in Zelle berechnet A1, unabhängig davon, ob die Endzeit nach Mitternacht liegt oder nicht. Dies funktioniert auch mit oder ohne Datumsstempel, da die Datumsstempel ignoriert werden.

=IF(B1<A1,MOD(B1-A1,1),B1-A1)

Erklärung der Formel

=IF(B1<A1,...)

ist es in der Zelle B1nach Mitternacht? Wenn ja, wird der nächste Teil ausgeführt

MOD(B1-A1,1)

Gibt den MOD(number,divisor)Rest zurück, nachdem eine Zahl durch einen Divisor geteilt wurde. Zelle B1minus Zelle A1ist ein negativer Wert, da die Zeit in der Zelle B1nach Mitternacht liegt und Excel negative Zeitwerte in den meisten Fällen nicht mag, daher wird es #####ohne das ausgegeben MOD(). Das MOD()sortiert das aus.

Eine Alternative zur MOD()Formel wäre die Verwendung

TIME(23,59,59)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(0,0,1)+B1

Welches ist 23:59:59 minus time in cell A1 plus 1 second plus time in cell B1.

Das letzte Bit ist ,B1-A1)der letzte Teil des IF()Wo, wenn die Zeit in der Zelle B1istnichtnach Mitternacht.

Antwort3

Danke @Akina, das ist die einzige Formel, die bei mir funktioniert hat ... Ich habe mehrere der oben genannten ausprobiert – wohlgemerkt mit positiven Stimmenzahlen – und bekam das gefürchtete ###### oder eine negative Zahl mit mehreren Dezimalstellen. Berücksichtigt wurden Zeiten, die sich über zwei Tage erstrecken, OHNE Datumsstempel und all diesen Hokuspokus ...

Antwort4

Um den Unterschied zwischen Minutesden beiden zu ermitteln 24Hrs DateTime Stamps, können Sie Folgendes verwenden:

=Int((A2-A1)*24*60)

Oder

=int((A2-A1)*1440)

Oder

=ROUNDUP((A2-A1)*1440,2)

Notiz,Die Formelzelle muss Generalein Format haben.

verwandte Informationen