Zeit- Zuschläge

[neopa]

Hallo Andi,

nun ist es eindeutiger.

In G23 bis N23 mit benutzerdefinierten Zahlenformat: [h]:mm;; folgend Formeln:

In G23: =C$8*(REST(A23-1;7)>1)
In H23:=REST(D23-C23;1)*(ANZAHL(C23:D23)=2)+REST(F23-E23;1)*(ANZAHL(E23:F23)=2)

und für den Feiertagszuschlag in I23:

Code:

=(((WENN(D23<C23;1;D23)-C23)*(ANZAHL(C23:D23)=2)+(WENN(F23<E23;1;F23)-E23)*(ANZAHL(E23:F23)=2))
*(B23=1)+ZÄHLENWENN(FT;A23+1)*((D23<C23)*D23+(F23<E23)*F23))*F$13%

In N23: =SUMME(H23:M23)
In O23: =N23-G23 hier jedoch mit benutzerdefinierten Zahlenformat: [h]:mm;[Rot]-[h]:mm;

alle Formeln nach unten kopieren.

Durch die Formeln in G23: H23 sind auch Deine Formeln in P:Q nicht mehr notwendig.

Wenn Du dazu in F13:F17 anstelle der Ganzzahlen die Prozentzahlen angibst, können die "%" in Spalte G und in allen bisher hier angegebenen Formeln auch das angehangene "%" an den Formeln entfallen.

[neopa]

Hallo Andi,

weil heute Sonntag ist nun die Formel für den Sonntag-Zuschlag.


In J23:

Code:

=(((REST(A23;7)=2)*((WENN(D23>C23;D23;1)-C23)*(ANZAHL(C23:D23)=2)+(WENN(F23>E23;F23;1)-E23)*
(ANZAHL(E23:F23)=2)))*(B23=0)+(REST(A23+1;7)=2)*
(ZÄHLENWENN(FT;A23+1)-MAX((TEXT(A23+1;"MMTT")={"1224"."1231"})*2)<1)*
(WENN(D23>C23;0;D23)*(ANZAHL(C23:D23)=2)+WENN(F23>E23;0;F23)*(ANZAHL(E23:F23)=2)))*F$14

und Formel nach unten kopieren.

Möglicherweise könnte man die Formel noch etwas kürzen. Die Formel geht nun davon aus, dass in F14 ein Prozentwertangabe steht.

Anmerkung: ich frage in der Formel bewusst nicht das Datum aus der Folgezeile in Spalte A ab (damit würde die Formel etwas kürzer), weil ich ja damit rechnen muss, dass das Datum des folgenden Tages in Spalte A noch nicht eingetragen ist.

[Josef B]

Hallo Werner
 
Für mich ist es nicht ganz klar, warum man tagesüberschreitende Zeiten berechnen soll, wenn in Spalte A die Tage eines Monats lückenlos aufgelistet sind.
Konsequenterweise müsste doch das Arbeitsende spätestens um 24:00 enden und bei Bedarf am nächsten Tag um 00:00 weitergehen.
Aber sollte ich damit falsch liegen, dann müsste man  doch sicher nicht nur
(ZÄHLENWENN(FT;A23+1)-
sondern auch (ZÄHLENWENN(FT;A23) prüfen
 
Gruss Sepp

[little76]

 
Hallo Werner,

super vielen Dank, habe sie kopiert und eingefügt...

Inzwischen habe ich weitere Änderungen vorgenommen...

Spalte O
=WENN(G23="";"";N23-G23)

Spalte G
=WENN(D23<"";C$8*(REST(A23-1;7)>1);"")

ab Spalte K51
=WENN(A51="";"";(B51=0)*(REST(A51;7)=1)*((WENN(D51I$16;MIN(D51;K$16);I$16))-MAX(I$16;C51))*(D51<>"")*(C51I$16;MIN(F51;K$16);I$16))-MAX(I$16;E51))*(F51<>"")*(E51)

ab Spalte J51
=WENN(A51="";"";(((REST(A51;7)=2)*((WENN(D51>C51;D51;1)-C51)*(ANZAHL(C51:D51)=2)+(WENN(F51>E51;F51;1)-E51)*(ANZAHL(E51:F51)=2)))*(B51=0)+(REST(A51+1;7)=2)*(ZÄHLENWENN(FT;A51+1)-MAX((TEXT(A51+1;"MMTT")={"1224"."1231"})*2)<1)*(WENN(D51>C51;0;D51)*(ANZAHL(C51:D51)=2)+WENN(F51>E51;0;F51)*(ANZAHL(E51:F51)=2)))*F$14)

Spalte H
=WENN(B23="Feiertag";C8+REST(D23-C23;1)*(ANZAHL(C23:D23)=2)+REST(F23-E23;1)*(ANZAHL(E23:F23)=2);REST(D23-C23;1)*(ANZAHL(C23:D23)=2)+REST(F23-E23;1)*(ANZAHL(E23:F23)=2))

Abgesehen von der Spalte H funktioniert auch alles. In dieser Spalte möchte er mir nicht die Stunden aus C8 dazurechnen. Werde es aber weiter versuchen.

Gruß Andi

[little76]

 
Hallo Werner,

super vielen Dank, habe sie kopiert und eingefügt...

Inzwischen habe ich weitere Änderungen vorgenommen...

Spalte O
=WENN(G23="";"";N23-G23)

Spalte G
=WENN(D23<"";C$8*(REST(A23-1;7)>1);"")

ab Spalte K51
=WENN(A51="";"";(B51=0)*(REST(A51;7)=1)*((WENN(D51I$16;MIN(D51;K$16);I$16))-MAX(I$16;C51))*(D51<>"")*(C51I$16;MIN(F51;K$16);I$16))-MAX(I$16;E51))*(F51<>"")*(E51)

ab Spalte J51
=WENN(A51="";"";(((REST(A51;7)=2)*((WENN(D51>C51;D51;1)-C51)*(ANZAHL(C51:D51)=2)+(WENN(F51>E51;F51;1)-E51)*(ANZAHL(E51:F51)=2)))*(B51=0)+(REST(A51+1;7)=2)*(ZÄHLENWENN(FT;A51+1)-MAX((TEXT(A51+1;"MMTT")={"1224"."1231"})*2)<1)*(WENN(D51>C51;0;D51)*(ANZAHL(C51:D51)=2)+WENN(F51>E51;0;F51)*(ANZAHL(E51:F51)=2)))*F$14)

Spalte H
=WENN(B23="Feiertag";C8+REST(D23-C23;1)*(ANZAHL(C23:D23)=2)+REST(F23-E23;1)*(ANZAHL(E23:F23)=2);REST(D23-C23;1)*(ANZAHL(C23:D23)=2)+REST(F23-E23;1)*(ANZAHL(E23:F23)=2))

Abgesehen von der Spalte H funktioniert auch alles. In dieser Spalte möchte er mir nicht die Stunden aus C8 dazurechnen. Werde es aber weiter versuchen.

Gruß Andi

Die Smilies habe ich nicht eingefügt

[WillWissen]

Hi Andi,

bitte nicht immer den gesamten (Vor)Beitrag zitieren. Benutze für deine Antworten bitte den Button "Antworten" unterhalb der Beiträge.
Die Smilies kannst du deaktivieren, indem du unterhalb des Antwortformulars bei den Beitragsoptionen im zweiten Kästchen einen Haken setzt.

[neopa]

Hallo Andi,

wenn Du meinen Formelvorschlag für die Spalte B genommen hast, dann steht in Spalte B als Wert eine 1 welche als "Feiertag" angezeigt wird oder eine 0 welche als "Leerzelle" oder es steht eine -1 welche als "frei" angezeigt wird.

Demzufolge darfst Du B## nicht nach "Feiertag" abprüfen sondern nach 1

Deine WENN()-Formelvorsatz in Spalte O halte ich nicht für gut. Damit würdest Du ausschließen, dass an einem Tag wo nicht gearbeitet werden soll, aber gearbeitet wird demjenigen die IST-Stunden nicht gewertet wird.

Wenn Du konsequent mit dem benutzerdefinierten Zahlenformat: [h]:mm;;  arbeitest, brauchst Du auch ansonsten nicht den WENN()-Formelvorsatz.


@Sepp.

möglicherweise wird die Zeitabrechnung verschieden gehandhabt. Ich kenne es so, dass wenn über Mitternacht gearbeitet wird, auch die Zeiterfassung so vorgenommen wir. D.h. an dem Tag, in der die Arbeit begonnen wurde. Außerdem schrieb ich auch, dass nicht gewährleistet ist, dass wirklich immer alle Tage lückenlos gelistet sein könn(t)en.

Den Feiertag am Tag der Stundenerfassung wird in meinem Formelvorschlag nicht über ZÄHLENWENN() sondern über den Wert in der von Andi vorgegebenen Hilfsspalte B berücksichtigt.

Sicherlich lassen sich einige Formeln auch noch etwas vereinfachen/ kürzen.

[Josef B]

Hallo Werner

So funktioniert das mit den Feiertagen natürlich auch.
Zwei unterschiedliche Arten der Feiertagsangaben für die Berechnung der Feiertagsstunden finde ich aber nicht  so optimal.
 
Gruss Sepp

[echo]

Bis zur Berücksichtigung vom Feiertag bin ich noch gar nicht gekommen und habe diese auch nicht berücksichtigt
Ist dies unter der jetzigen Konstellation überhaupt noch umsetzbar?

Hallo Andy undenkbar ist es nicht, aber auch nicht ganz einfach.

Ich habe vor einiger Zeit vergleichbares erstellt und bin so vorgegangen,
das ich zuerst für jeden denkbaren Zuschlag eine Hilfsspalte erzeugt habe um die jeweiligen Zuschläge zu berechnen.
Anschließend habe ich die summen je nach Relevanz addiert.

bei Schichten nach 24:00Uhr es gibt mE. noch weitere Fragen die am Wochenende / Feiertag im Raum stehen:
1. Möglichkeit: Jeder Tag beginnt / endet um 00:00Uhr / 24:00Uhr Arbeitszeiten nach 24:00Uhr werden dem folgenden Tag zugerechnet.
oder: Gleiche Berechnung vom kompletten Dienst. Alle Zuschläge auch nach 24:00Uhr wie bei Dienstbeginn.
Im ersten Fall müsstest du die Nachtschicht auch noch auf die zwei Tage verteilen.

Ich habe zwar nicht die Zeit um da weiter zu helfen aber zum abgucken hilft vielleicht  meine Version weiter.
ich hänge sie mal an
Gruß Holger

[neopa]

Hallo Andi,

hier nun noch die Formel für den Nachtzuschlag. Bei dieser gehe ich davon aus, dass auch nur bis max früh 6:00 am Folgetag gearbeitet wird. Anderenfalls muss entweder die Formel erweitert werden oder z.B. am 24.12/31.12 oder an dessen Vortag die Arbeitszeit die bis nach 6:00 Uhr erfolgt erfasst werden.

In L23

Code:

=(B23=0)*(REST(A23;7)<>2)*((D23>K$16)+(D23<>"")*(D23<C23))*(REST(D23-MAX(C23;7/8)*(C23>0);1)+REST(F23-MAX(E23;7/8)*(E23>0);1))*F$15

wieder mit dem benutzerdefinierten Zahlenformat [h]:mm;; 
(mehr dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=71 )
und Formel wieder nach unten kopieren.