Clever-Excel-Forum

Hallo!

Ich bin neu hier und habe ein halbwegs dringendes Anliegen.
Hab eine fast fertige Arbeitszeitserfassung (siehe Anhang) und der Excel-Profi, der mir beim Erstellen der Tabelle tatkräftig geholfen hat, fällt nun krankheitsbedingt für unbekannte Zeit aus.
Leider ist er mitten im Lösungsprozess der letzten beiden Probleme 'ausgestiegen' und alleine komme ich da nicht weiter, weil ich einfach zu wenig Ahnung habe.
Vielleicht kann mir hier im Forum ja jemand helfen.

In der im Anhang befindlichen Datei gibt es u.a. das Voreinstellungsblatt und die monatliche Erfassungstabelle.
Nun gibt es Arbeitnehmer die zwar am Wochenende arbeiten, dies aber immer abwechslend mit einem Kollegen/einer Kollegin, so dass jedeR nur 14-tägig am, Wochenende arbeiten muss.
Das ist mit der Tabelle in ihrem derzeitigen Zustand nicht vernünftig voreinzustellen.
Darüber hinaus erhalten diese ArbeitnehmerInnen Zuschläge in Höhe von 25 bzw. 35% für die Wochenendarbeit (d.h., sie arbeiten z.B. 6 Stunden, bekommen aber 8 Stunden bezahlt).

Ich denke, zumindest die 14-tägige Wochenendarbeit müsste in der Spalte 'N' der Tabellenblattes 'Januar' in die Formel eingebaut werden.
Ich hatte an etwas gedacht, das sich vielleicht auf die Kalenderwoche bezieht, weil die Leute doch immer im 14-tägigen Wechsel arbeiten, also immer in der geraden oder in der ungeraden Kalenderwoche.
Aber ich hab keine Ahnung, welchen Befehl es dafür evtl. gibt.

Die Zuschläge müssten evtl. in dem Tabellenblatt mit den Voreinstellungen eingebaut werden, vielleicht geht es aber auch in besagter Spalten 'N' .
Ich hab einfach zu wenig Ahnung ...

Kann mir hier jemand helfen?

Vorab allerbesten Dank und schöne Grüße

Gabi

[attachment=12044]

Hallo Gabi,

hier mal ein Ansatz mit Formeln. Die Formeln kannst Du in Spalte B eintragen und dann rüber ziehen. Das mit dem Du und Ich in der Formel könnte man auch noch verrechnen, sodass man die Formel auch nach unten ziehen kann, ohne die Kollegen in die Formel einbauen zu müssen.
Allerdings, wenn Du die Zellen alle mit Formeln füllst, sollte man nichts manuell eintragen - denn dann sind die Formeln ja weg

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
1		01. Jan	02. Jan	03. Jan	04. Jan	05. Jan	06. Jan	07. Jan	08. Jan
2		So	Mo	Di	Mi	Do	Fr	Sa	So
3	Ich	Ich
4	Du								Du

Zelle	Formel
B2	=B1
B3	=WENN(WOCHENTAG(B$1;2)>6;WENN(REST(KALENDERWOCHE(B$1;21);2)=0;$A3;"");"")
B4	=WENN(WOCHENTAG(B$1;2)>6;WENN(REST(KALENDERWOCHE(B$1;21);2)=1;$A4;"");"")

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

Hallo Schauan und Gabi
Die Kalenderwoche zum Bestimmen der Samstagsarbeit zu verwenden, finde ich nicht sehr praktikabel. Im Januar und Dezember kann es schon vorkommen, dass zwei Kalenderwochen in Folge ungerade sind.
Siehe mein Beispiel:
 
Januar

style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; ">   " />

	A	B	C
1	Januar 2016
2
3	Tag		KW
4	Freitag	01	53
5	Samstag	02	53
6	Sonntag	03	53
7	Montag	04	1
8	Dienstag	05	1
9	Mittwoch	06	1
10	Donnerstag	07	1
11	Freitag	08	1
12	Samstag	09	1
13	Sonntag	10	1

Bedingte Formatierungen der Tabelle

style="font-family:Arial; font-size:10pt;  "> Zelle Nr.: / Bedingung Format

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
 

@ Gabi: Ich denke, man könnte das manuell durch Eingabe in der Spalte code  und entsprechender Anpassung der Formel in Spalte N realisieren.
Wie dringend ist denn dein Anliegen? Ich habe mir deine Tabelle gründlich angesehen, und bin zum Schluss gekommen, dass da doch einiges an Arbeit ansteht.
 
Gruss Sepp
 

Hallo Schauan und Sepp,

allerbesten Dank für Eure Ideen!

@ Schauan: ich werde deine Formel morgen in meine Tabelle einarbeiten und gucken, wie ich damit zurecht komme.
Hatte hier heute zu viel andere Arbeit und muss jetzt weg.
DANKE aber vorab schon mal!!!!

Doch Sepp hat natürlich recht mit seinem Einwand bezüglich der Kalenderwochen.
Dumm aber auch ...

@ Sepp: Deine Idee, das Problem über den Code zu lösen hört sich für mein laienhaftes Verständnis erst mal gut an!
Mir ist nur gerade nicht ganz klar, ob man das 'automatisieren' kann, so dass ich den Code nicht bei allen betreffenden Arbeitnehmern, die diese Wochenendarbeit leisten, manuell für's ganz Jahr vortragen muss.
Das wär' ein wenig umständlich ...

Du fragtest nach der Dringlichkeit:
Naja, wir wollen das ganze zum Jahresanfang 2018 einführen und brauchen vorher eine Testphase, weil wir hier sehr unterschiedliche Kenntnisstände bezüglich der Umgangs mit Excel haben.

Schöne Grüße
Gabi

Hallo Gabi, vielleicht ist es jetzt an der Zeit, deine Anfragen in den Foren zu verlinken..? Im Nachbar-Forum ist dies ja schon geschehen... Aber niemand arbeitet gern für den Papierkorb...

Hallo,

das mit der Kalenderwoche kann man machen, wenn man die Übersicht jedes Jahr neu beginnt und das manuell berücksichtigt. Eine andere Variante wäre natürlich eine Formel, die sich auf ein festes Startdatum bezieht. Ich mach das z.B. auch in Übersichten, wo es um das Schichtsystem geht und ich anzeige, wie die Anwesenheit dann in der jeweiligen Woche verteilt ist. Hier mal der Ansatz auf diese Frage bezogen. Der 4.1.2015 müsste entweder an irgendeine Stelle in der Datei oder man nimmt ihn gleich in die Formel. Der bleibt dann mindestens bis zur Rente fest, falls es dann noch diese Excelübersicht gibt

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
1	04.01.2015	01. Jan	02. Jan	03. Jan	04. Jan	05. Jan	06. Jan	07. Jan	08. Jan
2		So	Mo	Di	Mi	Do	Fr	Sa	So
3	ich	ich							ich

Zelle	Formel
B2	=B1
B3	=WENN(REST(DATEDIF($A$1;B$1;"D");7)=0;$A$3;"")

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

Hallo!

Hab' erst mal versucht herauszufinden, wie das mit der Verlinkung gemeint ist und geht.

Ich hoffe, es ist jetzt richtig, wenn ich den Link hier so einfüge:
http://ms-office-forum.de/forum/showthre...ost1816666

War echt nicht böse geimeint, dass ich in 2 Foren gefragt habe - aber hier ist halt erst mal nix passiert und da dachte ich, ich frag' woanders noch mal nach.

So, setzte mich jetzt daran, die Formeln von Schauan in meiner Tabelle einzufügen und auszuprobieren.

@ Schauan: Danke für Deine Ideen!!! :)))

Schöne Grüße
Gabi

Hallo André!

Danke für Deine Formeln!

Dass man die Formeln alljährlich zum Jahresanfang manuell anpassen kann, sofern zum Jahreswechsel 2 ungerade Wochen aufeinander folgen, hatte ich auch schon überlegt.
Irgendwie hänge ich an der Idee mit den Kalenderwochen...

Erst mal zu deiner 1. Formel vom 3.7., in der die Kalenderwoche berücksichtigt ist:
Wenn ich das richtig interpretiere, dann macht  der 1. Teil der Formel doch nichts anderes, als zu gucken, ob der Wochentag größer 6 ist, oder?
Und der 2. Teil deiner Formel sagt dann, dass in geraden Kalenderwochen das eine gemacht werden soll und in ungeraden Kalenderwochen das andere, oder?
Ich meine diesen Teil Deiner Formel:

WENN(REST(KALENDERWOCHE(B$1;21);2)=0;$A3;"")
WENN(REST(KALENDERWOCHE(B$1;21);2)=1;$A4;"")

Ich würde das gerne verstehen. Also vor allem den WENN-Teil der Formel.
Der DANN- und der SONST-Teil sind mir klar.

Magst Du Dir die Mühe machen und mir die einzelnen Befehle erklären?
=0 bzw. =1 bezieht sich wahrscheinlich auf gerade oder ungerade Kalenderwoche, oder?
Aber den Rest verstehe ich nicht ...

Ich denke, wenn ich diesen 2. Formelteil  in meine Tabelle im Tabellenblatt Januar in Spalte 'N' einbaue, dann klappt es.
Ich müsste nur im DANN-Teil der Formel "" eintragen, wenn nicht gearbeitet wird, oder?

Kannst Du oder kann mir hier sonst jemand sagen, wo ich das in meiner Spalte "N" (Tabellenblatt Januar)in die bestehende Formel einfügen muss?



Die Formel, die Du gestern gepostet hast verstehe ich irgendwie nicht ... wie ist das mit dem 4.1. gemeint?
Und von der Formel
=WENN(REST(DATEDIF($A$1;B$1;"D");7)=0;$A$3;"")
verstehe ich quasi nix ... :(

Sorry, aber ich bin echt kein Profi ... noch nicht mal ein bisschen ...

Vorab allerbesten Dank für Deine/Eure Mühe!
:)

Schöne Grüße
Gabi

Hallo Gabi,

Zitat:Wenn ich das richtig interpretiere, dann macht  der 1. Teil der Formel doch nichts anderes, als zu gucken, ob der Wochentag größer 6 ist, oder?

Ja, genau. Wenn man die Woche mit Montag beginnt, ist der Sonntag entweder mit >6 definierbar oder mit =7

Zitat:Und der 2. Teil deiner Formel sagt dann, dass in geraden Kalenderwochen das eine gemacht werden soll und in ungeraden Kalenderwochen das andere, oder?

Ja.

Zitat:=0 bzw. =1 bezieht sich wahrscheinlich auf gerade oder ungerade Kalenderwoche, oder?

Ja.
REST ist eine Funktion, die den Rest einer Division ausgibt. z.B.
REST(5;2) = 1, denn 5/2 = 2, Rest 1
REST(6;2) = 0, denn 6/2 = 3, Rest 0

Zitat:Die Formel, die Du gestern gepostet hast verstehe ich irgendwie nicht ... wie ist das mit dem 4.1. gemeint?
Und von der Formel
=WENN(REST(DATEDIF($A$1;B$1;"D");7)=0;$A$3;"")

Zum Rest habe ich ja gerade was geschrieben. DATEDIF liefert die Differenz zwischen zwei Daten, mit der Option "D" in Tagen. Ausführlicheres dazu findest Du u.a. bei Microsoft. 7 würde einen wöchentlichen Rhythmus beinhalten, für zwei Wochen müsste da eine 14 rein - siehe folgende Formel.
Ich gehe mit der Formel nun nicht mehr auf die Nummer der KW, sondern auf die Datumsdifferenz ab einen (fast) beliebigen Startdatum. Im folgenden habe ich es noch "verrückter" auf den Tag 2 gelegt, der ist schon über 100 Jahre her ... Das ist wie mit Stadionrunden laufen. Man kommt in jeder Runde an Start und Ziel vorbei, egal, ob man eine oder 100 läuft Du kannst diese Formel dann in jedem Monat verwenden, in anderen Dateien usw. und kommst immer auf einen kontinuierlichen Wechsel.

Allerdings kann die Formel nicht mithalten, wenn die Firma 3 Wochen Betriebsruhe hat und die Kollegen danach wechseln wollen. Das passt dann nicht zum 14-Tage-Rhytmus. Eventuell muss ja die Woche vor der Betriebsruhe mit einem Sonntagsdienst abgeschlossen werden. Dann muss die Kollegin in der Woche vor der Betriebsruhe und der Woche danach ran.

Für Deine Spalte N hab ich mal noch was undurchschaubareres zusammengebaut. Mit INDEX und Vergleich ersetze ich hier Dein BEREICH.VERSCHIEBEN. Da die Funktion VERGLEICH ein Problemchen mit leeren Zellen hat, musste ich den Bereich bis zur letzten Zelle mit ein wenig Aufwand und weiteren INDEX sowie INDIREKT und ADRESSE erst mal "berechnen".
Dafür ist das nun kürzer. Zum Test habe ich mal noch eine Arbeitszeitverkürzung ab dem 4.1. eingetragen.
Wenn auch Samstags gearbeitet werden soll, muss man den Bedingungsteil entsprechend anpassen. Dort habe ich jetzt ODER verwendet, um die Wochentage und die Sonntagsdienste zu berücksichtigen.

Arbeitsblatt mit dem Namen 'Januar'
	N
4	08:00:00
5	08:00:00
6	08:00:00
7	05:00:00
8	05:00:00
9	05:00:00
10	00:00:00
11	00:00:00
12	05:00:00
13	05:00:00
14	05:00:00
15	05:00:00
16	05:00:00
17	00:00:00
18	05:00:00

Zelle	Formel
N4	=WENN(ODER(WOCHENTAG(A4;2)<6;REST(DATEDIF(2;A4;"D");14)=0);INDEX(Voreinstellungen!$C$12:$C$16;VERGLEICH(A4;INDEX(Voreinstellungen!$B$12;1;1):INDEX(INDIREKT("Voreinstellungen!" & ADRESSE(VERWEIS(2;1/(Voreinstellungen!$B$12:$B$16<>"");ZEILE(Voreinstellungen!$B$12:$B$16));2));1;1);1);1)/5;0)

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

Hallo Gabi

Zitat:Du fragtest nach der Dringlichkeit:
Naja, wir wollen das ganze zum Jahresanfang 2018 einführen und brauchen vorher eine Testphase, weil wir hier sehr unterschiedliche Kenntnisstände bezüglich des Umgangs mit Excel haben.

Ich denke, so wie du die Sache angehst sollte das eigentlich gelingen.
 
Und nun zur Wochenendarbeit
Ich würde den Starttag für das "14 Tage Intervall" in die Tabelle Voreinstellungen integrieren.
Darauf kannst du dann aus allen Monatstabellen mit einer Formel zugreifen.
Die Frage ist dann nur, gibt es  durch Ferien oder Krankheit oder aus anderem Grund eventuell Verschiebungen, die man im folgenden Intervall auch beachten muss?

Zitat:Darüber hinaus erhalten diese ArbeitnehmerInnen Zuschläge in Höhe von 25 bzw. 35% für die Wochenendarbeit (d.h., sie arbeiten z.B. 6 Stunden, bekommen aber 8 Stunden bezahlt.

Kannst du uns dazu etwas mehr verraten.
Und arbeiten diese Leute an den Wochenenden immer zwei Tage?
 
Einen schönen Tag wünscht
Sepp