Berechnung der verstrichenen Zeit mit ausgeschlossenen Zeiträumen (Microsoft Excel)
Mahesh kann herausfinden, wie die Differenz zwischen zwei Daten und Zeiten in Minuten berechnet werden kann. Er möchte jedoch die Differenz in Minuten berechnen, jedoch die Stunden zwischen 17:00 Uhr und 8:00 Uhr sowie alles zwischen Freitag 17:00 Uhr und Montag 8:00 Uhr ausschließen. Wenn beispielsweise das erste Datum der 18.02.09, 18:00 Uhr und das Enddatum der 19.02.09, 9:00 Uhr ist, sollte das korrekte Ergebnis 60 Minuten betragen. Mahesh fragt sich, ob dies mit einer Formel möglich ist.
Es sollte offensichtlich sein, dass eine Formel zum Erreichen des gewünschten Ergebnisses sehr komplex sein kann. Viele Abonnenten stellten unterschiedliche Lösungen zur Verfügung, einschließlich einiger großartiger benutzerdefinierter Funktionen. Anstatt mich auf alle zu konzentrieren, dachte ich, ich würde einfach zur elegantesten (kürzesten) Formel springen und vorschlagen, sie zu verwenden.
Angenommen, Ihr Startdatum / Ihre Startzeit befand sich in Zelle A1 und das Enddatum / die Endzeit in Zelle B1. In Anbetracht dessen könnten Sie die folgende Formel verwenden:
=(NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")
+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"
,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)
* MOD(A1,1),"17:00","08:00")
Dies ist eine einzelne Formel; es gibt eine verstrichene Zeit zurück. Dies bedeutet, dass Sie die Zelle formatieren müssen, um die verstrichene Zeit anzuzeigen. Wenn Sie das Ergebnis lieber als reguläre Ganzzahl haben möchten, sollten Sie stattdessen diese Version der Formel verwenden:
=((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")
+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"
,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)
MOD(A1,1),"17:00","08:00"))1440
Die Änderung (Multiplikation des ursprünglichen Ergebnisses mit 1440) führt zu einer Anzahl von Minuten und nicht zu einer verstrichenen Zeit. Der Wert 1440 wird durch Multiplizieren von 60 mit 24 abgeleitet, um die Anzahl der Minuten pro Tag zu erhalten.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (5399) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier: