Office: Berechnung Nachtzuschlag

Hallo zusammen!

Hab da mal n kleines Problem.
Ich möchte den Nachtzuschlag ausrechnen. Es soll erkannt werden, dass es ab 00:00 Uhr bis 06:00 Uhr einen Nachtzuschlag gibt. Sollte dies der Fall sein, soll automatisch die tatsächliche Zeit des Nachtzuschlages ermittelt werden. Das Ergebnis soll als Uhrzeit (hh:mm:ss) angezeigt werden. Zu beachten ist, dass die Anfangszeit auch vor 00:00 Uhr sein kann und das Ende auch nach 06:00 Uhr. Leider gibt es auch ein Problem bei der Berechnung der Zeit nach 00:00 Uhr.

Ich hab schon alles möglich ausprobiert, klappt aber irgendwie alles net so wie ich es mir vorgestellt habe. Hab eine Tabelle mit den Daten beigefügt.

Bitte helft mir!

Danke und viele Grüße
Die Kurze

 

Hallo,

was sagst Du zu folgenden Formeln?

	A	B	C	D	E	F	G
1	Anfang	Ende	Nachtzu- schlag		Anfang	Ende	Dauer
2	23:45	00:45	?		00:00	00:45	00:45
3	01:05	01:50	?		01:05	01:50	00:45
4	02:00	06:30	?		02:00	06:00	04:00
5	23:45	07:20	?		00:00	06:00	06:00
6
7	Nachtzuschlag ab 00:00 Uhr bis 06:00 Uhr

Zelle	Formel
E2	=WENN(A2>B2;0;WENN(A2<0,25;A2;0))
F2	=WENN(ODER(A2>B2;A2<0,25);MIN(B2;0,25);0)
G2	=F2-E2

Die Formeln im Bereich E2:G2 in die darunterliegenden Zellen kopieren.

 

Hallo!

Ist gar net so schlecht. Besser wäre es aber, wenn es direkt von der Anfangs- und Endzeit berechnet wird. In der Formel soll quasi gleich die Berechnung erfolgen. Es sollen keine zusätzlichen manuellen Zeiten erstellt werden. Vielleicht nur 2 Hilfezellen für 00:00 Uhr und 06:00 Uhr.

Bsp: Anfang 23:45 Uhr; Ende 00:45 Uhr
von 23:45 Uhr bis 00:00 Uhr soll nichts berechnet werden und von
00:00 Uhr bis 00:45 Uhr soll es ausgerechnet werden.

Gibt es dafür überhaupt ne Wenn-Formel?

Trotzdem schon mal danke

Viele Grüße
Die Kurze

 

Hallo,

das sollte doch kein Problem mehr sein - oder ?
Einfach die beiden Formeln in Spalte E und F subtrahieren:
=WENN(ODER(A2>B2;A2<0,25);MIN(B2;0,25);0)-WENN(A2>B2;0;WENN(A2<0,25;A2;0))
Das kann man etwas vereinfachen zu:
=WENN(A2>B2;MIN(B2;0,25);WENN(A2<0,25;MIN(B2;0,25)-A2;0))
oder aber auch zu:
=MIN(B2;0,25)-WENN(A2>B2;0;MIN(A2;0,25))

EDIT:
Falls Du jedoch vorhast, auch nicht standardisierte (=ungültige) Uhrzeiten einzugeben wie zB. 24:15 Uhr statt 00:15, dann müsstest Du allerdings statt A2 folgenden Ausdruck REST(A2;1) einsetzen und statt B2 den Ausdruck REST(B2;1) einsetzen.

 

hilfe gebraucht! Formel für andere Zeiten klappt in LibreOffice aber nicht Excsel

Hallo Ihr Lieben,

ich hoffe hier bei euch Hilfe zu finden.

Es geht um fast exakt die gleiche Situation der ursprünglichen Frage, nur das die Uhrzeit zur Berechnung der Nachtzeit nicht von 0:00 Uhr bis 6:00 Uhr ist, sondern von 23:00 Uhr bis 06:00 Uhr. Ich nahm folgende Formel, wie hier beschrieben,

=MIN(B2;0,25)-WENN(A2>B2;0;MIN(A2;0,25))

und änderte diese ab zu

=MIN(D5;0,25)-WENN(C5>D5;0,958333333333333;MIN(C5;0,25))

Und das ist das Ergebnis (std) in Excel 2013, das Feld ist so formatiert das eine Zeit ausgegeben wird im Format h:mm. Als Wert für 23:00 Uhr habe ich 0,958333333333333 eingesetzt statt der Null.

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]C5[/TD]
[TD]D5[/TD]
[TD]Std[/TD]
[/TR]
[TR]
[TD]19:00[/TD]
[TD]04:00[/TD]
[TD]-19.00[/TD]
[/TR]
</tbody>[/TABLE]


in Libre-Office, wo ich zuerst arbeitete, ist das Ergebnis 5, also korrekt. 5 Stunden Nachtarbeit mit exakt der Formel wie hier in der Farbe orange dargestellt.

Die Tabelle wurde ursprünglich in Excel erstellt, ich möchte nur dieses eine Feld bearbeiten, damit die Kollegin die Nachtstunden nicht von Hand rechnen muss, da dies eine Fehlerquelle darstellt. Jetzt arbeite ich in Excel. Finde aber den Fehler nicht.

So wie es sich jetzt in Excel darstellt, kann ich in C5 den Wert ändern nach Belieben aber das Ergebnis (std) bleibt das gleiche. Ändere ich dagegen den Wert in D5 ab auf zb. 05:00 Uhr ist das Ergebnis (std) = -18:00, also eine Stunde weniger.



Könnt ihr mir bitte helfen? Ich soll das heute noch abgeben

Liebe Grüße

Heike (kein Profi, aber auch nicht ganz ohne Kenntnisse)

 

Hola,

schau mal hier:

http://www.excelformeln.de/formeln.html?welcher=9

Gruß,
steve1da

 

jaa, diese Variante kannte ich schon! ich habe sie nicht ausprobiert, mir gefiel die kurze Variante hier so gut, in welcher ich die Zelladressen schneller umschreiben konnte. Auch wollte ich keine extra Zellen anlegen mit Werten für die Nachtarbeit (23:00 bis 06:00).

Ich wäre darauf zurück gekommen wenn mir keine Lösung eingefallen wäre, danke schön Steve1da.

und nun habe ich einfach meine Zahl (für 23:00 Uhr) minus 1 genommen, damit klappt es in Libre Office Calculator und in Excel.

Zugrunde legte ich diese Tabelle hier

Da aber, zum nachvollziehen, dort 0:00 Uhr mit 1 angegeben ist, und in der Formel hier mit 0, rechnete ich jetzt beim DANN-Wert minus 1 von der Dezimalzahl für 23:00 Uhr, und es klappt in allen Varianten des Durchrechnen.

=(MIN(D5;0,25)-WENN(C5>D5;0,95833333333333-1;MIN(C5;0,25)))-G5

der Inhalt des Feldes G5, das ich hier zum Abzug bringe zusätzlich, ist die Länge der Pausen welche in die Nachtzeit fallen, diese teilt der Mitarbeiter sich selbst ein und müssen händisch eingetragen werden.

Warum aber diese Formel mit 0 und minus 1 klappt erschließt sich mir nicht. Wenn mir das liebenswürdiger Weise erläutert würde würde ich mich freuen, zum Verständnis.

Vielen Dank und Grüße, Heike

 

Hola,

ich versuchs mal :)

eine Stunde ist für Excel 1/24 von einem Tag - deine 23 Uhr sind also die 0,95833333333333.

Wenn also C5 > als D5 ist, sollen die 0,95833333333333, oder 23 Stunden, von dem Minimum von D5 und 0,25 ab. In deinem Fall ist D5 04:00 Uhr.

04:00 minus 23:00 ergibt für Excel eine negative Zahl, die nicht dargestellt werden kann. Durch die -1, also minus 24:00, ziehst du nochmal 24 Stunden davon ab. Für Excel ein erheblicher Unterschied.

Gruß,
steve1da

 

ok,also ein Problem der Darstellung? Warum wird denn eine negative Zahl wenn ich eine weitere Zahl abziehe darstellbar, also bliebt nicht negativ? Anders gefragt, wird durch den Abzug von -1 für Excel aus der Zahl wieder eine Uhrzeit bzw darstellbare Stundenzahl?

Wenn das Ergebnis für Excel -19 war (Arbeitszeit bis 4:00 Uhr minus 23:00 Uhr) und ich rechne dann +24 Stunden dann kommt 5 raus.

Ich überlege warum nicht 1 als Wert für null Uhr angegeben werden kann wie in der Tabelle vorgesehen, warum diese Kurve.

OK, ich nehme es mal einfach so hin als Prinzip hier in diesem Fall, warum ich letztlich addiere wenn ich -1 mache ist mir auch nicht klar. Dazu müsste ich wohl wissen was genau das DANN macht, und da passe ich.

Vielen Dank nochmal, Grüße Heike

 

Hola,

eine Uhrzeit ist nichts anderes als eine Zahl. Eine 1 ist ein voller Tag, eine Stunde wie schon gesagt, 1/24 eines Tages.

Durch das -1 in der Formel erhälst du ja eine negative Zahl. Vor der Klammer steht aber auch ein Minuszeichen. Eine Zahl, minus einer Minuszahl, ergibt eine positive Zahl und die ist für Excel im Uhrzeitformat darstellbar.

Gruß,
steve1da

 

Hallo zusammen,

ich habe eine Tabelle zur Zeiterfassung erstellt.
B2= Arbeitsbeginn
C2= Arbeitsende
T2= Formel zur Berechnung von Nachtzuschlägen (23:00 Uhr "A35" bis 06:00 Uhr "B35")
Formel: =MAX(;1-MAX(B2;$A$35)+MAX(;$B$35-B2)+MIN(C2;$B$35)+(C2>$A$35)*(C2-$A$35)-(B2<C2)*(1-($A$35-$B$35))-($A$35<$B$35)*(C2+(B2>C2)-B2))*(B2<>$B$35)

Jetzt habe ich in B2 und C2 aber auch mal Zeiten stehen wo der Nachtzuschlag nicht berücksichtigt wird z.B.: 08:00 Uhr bis 17:30 Uhr,
In T2 stehen dann aber immer die 7 Stunden Nachtzuschlag, da sollte aber 0 stehen.

Kann mir jemad helfen?

Grüße

 

Hola,

deine Formel gibt einen Fehler aus.

http://www.excelformeln.de/formeln.html?welcher=9

Gruß,
steve1da

 

Hallo,

das habe ich auch eben gesehen.

Habe sie nun geändert aber es zeigt immer noch die 7 Stunden an.

Neue Formel:
siehe Bild

Grüße

 

Hola,

irgendwie ist die immer noch fehlerhaft. Das liegt wohl daran, dass du die Formel nicht durch Code-Tag umrandest. Klick unter dem Antwortfenster mal auf "Erweitert", dann kannst du durch markieren der Formel und klick auf das #-Zeichen den Code schützen.

Code:

=MAX(;1-MAX(B2;$A$35)+MAX(;$B$35-B2)+MIN(C2;$B$35)+(C2>$A$35)*(C2-$A$35)-(B2 < C2)*(1-($A$35-$B$35))-($A$35 < $B$35)*(C2+(B2 > C2)-B2))*(B2<>C2)*($A$35<>$B$35)

Gruß,
steve1da

 

Code:

=max(;1-max(b2;$a$35)+max(;$b$35-b2)+min(c2;$b$35)+(c2>$a$35)*(c2-$a$35)-(b2<c2)*(1-($a$35-$b$35))-($a$35<$b$35)*(c2+(b2>c2)-b2))*(b2<>$b$35)*($a$35<>$b$35)