Uhrzeit ohne Doppelpunkt eingeben (hhmmss) und Zeitdifferenzen in Excel berechnen

Question 1

Eine Möglichkeit wäre die Verwendung einer Kombination aus Zellformatierung und ExcelZEITFunktionen.

Formatieren Sie Ihre Zeiteingabezellen (A2 und B2 in meinem Beispiel) als Text. Das erwartete Format ist immer hhmmss, geben Sie also bei Zeiten mit einstelligen Stunden die führende Null ein. Anschließend können Sie mit dieser Formel rechnen:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Dabei werden die beiden Zeichen ganz links als „Stunden“, die beiden Zeichen in der Mitte als „Minuten“ und die beiden Zeichen ganz rechts als „Sekunden“ verwendet und in das umgewandelt, was Excel als Zeit erkennt. Anschließend wird das eine vom anderen subtrahiert und das Ergebnis mit der folgenden Formatierung angezeigt hhmmss:

BEARBEITEN: Da die Anforderung nicht ganz der Frage entspricht, habe ich die Formel geändert, um führende Nullen durch Auffüllen zu berücksichtigen:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

Deutlich weniger lesbar, füllt den Wert jetzt aber mit Nullen auf und verwendet die 6 ganz rechts, sollte also funktionieren, unabhängig davon, wie viele Nullen Sie verwenden.

Ich glaube, Sie möchten das Ergebnis tatsächlich wie hh:mm:ssin diesem Fall speziell formatieren.

Answer

Eine Möglichkeit wäre die Verwendung einer Kombination aus Zellformatierung und ExcelZEITFunktionen.

Formatieren Sie Ihre Zeiteingabezellen (A2 und B2 in meinem Beispiel) als Text. Das erwartete Format ist immer hhmmss, geben Sie also bei Zeiten mit einstelligen Stunden die führende Null ein. Anschließend können Sie mit dieser Formel rechnen:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Dabei werden die beiden Zeichen ganz links als „Stunden“, die beiden Zeichen in der Mitte als „Minuten“ und die beiden Zeichen ganz rechts als „Sekunden“ verwendet und in das umgewandelt, was Excel als Zeit erkennt. Anschließend wird das eine vom anderen subtrahiert und das Ergebnis mit der folgenden Formatierung angezeigt hhmmss:

BEARBEITEN: Da die Anforderung nicht ganz der Frage entspricht, habe ich die Formel geändert, um führende Nullen durch Auffüllen zu berücksichtigen:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

Deutlich weniger lesbar, füllt den Wert jetzt aber mit Nullen auf und verwendet die 6 ganz rechts, sollte also funktionieren, unabhängig davon, wie viele Nullen Sie verwenden.

Ich glaube, Sie möchten das Ergebnis tatsächlich wie hh:mm:ssin diesem Fall speziell formatieren.

Question 2

Hier ist eine weitere Möglichkeit, Ihre Zeiten so zu dekodieren, wie Sie sie eingeben möchten:

Ich habe die Start- und Endzeiten als unformatierte Zahlen belassen, um die Aktion besser sichtbar zu machen. In diesem Beispiel ist Ihre Startzeit 25 Sekunden nach Mitternacht oder Mittag, was Sie als 000025 eingeben würden. Als Zahl wird sie als 25 gespeichert, auch wenn sie durch Ihre Formatierung wie 00:00:25 aussieht.

Bei diesem Ansatz werden Stunden und Minuten anhand ihrer Position in der Zahl als Hundertstelpotenzen abgezogen. Sekunden sind unabhängig davon immer die richtigen zwei Ziffern. Die Formel in C2 lautet:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

Die MOD-Funktion ist das Gegenteil der INT-Funktion. Sie gibt den Rest nach der Division zurück.

Answer

Hier ist eine weitere Möglichkeit, Ihre Zeiten so zu dekodieren, wie Sie sie eingeben möchten:

Ich habe die Start- und Endzeiten als unformatierte Zahlen belassen, um die Aktion besser sichtbar zu machen. In diesem Beispiel ist Ihre Startzeit 25 Sekunden nach Mitternacht oder Mittag, was Sie als 000025 eingeben würden. Als Zahl wird sie als 25 gespeichert, auch wenn sie durch Ihre Formatierung wie 00:00:25 aussieht.

Bei diesem Ansatz werden Stunden und Minuten anhand ihrer Position in der Zahl als Hundertstelpotenzen abgezogen. Sekunden sind unabhängig davon immer die richtigen zwei Ziffern. Die Formel in C2 lautet:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

Die MOD-Funktion ist das Gegenteil der INT-Funktion. Sie gibt den Rest nach der Division zurück.

Question 3

Ich weiß, das ist ein alter Beitrag, aber wenn (wie ich) Leute in zahllosen Foren nach einer Lösung suchen, um beim Eintragen von Stundenzetteln Zeit zu sparen, keinen „Doppelpunkt“ manuell eingeben zu müssen, sondern den Doppelpunkt sichtbar zu haben und dann Berechnungen durchführen zu können ... und das alles ohne Verwendung von VBA-Skripten, ausgeblendeten Zellen und Formeln ... usw. usw. ... Das ist die Lösung, die mir eingefallen ist – und sie scheint mit führenden Nullen, 24-Stunden-Zeit und Stundenzetteln, die Mitternacht umfassen, zu funktionieren (obwohl eine Anpassung erforderlich wäre, wenn Sie erwarten, dass ein Stundenzettel mehrere Mitternachtsstunden umfasst – ich hielt das in meinem Fall nicht für notwendig).

Außerdem brauche ich keine „Sekunden“, aber Sie sollten es problemlos erweitern können, wenn Sie dem Konzept folgen:

Formatieren Sie Ihre Start- und Endzeitzellen benutzerdefiniert wie folgt:

0#":"##

Dadurch werden sie in „Textzellen“ umgewandelt, die Ziffern werden jedoch so aufgefüllt, dass immer vier Ziffern angezeigt werden (auch mit einer führenden Null bzw. Null für amerikanische Freunde).

Beachten Sie, dass dadurch die Eingabe JEDER vierstelligen Zahl in einem Zeitformat möglich ist. Theoretisch ist es also möglich, 0768 einzugeben, und es wird die Zeit 07:68, die natürlich nicht existiert. Also entweder

a) Erstellen Sie eine Datenüberprüfungsliste wie folgt: - Erstellen Sie auf einem separaten Arbeitsblatt (Registerkarte) in einer einzelnen Spalte eine Liste aller zulässigen Werte. In meinem Fall waren das 05:00, 05:15, 05:30, 05:45 ... 04:45, da ich nur mit 15-Minuten-Schritten arbeitete. Wenn Sie bis auf Sekunden herunterbrechen müssten (ernsthaft?), würde das ziemlich lang werden ... also 050001, 050002 ... usw. Oder Sie schreiben eine andere Formel, nur um diese Liste zu erstellen, und kopieren dann die Textdaten (nur Werte einfügen) in eine neue Spalte, um die Formeln zu entfernen (das würde ich tun). - Wählen Sie alle Zellen mit „akzeptablen Werten“ aus und wählen Sie auf der Registerkarte „Formeln“ der Symbolleiste „Namen festlegen“. Geben Sie ihm einen Namen wie „MonkeyTimeList“. - Gehen Sie zurück zu Ihrem Arbeitszeitblatt, wählen Sie alle Zellen aus, die sowohl Start- als auch Endzeiten abdecken, und wechseln Sie zur Registerkarte „Daten“ der Symbolleiste, wählen Sie „Datenüberprüfung > Datenüberprüfung > Einstellungen“, wählen Sie im Abschnitt „Zulassen“ „Liste“ aus der Dropdown-Liste und geben Sie im Abschnitt „Quelle“ den von Ihnen definierten Namen mit einem vorangestellten „=“ ein, also in meinem Fall „=MonkeyTimeList“. Klicken Sie zunächst auf „OK“, wählen Sie die Registerkarte „Fehlermeldung“, aktivieren Sie das Kontrollkästchen „Fehlermeldung anzeigen …“ und wählen Sie im Dropdown-Menü „Stil“ „Stopp“. Fügen Sie gerne eine Fehlermeldung hinzu, wie „Monkey Man sagt, Sie sind ein Idiot, versuchen Sie, die Zeiten im RICHTIGEN FORMAT einzugeben!“. Klicken Sie dann auf „OK“. - Dadurch wird sichergestellt, dass nur Daten aus Ihrer „akzeptablen“ Liste als Zeit eingegeben werden können.

b) Theoretisch könnten Sie die bedingte Formatierung verwenden, um nur eine Zelle hervorzuheben, in der falsche Daten hinzugefügt wurden. Dadurch würden Sie die Verwendung eines separaten Arbeitsblatts für Ihre Datenüberprüfungsdaten vermeiden. Ich habe es nicht auf diese Weise gemacht, weshalb ich theoretisch sage. Das kann ein anderes Tutorial sein....

So können Sie nun Ihre Zeiten eingeben, ohne jedes Mal nach dem Doppelpunkt und der Umschalttaste suchen zu müssen.

Wenn Sie WIRKLICH Sekunden in Ihrem Zellenformat haben möchten, würde das benutzerdefinierte Zellenformat folgendermaßen aussehen:

0#":"##":"##

ALSO... JETZT BERECHNEN WIR DIE STUNDEN von der Startzeit bis zur Endzeit.... auch dies basiert nur auf Minuten... aber wenn Sie der Logik folgen können, kann es auch auf Sekunden erweitert werden.

Geben Sie in die dritte Spalte (vorausgesetzt, Spalte A ist die Startzeit, B ist die Endzeit und C ist die Gesamtstundenzahl) die folgende Formel ein:

=WENN(WERT(B2)>=WERT(A2),(SUMME(WERT(MITTE(TEXT(B2,"0000"),1,2)),WERT(MITTE(TEXT(B2,"0000"),3,2))/60))-(SUMME(WERT(MITTE(TEXT(A2,"0000"),1,2)),WERT(MITTE(TEXT(A2,"0000"),3,2))/60)),(SUMME(WERT(MITTE(TEXT(B2,"0000"),1,2)),24,WERT(MITTE(TEXT(B2,"0000"),3,2))/60))-(SUMME(WERT(MITTE(TEXT(A2,"0000"),1,2)),WERT(MITTE(TEXT(A2,"0000"),3,2))/60)))

Jetzt muss ich es erklären …

Die Funktion „WENN“ unterscheidet zwischen Endzeiten auf einer 24-Stunden-Uhr, die vor Mitternacht liegen, und solchen, die nach Mitternacht liegen. WENN nach Mitternacht, werden 24 Stunden zur Endzeit addiert, damit die Berechnung von Endzeit minus Startzeit trotzdem funktioniert. WENN vor Mitternacht, müssen keine 24 Stunden addiert werden. Für Berechnungszwecke wird also die Zeit 01:00 tatsächlich als 25:00 behandelt, 02:00 ist 26:00 und so weiter. HINWEIS: Dies setzt voraus, dass eine Schicht nicht länger als 23 Stunden und 45 Minuten dauert. Wenn sie länger dauert, müssen Sie möglicherweise Datumsspalten in Ihre Formel einbeziehen … auch das habe ich nicht gebraucht.

Die MID-Funktionen arbeiten mit TEXT (daher die TEXT-Funktion) und geben die Ziffern an bestimmten Positionen aus einer referenzierten Zelle im angegebenen Format zurück... Also, „MID(TEXT(B2, „0000“),1,2)“ betrachtet Zelle B2, betrachtet sie immer im Format von 4 Ziffern/Buchstaben und gibt die beiden Ziffern ab Position „1“ zurück. In meinem Fall stellt dies ganze Stunden dar. Ein weiteres Beispiel: „MID(TEXT(A2, „000000“),5,2)“ betrachtet Zelle A2 und gibt die beiden Ziffern ab Position „5“ zurück, was ganze Sekunden sein könnten.

Dem Ganzen habe ich die Funktion „VALUE“ vorangestellt, um den zurückgegebenen Text in eine Zahl umzuwandeln, damit wir damit in Berechnungen arbeiten können.

Dies ermöglicht es uns, für jede Zelle die Stunden, Minuten und Sekunden separat zu behandeln. Berechnungen mit Stunden sind einfach, da es sich bereits um ganze Zahlen handelt. Minuten sind jedoch eigentlich Bruchteile von Stunden oder Bruchteile von 60 Minuten. Wenn Sie also die Ziffern, die „Minuten“ darstellen, extrahieren, dividieren Sie diese durch „60“, um einen „Bruchteil“ einer Stunde zu erhalten. Ebenso verhält es sich mit Sekunden: dividieren Sie durch 3600 (Anzahl der Sekunden in einer Stunde), um Sekunden als „Bruchteil einer Stunde“ zu erhalten.

Da die Zeiten nun als Zahlen mit Brüchen dargestellt werden, können Sie beliebig addieren oder subtrahieren. Im Fall meiner Formel ergibt sich die Gesamtzahl der Stunden zwischen Startzeit und Endzeit. Ich formatiere die Zelle mit der Formel als Benutzerdefiniert > 00,00, wodurch ich dann mit Stundensätzen usw. multiplizieren kann.

Wenn jemand unbedingt noch Sekunden in seiner Formel haben möchte und nicht weiterkommt, lassen Sie es mich wissen. Wenn mir bei der Arbeit langweilig ist, kann ich vielleicht helfen... aber Sekunden??? wirklich???

Answer

Ich weiß, das ist ein alter Beitrag, aber wenn (wie ich) Leute in zahllosen Foren nach einer Lösung suchen, um beim Eintragen von Stundenzetteln Zeit zu sparen, keinen „Doppelpunkt“ manuell eingeben zu müssen, sondern den Doppelpunkt sichtbar zu haben und dann Berechnungen durchführen zu können ... und das alles ohne Verwendung von VBA-Skripten, ausgeblendeten Zellen und Formeln ... usw. usw. ... Das ist die Lösung, die mir eingefallen ist – und sie scheint mit führenden Nullen, 24-Stunden-Zeit und Stundenzetteln, die Mitternacht umfassen, zu funktionieren (obwohl eine Anpassung erforderlich wäre, wenn Sie erwarten, dass ein Stundenzettel mehrere Mitternachtsstunden umfasst – ich hielt das in meinem Fall nicht für notwendig).

Außerdem brauche ich keine „Sekunden“, aber Sie sollten es problemlos erweitern können, wenn Sie dem Konzept folgen:

Formatieren Sie Ihre Start- und Endzeitzellen benutzerdefiniert wie folgt:

0#":"##

Dadurch werden sie in „Textzellen“ umgewandelt, die Ziffern werden jedoch so aufgefüllt, dass immer vier Ziffern angezeigt werden (auch mit einer führenden Null bzw. Null für amerikanische Freunde).

Beachten Sie, dass dadurch die Eingabe JEDER vierstelligen Zahl in einem Zeitformat möglich ist. Theoretisch ist es also möglich, 0768 einzugeben, und es wird die Zeit 07:68, die natürlich nicht existiert. Also entweder

a) Erstellen Sie eine Datenüberprüfungsliste wie folgt: - Erstellen Sie auf einem separaten Arbeitsblatt (Registerkarte) in einer einzelnen Spalte eine Liste aller zulässigen Werte. In meinem Fall waren das 05:00, 05:15, 05:30, 05:45 ... 04:45, da ich nur mit 15-Minuten-Schritten arbeitete. Wenn Sie bis auf Sekunden herunterbrechen müssten (ernsthaft?), würde das ziemlich lang werden ... also 050001, 050002 ... usw. Oder Sie schreiben eine andere Formel, nur um diese Liste zu erstellen, und kopieren dann die Textdaten (nur Werte einfügen) in eine neue Spalte, um die Formeln zu entfernen (das würde ich tun). - Wählen Sie alle Zellen mit „akzeptablen Werten“ aus und wählen Sie auf der Registerkarte „Formeln“ der Symbolleiste „Namen festlegen“. Geben Sie ihm einen Namen wie „MonkeyTimeList“. - Gehen Sie zurück zu Ihrem Arbeitszeitblatt, wählen Sie alle Zellen aus, die sowohl Start- als auch Endzeiten abdecken, und wechseln Sie zur Registerkarte „Daten“ der Symbolleiste, wählen Sie „Datenüberprüfung > Datenüberprüfung > Einstellungen“, wählen Sie im Abschnitt „Zulassen“ „Liste“ aus der Dropdown-Liste und geben Sie im Abschnitt „Quelle“ den von Ihnen definierten Namen mit einem vorangestellten „=“ ein, also in meinem Fall „=MonkeyTimeList“. Klicken Sie zunächst auf „OK“, wählen Sie die Registerkarte „Fehlermeldung“, aktivieren Sie das Kontrollkästchen „Fehlermeldung anzeigen …“ und wählen Sie im Dropdown-Menü „Stil“ „Stopp“. Fügen Sie gerne eine Fehlermeldung hinzu, wie „Monkey Man sagt, Sie sind ein Idiot, versuchen Sie, die Zeiten im RICHTIGEN FORMAT einzugeben!“. Klicken Sie dann auf „OK“. - Dadurch wird sichergestellt, dass nur Daten aus Ihrer „akzeptablen“ Liste als Zeit eingegeben werden können.

b) Theoretisch könnten Sie die bedingte Formatierung verwenden, um nur eine Zelle hervorzuheben, in der falsche Daten hinzugefügt wurden. Dadurch würden Sie die Verwendung eines separaten Arbeitsblatts für Ihre Datenüberprüfungsdaten vermeiden. Ich habe es nicht auf diese Weise gemacht, weshalb ich theoretisch sage. Das kann ein anderes Tutorial sein....

So können Sie nun Ihre Zeiten eingeben, ohne jedes Mal nach dem Doppelpunkt und der Umschalttaste suchen zu müssen.

Wenn Sie WIRKLICH Sekunden in Ihrem Zellenformat haben möchten, würde das benutzerdefinierte Zellenformat folgendermaßen aussehen:

0#":"##":"##

ALSO... JETZT BERECHNEN WIR DIE STUNDEN von der Startzeit bis zur Endzeit.... auch dies basiert nur auf Minuten... aber wenn Sie der Logik folgen können, kann es auch auf Sekunden erweitert werden.

Geben Sie in die dritte Spalte (vorausgesetzt, Spalte A ist die Startzeit, B ist die Endzeit und C ist die Gesamtstundenzahl) die folgende Formel ein:

=WENN(WERT(B2)>=WERT(A2),(SUMME(WERT(MITTE(TEXT(B2,"0000"),1,2)),WERT(MITTE(TEXT(B2,"0000"),3,2))/60))-(SUMME(WERT(MITTE(TEXT(A2,"0000"),1,2)),WERT(MITTE(TEXT(A2,"0000"),3,2))/60)),(SUMME(WERT(MITTE(TEXT(B2,"0000"),1,2)),24,WERT(MITTE(TEXT(B2,"0000"),3,2))/60))-(SUMME(WERT(MITTE(TEXT(A2,"0000"),1,2)),WERT(MITTE(TEXT(A2,"0000"),3,2))/60)))

Jetzt muss ich es erklären …

Die Funktion „WENN“ unterscheidet zwischen Endzeiten auf einer 24-Stunden-Uhr, die vor Mitternacht liegen, und solchen, die nach Mitternacht liegen. WENN nach Mitternacht, werden 24 Stunden zur Endzeit addiert, damit die Berechnung von Endzeit minus Startzeit trotzdem funktioniert. WENN vor Mitternacht, müssen keine 24 Stunden addiert werden. Für Berechnungszwecke wird also die Zeit 01:00 tatsächlich als 25:00 behandelt, 02:00 ist 26:00 und so weiter. HINWEIS: Dies setzt voraus, dass eine Schicht nicht länger als 23 Stunden und 45 Minuten dauert. Wenn sie länger dauert, müssen Sie möglicherweise Datumsspalten in Ihre Formel einbeziehen … auch das habe ich nicht gebraucht.

Die MID-Funktionen arbeiten mit TEXT (daher die TEXT-Funktion) und geben die Ziffern an bestimmten Positionen aus einer referenzierten Zelle im angegebenen Format zurück... Also, „MID(TEXT(B2, „0000“),1,2)“ betrachtet Zelle B2, betrachtet sie immer im Format von 4 Ziffern/Buchstaben und gibt die beiden Ziffern ab Position „1“ zurück. In meinem Fall stellt dies ganze Stunden dar. Ein weiteres Beispiel: „MID(TEXT(A2, „000000“),5,2)“ betrachtet Zelle A2 und gibt die beiden Ziffern ab Position „5“ zurück, was ganze Sekunden sein könnten.

Dem Ganzen habe ich die Funktion „VALUE“ vorangestellt, um den zurückgegebenen Text in eine Zahl umzuwandeln, damit wir damit in Berechnungen arbeiten können.

Dies ermöglicht es uns, für jede Zelle die Stunden, Minuten und Sekunden separat zu behandeln. Berechnungen mit Stunden sind einfach, da es sich bereits um ganze Zahlen handelt. Minuten sind jedoch eigentlich Bruchteile von Stunden oder Bruchteile von 60 Minuten. Wenn Sie also die Ziffern, die „Minuten“ darstellen, extrahieren, dividieren Sie diese durch „60“, um einen „Bruchteil“ einer Stunde zu erhalten. Ebenso verhält es sich mit Sekunden: dividieren Sie durch 3600 (Anzahl der Sekunden in einer Stunde), um Sekunden als „Bruchteil einer Stunde“ zu erhalten.

Da die Zeiten nun als Zahlen mit Brüchen dargestellt werden, können Sie beliebig addieren oder subtrahieren. Im Fall meiner Formel ergibt sich die Gesamtzahl der Stunden zwischen Startzeit und Endzeit. Ich formatiere die Zelle mit der Formel als Benutzerdefiniert > 00,00, wodurch ich dann mit Stundensätzen usw. multiplizieren kann.

Wenn jemand unbedingt noch Sekunden in seiner Formel haben möchte und nicht weiterkommt, lassen Sie es mich wissen. Wenn mir bei der Arbeit langweilig ist, kann ich vielleicht helfen... aber Sekunden??? wirklich???

Uhrzeit ohne Doppelpunkt eingeben (hhmmss) und Zeitdifferenzen in Excel berechnen

Antwort1

Antwort2

Antwort3

verwandte Informationen