Excel Zeiterfassung

[Der Omnipoet]

Ich verfeinere grade meinen Stundenzettel für die Firma und stoße da auf ein mir unlösbares Problem, vlt. kann mir hier jemand helfen.

Folgendes: Der Stundenzettel wird in Kalenderwochen geführt und mein Chef legt Wert darauf nicht nur einfach die Stunde pro Tag zu erfassen, sondern auch woran man wie lange gearbeitet hat. Dafür gäbe es heutzutage auch einige einfache Tools. Aber da ich sowieso schon vor längerer Zeit einen recht übersichtlichen und gut funktionierenden Stundenzettel gemacht habe der alles Wichtige so weit abdeckt, ist das schlicht unnötig. Ein Problem hat der Stundenzettel aber, er bildet kein Stundendefizit ab, anders gesagt, keine Minusstunden. Das brauche ich aber um ja auch den Abbau von Überstunden darstellen zu können, das geht bisher nur über einen Zeitausgleich über 8 Stunden (Also Überstundenurlaub dies das) Nicht aber wenn ich mal nen Tag früher gehe oder so. Das wäre recht einfach zu lösen, indem die Überstunden sich mit den Arbeitsstunden verrechnen und solange im Minus sind, bis das Soll erfüllt ist. Also die Überstunden zu jeder KW bei -30 stehen. Ja lange Rede kurzer Sinn, die Formel für Überstunden sieht aktuell so aus

=WENN(F42*24>Stammdaten!D9;F42-Stammdaten!D9/24;"-")

Aktuell passiert da folgendes, Solange keine Überstunden festgestellt / verrechnet werden, gibt das Feld "-" aus also nichts im Grunde, was ich aber brauche ist, das dieses "-" ausgegeben wird wenn ich die Überstunden nulle, also dort wie im Beispielbild eig. -02:00 stehen müsste, da noch 2 Stunden Arbeitszeit zu verrichten sind um das Soll zu erfüllen, andersherum genauso. Mache ich 2 Überstunden müsste dort +02:00 stehen, dass funktioniert ja auch aktuell schon, nur die Minusstunden bekomme ich nicht hin. Das liegt auch an der Art und Weise wie ich die Zeitangabe ausgeben, nämlich als HH:MM, das ist aber leider wichtig, da wir hier in Viertelstunden abrechnen und daher die Zeitrechnung in Kommavariante für meinen Chef nicht in Frage kommt.

TL;DR
Der Code muss so funktionieren das er solange Minusstunden anzeigt bis die Sollstunden erfüllt sind, erst wenn Soll und Überstunden gleichauf sind gibt das Feld ein "-" aus und entstehen Überstunden geht die Anzeige ins Plus.


Ich hoffe ich konnte das halbwegs gut erklären und jemand kann mir helfen.

Kurze Formellegende:
F42 = Arbeitsstunden / Woche:
Stammdaten!D9 = Eingabemaske der Sollstunden

 

[one]

Lade mal einen anonymisierten sheet hoch, das wird die Sache extrem vereinfachen.

 

[Mäxchen]

Vielleicht klappt sowas?

=WENN(F42*24 < Stammdaten!D9;"-";)&TEXT(ABS(F42-Stammdaten!D9/24);"hh:mm")

 

[obedi]

Ja genau die ABS Funktion macht es. Ich nutze noch eine vereinfachte Eingabe (Spalte A) in welcher ich die Zeit ohne Formatierung direkt hintereinander via Nummernblock eingebe:

B18    =ZEIT(LINKS(A18;2);RECHTS(A18;2);)
B19    =ZEIT(LINKS($A20;2);RECHTS($A20;2);)
B20    =ZEIT(LINKS($A22;2);RECHTS($A22;2);)
B21    =ZEIT(LINKS($A24;2);RECHTS($A24;2);)
B22    =ZEIT(LINKS($A26;2);RECHTS($A26;2);)

C18    =ZEIT(LINKS($A19;2);RECHTS($A19;2);)
C19    =ZEIT(LINKS($A21;2);RECHTS($A21;2);)
C20    =ZEIT(LINKS($A23;2);RECHTS($A23;2);)
C21    =ZEIT(LINKS($A25;2);RECHTS($A25;2);)
C22    =ZEIT(LINKS($A27;2);RECHTS($A27;2);)

D18    =C18-B18
D19    =C19-B19
D20    =C20-B20
D21    =C21-B21
D22    =C22-B22
D23    =C23-B23
D24    =C24-B24
D25    =C25-B25
D26    =C26-B26
D27    =C27-B27
D28    =C28-B28
D29    =C29-B29
D30    =SUMME(D18:D29)
D31    =WENN(D30>F2;ABS(F2-D30);TEXT(ABS(D30-F2);"-hh:mm"))

- Spalte A ist als 'Nur Text' formatiert
- Spalte B  bis D  sind als 'Benutzerdefiniert: hh:mm' formatiert

J2    =WENN(LINKS(I2)="-";TEIL(I2;2;99)*-1;I2)
J3    =WENN(LINKS(I3)="-";TEIL(I3;2;99)*-1;I3)
J4    =WENN(LINKS(I4)="-";TEIL(I4;2;99)*-1;I4)
J5    =WENN(LINKS(I5)="-";TEIL(I5;2;99)*-1;I5)
J6    =WENN(LINKS(I6)="-";TEIL(I6;2;99)*-1;I6)

I7    =TEXT(ABS(SUMME($J$2:$J$6));WENN(SUMME($J$2:$J$6)<0;"-";) &"hh:mm")

- Spalte I ist ebenfalls als 'Benutzerdefiniert: hh:mm' formatiert

gleiches Spiel für den Monat:

Spalte P:
=WENN(LINKS(K45)="-";TEIL(K45;2;99)*-1;K45)
=WENN(LINKS(K46)="-";TEIL(K46;2;99)*-1;K46)
=WENN(LINKS(K47)="-";TEIL(K47;2;99)*-1;K47)
=WENN(LINKS(K48)="-";TEIL(K48;2;99)*-1;K48)
=WENN(LINKS(K49)="-";TEIL(K49;2;99)*-1;K49)

Spalte R:
=TEXT(ABS(SUMME(P45:P49));WENN(SUMME(P45:P49)<0;"-";) &"hh:mm")

 

[Der Omnipoet]

Hey Leute! Sry das ich mich so lang nicht gemeldet habe, ich war leider sehr eingebunden. Erst einmal vielen Dank für die Anteilnahem und Hilfestellung. Ich habe im Laufe der letzten Woche nochmals an der Datei rumgebastelt und mich anschließend für einen komplett anderen Lösungsweg entscheiden, der deutlich einfacher ist. Damit habe ich also das eine Problem gelöst bekommen, ... nun habe ich ein neues

Folgendes:
Ich habe mich dazu entschlossen Ausgleichszeit statt in Tagen eben auch in Zeit HH:MM einzugeben, das schafft mehr Vielseitigkeit und Möglichkeiten einen Zeitausgleich einzutragen. Nun wird in der Ausgleichzeile nichts anderes gemacht als zu prüfen ob weniger als Soll gearbeitet wurde, wenn weniger als Soll gearbeitet wird, gilt das als Ausgleichszeit und wir in einer anderen Spalte mit den Überstunden gutgeschrieben.
Nun ist es aber so dass aufgrund meiner Formel die Ausgleichszeit automatisch bei plus 30std steht, das bekomme ich nicht so richtig in den Griff. Am besten wäre es, wenn erst dann die Ausgleichspalte anfängt zu arbeiten, wenn ein wert über 0 in der Arbeitszeitspalte auftaucht. Sodass solang es keinen Wert bei Arbeitszeit gibt, die Ausgleichspalte ebenfalls 0 wiedergibt. Ich halte mich da an @one seinen Rat und habe mal ein anonymisiertes Sheet hochgeladen.

 

[Der Omnipoet]

Ist gelöst:

Feld D19:

=WENN(B19=0;0;WENN(B19<E8;E8-B19;0))