Abend- und Nachtarbeitszeit berechnen

[Elsa123]

Hallo zusammen,

ich würde gerne in Excel die Stunden für den Abendzuschlag (20:00-23:00 Uhr) und für den Nachtzuschlag (23:00-06:00 Uhr) berechnen. Dabei arbeite ich mit folgender Formel, die ich online gefunden habe: =MAX(;MIN($I$2+($H$2>$I$2);C2+(B2>C2))-MAX($H$2;B2))+MAX(;(MIN($I$2;C2+(B2>C2))-B2)*($H$2>$I$2))+MAX(;MIN($I$2+($H$2>$I$2);C2+0)-$H$2)*(B2>C2)

Dabei ist hier H2 der Beginn für den Abendzuschlag und I2 das Ende. Der Arbeitszeitbeginn ist in B2, das Ende in C2.
Excel berechnet bei einem Beginn nach 20 Uhr und einem Ende nach 23 Uhr aber dennoch die vollen drei Stunden. Wie kann ich das ändern?

Beim Nachtzuschlag scheinen die Werte korrekt zu sein.

Vielen Dank im Voraus.


Viele Grüße
Elsa

[steve1da]

Hola,
wenn ich in B2 20:30 schreibe und in C2 23:30 dann werden doch korrekterweise 2:30 als Abendstunden ausgegeben.
Wo ist denn das bei dir nicht der Fall?

[EA1950]

Hallo,

Excel berechnet bei einem Beginn nach 20 Uhr und einem Ende nach 23 Uhr aber dennoch die vollen drei Stunden. Wie kann ich das ändern?

du hast einen typischen Fehler gemacht: Deine Uhrzeitangaben in den Spalten B+C sind Texte, keine Zahlen.
Damit die Formeln überall korrekt funktionieren, müssen diese Uhrzeiten tatsächlich Zahlen zwischen 0 und 1 sein.

In der beiliegenden Datei habe ich dein Arbeitsblatt "Tabelle1" ins Arbeitsblatt "Tabelle1_korrekt" kopiert und diese Uhrzeiten in tatsächliche Uhrzeiten umgewandelt (Spalte B+C). Die Werte der Spalten D+E dürften jetzt korrekt sein.

Darüber hinaus habe ich dir eine benutzerdefinierte Funktion in den Namensmanager eingefügt:

PHP-Code:

/**
Berechnet für die Arbeitszeit (AZa-AZe), wieviel Nachtschichtzeit (NSa-NSe) enthalten ist.
*/
NSArbZt = LAMBDA(AZa;AZe;NSa;NSe; LET(
    AZn; AZa>AZe;  AZf; AZe+AZn;
    NSn; NSa>NSe;  NSf; NSe+NSn;
    Per; LAMBDA(a;b;c;d;n; MAX(;(MIN(a;b)-MAX(c;d))*n));
    Per(NSf;AZf; NSa;AZa; 1) + Per(NSe;AZf; 0;AZa; NSn) + Per(NSf;AZe; NSa;0; AZn)
));; 


und diese in den beiden Spalten L+M angewendet:
ab Zelle L2: =NSArbZt($B2;$C2;$H$2;$I$2)
ab Zelle M2: =NSArbZt($B2;$C2;$H$3;$I$3)

[Elsa123]

Vielen Dank für die Antworten.

Ich habe mittlerweile festgestellt, dass die Formeln richtig funktionieren, wenn ich die Uhrzeiten manuell eintippe. Aber sobald ich die Daten aus einer anderen Tabelle, die ich aus dem Zeiterfassungsprogramm exportiert habe, einfüge, funktioniert es nicht mehr, auch nicht in der Version von EA1950. Dabei wähle ich natürlich "Werte einfügen" aus, um die Formatierung nicht zu verändern. Sobald ich dann einen Doppelklick in das betreffende Feld mit der Uhrzeit mache, wird der Abendwert korrekt berechnet. 

Viele Grüße
Elsa

[steve1da]

Du fügst deine Werte beim Kopieren als TEXT ein. Da nützt auch ein nachträgliches Ändern des Zellformats erstmal nichts.
Wenn du das nicht ändern kannst kannst du als Workaround in den Formeln hinter jedem B2 und C2 ein *1 setzen, also so: ......C2*1+(B2*1>C2*1) .......

[Elsa123]

Ich habe jetzt eine Lösung gefunden: Indem ich zwei Hilfsspalten einfüge, die als Uhrzeit formatiert sind und in denen ich Beginn und Ende mit 1 multipliziere, werden die Werte direkt richtig angezeigt. Zwar nicht ganz elegant, soll mir aber reichen.

[Andreas Killer]

Aber sobald ich die Daten aus einer anderen Tabelle, die ich aus dem Zeiterfassungsprogramm exportiert habe, einfüge, funktioniert es nicht mehr

Wie schon meine Vorredner gesagt haben, was Du das in Excel hast ist ein Text, keine Zeit.

Als was exportierst Du die Daten? Als CSV-Datei? Falls ja, dann importiere die mit Power Query und korrigiere so den Fehler.
Wenn Du dazu Hilfe brauchst müssen wir so eine Datei mal sehen.

Andreas.

[steve1da]

Zwar nicht ganz elegant, soll mir aber reichen.

Das es auch ohne Hilfsspalten geht hab ich scheinbar vergebens erklärt.

[EA1950]

Hallo Elsa,

Ich habe jetzt eine Lösung gefunden: Indem ich zwei Hilfsspalten einfüge, die als Uhrzeit formatiert sind und in denen ich Beginn und Ende mit 1 multipliziere, werden die Werte direkt richtig angezeigt. Zwar nicht ganz elegant, soll mir aber reichen.

diese Hilfsspalten sind nicht nötig. Man kann die Uhrzeit-Texte auch ohne Hilfsspalten in echte Uhrzeit-Zahlen umwandeln.

Im Folgenden beziehe mich auf deine gepostete Datei bzw. auf meine Datei und zwar jeweils auf das Arbeitsblatt "Tabelle1". In diesem Arbeitsblatt hast du im Zellbereich B2:C28 lauter Uhrzeit-Texte. Diese sollen umgewandelt werden in Uhrzeit-Zahlen (an Ort und Stelle - also ohne Hilfsspalten):

1) Du gibst in eine freie Zelle die Zahl 1 ein, zB. in Zelle F2.
2) Du drückst die Tastenkombination Strg+C, sodass die 1 in die Zwischenablage kopiert wird.
3) Du markierst den Zellbereich B2:C28 (der die Uhrzeit-Texte enthält)
4) Du drückst die Tastenkombination Strg+Alt+V sodass das Dialogfenster "Inhalte einfügen" erscheint.
5) Dort wählst du aus dem Abschnitt "Einfügen" die Option "Werte" aus und aus dem Abschnitt "Vorgang" die Option "Multiplizieren", ferner zum Abschluss den "OK"-Button
6) Danach erscheinen im Bereich B2:C28 alle Uhrzeiten als Dezimalzahlen (Werte zwischen 0,0 und 0,999999).
7) Diese Werte kannst du mit einem Uhrzeit-Format (zB. hh:mm) wieder als Uhrzeiten zur Anzeige bringen.

Auch wenn diese Werte (aus Punkt 7)) dann wieder als Uhrzeiten angezeigt werden, so sind sie doch in Excel fortan als Zahlen gespeichert und nicht als Texte - und allein darauf kommt es an.

[slowboarder]

Hi
mit WENN wird es hier kompliziert, weil
- die Nachstunden sich auf zwei Blöcke verteilen (von 0:00-6:00 und von 23:00-0:00)
- zwei verschiedene Tage im Einsatz sein können.
Das gibt dir sehr viele Varianten, die du berücksichtigen musst.

hier mal ein anderer Ansatz, der zwar etwas "brute force" ist, weil der die Berechnung in einer Schleife über jede Minute des Zeitraums durchführt, allerdings erlaubt dieser Ansatz auch eine sehr differenzierte Auswertung, dh du könntest auch noch zusätzlich Pausenzeiten und ähnliches getrennt auswerten, ohne dass die Basisformel dabei komplizierter wird.

schau dir mal das Beispiel an:


  Vorlage Nachtarbeit (2).xlsx (Größe: 16,1 KB / Downloads: 3)

die neue Berechnung ist in den Spalten F und G.

Gruß Daniel