Office 2003: Berechnung der Arbeitszeit mit Berechnung der Mehr - / Minderstunden

[beatgordon]

Hallo,

ich versuche mich gerade an einer Lösung zur Erfassung von Arbeitszeiten mit Auswertung der Stunden.
Bis jetzt habe ich je eine Spalte für Datum, AZ Beginn, AZ Ende, Pause, AZ Tag (Summe von AZ Ende - AZ Anfang - Pause * 24 - Zellen in Zahlen formatiert ), Wochenstunden kumuliert (Summe aus allen Wochentagen, Ergebnis auch in Zahlen angezeigt). Jetzt meine Frage dazu :
Jetzt würde ich gerne eine Spalte haben wollen mit Mehr - / Minderstunden das heisst eine Zelle in der die Stunden über der vereinbarten Wochenarbeitszeit von über 37,5 Std mit grünen Hintergrund angezeigt werden ( z.B. Wochenarbeitszeit 40 Std, 37,5 ist ausgemacht dann als Anzeige 2,5 Std mit grünen Hintergrund) und anders rum wenn es weniger sind die Zahl in rot.

Gibt es vielleicht sogar noch eine bessere Lösung der Formel um die Zeiten vielleicht auch in Std. Min. anzeigen zu lassen ?

Damit bin ich leider etwas überfordert wer kennt sich damit aus ? Freue mich über euren Input! Vielen Dank!

[steve1da]

Hola,

das ist alles recht problemlos möglich. Zeig doch mal anhand einer Beispieldatei was du schon hast.

Gruß,
steve1da

[beatgordon]

Anbei mal die Datei wie Sie jetzt aussieht. Das große weiße Feld bei Mehr/Minderstunde soll es so anzeigen wie beschrieben. Die BErechnung habe ich ja beschrieben von dem Rest. Vielen Dank für die Hilfe !

[steve1da]

Hola,

in H5:

Code: [Auswählen]

=F11-37,5
Dann in H5 gehen, Start - Bedingte Formatierung - Neue Regel - Formel zur Ermittlung....

Hier legst du 2 Regeln an:

Code: [Auswählen]

=H5<0 für rot

Code: [Auswählen]

=H5>0 für grün

Allerdings musst du dass dann für jede Wochenabrechnung machen. Wenn du das an jedem Sonntag automatisert haben möchtest, einfach melden.

Gruß,
steve1da

[beatgordon]

Oh Mann, da hätte ich auch echt selber drauf kommen können ich habe das mit der bedingten Formatierung voll vergessen Vielen Dank für deine schnelle Hilfe! Aber jetzt hast du mich schon neugierig gemacht wie sich das jeden Sonntag automatisch addiert ! Eine extra Spalte für die kumulierte Mehr Minder Arbeit.. das wär schon Sahne! Verrätst du mir wie ich da dran geh ?
Viele Grüße, Julian

[steve1da]

Hola,

da nicht immer nur "Sonntag" in der Zelle steht, bezieh ich mich aufs Datum in Spalte A:

Code: [Auswählen]

=WENN(WOCHENTAG(A5;2)=7;SUMME(INDIREKT("E"&ZEILE()-6&":E$"&ZEILE()));"")
Gruß,
steve1da

[beatgordon]

Okay das verstehe ich jetzt nicht genau, sorry. Also wenn ich das so einfüge in die Liste wie du es gepostet hast macht es garnichts. Was muss ich denn da genau editieren für meine Liste ? Ich hab die Datei nochmal angehangen wie ich es jetzt gemacht habe. Aber da hab ich eben jede einzelne Zelle einzeln formatieren müssenum immer wieder eine aktuelle Gesamtübersicht der Stunden zu haben in Bezug auf die jeweilige Mehr - Minderstundenübersicht in der Spalte davor.  Wie würde das denn aussehen was du gepostet hast ?
Viele Grüße, Julian 

[steve1da]

Hola,

meine Formel kommt in F5.

Gruß,
steve1da

[beatgordon]

Also wenn ich diese Formel in F5 einfüge passiert garnichts bei mir.... Muss ich das noch formatieren ? VG

[steve1da]

Hola,

hast du sie auch nach unten gezogen?

Gruß,
steve1da

[beatgordon]

Jetzt hab ich es, das ist dann quasi die Alternative zu der "normalen" Summenberechnung die ich integriert hatte. Auch wenn ich jetzt etwas nervig erscheine aber das Problem mit der automatischen Addition der Mehr-/ Minderstunden wie im letzten Anhang zu sehen, geht das denn einfacher als ich es gemacht habe ? Ich habe jede Zelle in der die Stunden angezeigt werden in Spalte I einzeln formatiert ( zb. in I 11 =SUMME(H5) in I 17 dann =SUMME(H5+H12) usw. ) aber das bedeutet auch das ich diese Formel nicht einfach nach unten ziehen kann, zumindest stimmen dann die Zahlen nicht da es ja immer eine weitere Zelle ist dazu addiert werden muss. Hab das schon probiert. Vielen Dank für deine Hilfe habe jetzt schon viel gelernt Viele Grüße

[steve1da]

Hola,

ein ganz großes Problem dabei sind die verbundenen Zellen in Spalte H. Sehen toll aus, das wars dann aber auch schon. Damit in Formeln umzugehen ist schwer.

In H5 zum runterziehen:

Code: [Auswählen]

=WENN(WOCHENTAG(A5;2)=7;F5-37,5;"")
In I5 zum runterziehen:

Code: [Auswählen]

=WENN(WOCHENTAG(A5;2)=7;SUMME($H$5:H5);"")
Deine bedingten Formatierungen musst du dann anpassen.

Gruß,
steve1da

[beatgordon]

Anbei die fertige Datei, vielen lieben Dank für den Support!!!! Ich werde mich in diese WENN Formeln jetzt mal richtig vertiefen das ist sehr interessant! Viele Grüße, Julian

[Frank Furter]

hallo, dann solltest du dich unbedingt auch mit INDIREKT() - siehe hierzu..:

http://www.online-excel.de/excel/singsel.php?f=24

und ZEILE() auseinandersetzen... ... weil deine Formel in F5 und F6 mumpitz ist siehe auch die grafik...



alternativ kannst du für E5 und ff. diese nehmen..:

=WENN(A5="";"";WENN(REST(A5;7)=1;SUMME($E$5:E5)-SUMME($F$4:F4);""))

freilich geht anstatt REST() auch WOCHENTAG()...

[Frank Furter]

Hola,

da nicht immer nur "Sonntag" in der Zelle steht, bezieh ich mich aufs Datum in Spalte A:

Code: [Auswählen]

=WENN(WOCHENTAG(A5;2)=7;SUMME(INDIREKT("E"&ZEILE()-6&":E$"&ZEILE()));"")
Gruß,
steve1da

hi steve1da, ich wundere mich a) dass du (hier) INDIREKT() vorschlägst b) dass du sicher wider besseren wissens in der INDIREKT() formel einen gemischten bezug herstellst... ... seit wann tut das not..? und die formel in E5 eingesetzt ist freundlich ausgedrückt unglücklich (siehe auch in der foremlauswertung) und teste mal den 01.11.2015 (in A5)

[beatgordon]

Hallo Frank Furter, danke für deinen Input zu der Liste. Ich muss ehrlich gestehen ich bin wirklich ein Anfänger bei Excel und wollte mich mit der Liste einfach probieren. Ich verstehe nur nicht was jetzt der Fehler ist weil die Berechnungen ja eigentlich stimmen auf den ersten Blick. Auch bin ich mir nicht sicher ob du die Berechnung in E5 oder F5 kritisierst da du E5 schreibst aber F5 im Screenshot die Formel anzeigen lässt. Muss zugeben ich bin da ein wenig verwirrt  >Könntest du mir es noch ein wenig einfacher erklären wie ich die Liste verbessern kann oder wo der Fehler genau steckt ? Vielen Dank für die Hilfe vorab! VG, Julian

[Frank Furter]

hallo Julian, ich meine natürlich F5... sorry, wegen des missverständnisses... ich hatte eine funktionierende formel (getestet) gepostet... und der ausschnitt in der grafik ist selbsterklärend - schau mal auf das gelb markierte... ... dass deine formel nich funzt, kann man überprüfen (testen) wenn man z.b. in A5 den 01.11.2015 eingibt...