Benötige Hilfe für Fomel zur Auswertung der Arbeitszeit

[Swobi1990]

Hallo,

ich stehe aktuell ein wenig auf dem Schlauch.

In unserer Werkstatt wird eine Tabelle geführt mit mehreren Parametern, die ich versuche ein wenig zu automatisieren.

Es gibt 3 verschiedene Mitarbeiter, die jeden Tag auf ein Sollwert von 7,75 Std kommen müssen, außer Freitags, da sind es nur 6,5 Stunden.

Die Liste ist eine einzige Exceldatei für alle Mitarbeiter wo vom kaufmännischen Mitarbeiter die täglichen Stunden eingetragen werden in den Spalten "Arbeitsstunden" (für den Auftrag des Kunden anhand der Fahrgestellnummer), außerdem werden sog. Werkstattstunden hin und wieder zur Materialbesorgung, Aufräumarbeiten etc in der gleichen Zeile notiert.

Die Schwierigkeit besteht zudem dadurch, dass teilweise mehrere Zeilen pro Tag gefüllt werden für verschiedene Kundenaufträge und auch verschiedene Mitarbeiter manchmal am gleichen Kundenauftrag arbeiten, teilweise am gleichen Tag, teilweise an einem anderen. Die Auswertung wie lange ein Kundenauftrag bearbeitet wurde und von wem ist hier nicht von Relevanz und wird anders ausgewertet.

Ich möchte nun nur Soll / Ist Werte für jeden Tag und pro Monat errechnen. und dadurch gleichzeitig ein Überstundenkonto führen.

Vielleicht hilft meine Datei zum besseren Verständnis, für alles rot markierte fehlt mir die Formel bzw. der richtige Ansatz.

Vielen Dank vorab

[MisterBurns]

die jeden Tag auf ein Sollwert von 7,75 Std kommen müssen, außer Freitags, da sind es nur 6,5 Stunden.

Was ist denn das für eine lustige Firma, wo 37,5 Stunden gearbeitet wird? Im echten Leben wird jeden Tag ein Schnitt von 7,7 Stunden gearbeitet - auch Freitags. Das ergibt 38,5 Stunden Regelarbeitszeit.
Dass dann tatsächlich Mo-Do länger und Fr kürzer gearbeitet wird, steht auf einem anderen Blatt.

[Swobi1990]

Ist ein Handwerksbetrieb, ganz normal: Jeden Tag 07:30 Uhr bis 16:15 Uhr mit einer Stunde Pause über den Tag, außer Freitags, da ist um 15 Uhr Ende. Denke so außergewöhnlich ist es nicht. Unabhängig davon habe ich durch die freundlich Anmerkung leider noch nicht die Formel lösen können   :21:  :D

[steve1da]

Hola,

da du ja eh schon alle Stunden des Jahres auf einem Blatt hast kannst du dir die Daten per Sverweis holen und verrechnen:

https://www.online-excel.de/excel//singsel.php?f=9

Gruß,
steve1da

[Swobi1990]

Wie verrechne ich denn?

ZB nur je Mitarbeiter und wenn mehrere Ergebnisse pro Tag sind.

In meinem Beispiel ist das mit dem Sverweis ja für Feiertag und Urlaub gut mit Sverweis zu rechnen, aber der rest ?

[Kuwer]

Hallo,

als Ansatz mit SUMMEWENNS() für O9. Benötigt eine zusätzliche Spalte für Monat (jetzt F:F):

Stunden

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2019

37,5 Std Woche (Mo-Do 7,75 Std, Fr. 6,5 Std)

Monat

2

Sollwerte:

Januar 19

3

Arbeitsstunden

4

143,5

5

6

Monat

7

Istwerte:

Januar 19

8

Monteur

Arbeitsstunden

9

Fgst.-Nr.

Monteur

Datum

Kunde

Arbeitsstunden

Werkstattstunde

Überstunden

Urlaub

Feiertag

Max Mustermann

17,5

10

Max Mustermann

01.01.2019

1

Wenn es ein Feiertag ist, fülle bitte hier vorab aus, also 7,75

11

Michael Meyer

01.01.2019

1

12

Jürgen Müller

01.01.2019

1

Formeln der Tabelle

Zelle Formel O4 =SVERWEIS(Stunden!$O$2;Kalender!$A$376:$E$388;2;0) O9 =SUMMEWENNS(H:H;D:D;$N$9;F:F;MONAT($O$7)) F10 =MONAT(E10) F11 =MONAT(E11) F12 =MONAT(E12)

Gruß Uwe

[Swobi1990]

Super, das hat schon sehr geholfen ! Vielen Dank

Nun fehlt nur noch eine letzte Kleinigkeit:

1. Die Spalte Über-/Unterstunden sollen sich anhand der "Kalender Matrix" je Mitarbeiter und Tag selber berechnen.
--> in meinem Beispiel ist bei Max Mustermann am 07.01. die Summe von Arbeits- und Werkstattstunden 8,75, also 1h mehr als die eigentliche Arbeitszeit lt. "Kalender Matrix", dieser Wert soll nach Möglichkeit in der letzten für Max Mustermann eigetragenen Zeit am 07.01. bei Überstunden mit +1 stehen
--> in meinem Beispiel ist bei Max Mustermann jedoch am 08.01. die Summe von Arbeits- und Werkstattstunden nur 5,75, also 2h weniger als die eigentliche Arbeitszeit lt. "Kalender Matrix", dieser Wert soll nach Möglichkeit in der letzten für Max Mustermann eigetragenen Zeit am 08.01. bei Überstunden mit -2 stehen



Danke vorab

[Klaus-Dieter]

Hallo Swobi,

aus meiner Sicht hast du das Projekt viel zu kompliziert aufgebaut. Hier mal ein Formelbeispiel für die Berechnung der Urlaubstage:

Stunden

	E	F	G	H	I	J	K
9	Datum	Kunde	Abwesend	Arbeitsstunden	Werkstattstunde	Überstunden	Urlaub
10	Di. 01.01.2019		U				7,5

Formeln der Tabelle

Zelle Formel K10 =WENN(G10="U";SVERWEIS(WOCHENTAG(E10);{1.0;2.0;3.7,5;4.7,5;5.7,5;6.6,5;7.0};2;FALSCH);0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Das kann man Sinngemäß auch bei den Feiertagen einsetzen. Die Lösung mit den Gesamtstunden per SVerweis ist auch nicht so glücklich. Das würde ich alles so gestalten, dass man ohne das Blatt Kalender auskommt. Die Spalte Abwesenheit habe ich eingefügt. Wie sollen Krankheitstage, Seminare, Bildungsurlaub usw. erfasst werden?

[Swobi1990]

Hallo Klaus-Dieter,

vielen Dank auch für deine Hilfe, die Spalte habe ich erweitert nun auch eingebaut. Sie war wichtig und ich hatte es vergessen.

Vielleicht hast du recht und einiges würde sich noch einfacher gestalten lassen, für den aktuellen Moment würde ich es erstmal jedoch so belassen, da wir schnell damit "Live" gehen wollen.

Meinst du, es gäbe noch eine Lösung für den Punkt der Überstundenberechnung aus meinem Beispiel ? Dann würde nämlich erstmal alles klappen :)

Danke vorab

[Klaus-Dieter]

Hallo,

darüber muss ich noch mal nachdenken, da es pro Tag und Mitarbeiter mehrere Zeilen geben kann, ist das nicht ganz so einfach. Da die Zahl der möglichen Kunden sicher nach oben offen ist, und die Eingaben einzelner Mitarbeiter nicht unbedingt unter einander stehen, passt das nicht so gut in das Stundenblatt.