Microsoft Excel

Herbers Excel/VBA-Archiv

Nachtdienst und Wochenendzulage berechen

Betrifft: Nachtdienst und Wochenendzulage berechen von: Grog Rodriguez
Geschrieben am: 02.10.2020 20:06:46

Hallo liebe Excel-Freunde,


Ich bin schon eine Weile am Tüfteln und habe leider noch nicht Funktionierendes zustande gebracht.

Zu dem Thema gibts ja eine Menge Einträge. Für mein Bedürdfnis habe ich aber noch nichts gefunden.



Ich will die Nachtdienst und Wochenendzulage in meinem 6er-Dienstrad berechenen.


Im sheet "Jan"/SPalte E und F habe ich den Schichtplan mit Indexformel eingebettet.


Ich will in Spalte I die Nachtdienst- und Wochenendstunden berechnen:

Nachtdienstzulage gibts zwischen 20 Uhr und 6 Uhr.

Wochenendzulage fällt von Samstag 20 Uhr bis Montag 6 Uhr an.


Hier meine Datei,

https://www.herber.de/bbs/user/140605.xlsx


Vielen Dank im Vorraus

Betrifft: AW: Nachtdienst und Wochenendzulage berechen
von: ralf_b
Geschrieben am: 02.10.2020 21:19:08

die Datei ist ja ein Dauerbrenner.

wie sind denn deine Berechnungsschritte?
Schon eine mögliche Formelherleitung für deine Aufgabe vorhanden?

In welchen Foren hast du noch gefragt?

Betrifft: Ohne VBA
von: lupo1
Geschrieben am: 02.10.2020 22:00:35

https://www.herber.de/bbs/user/140608.xlsx (ca. 50 kb)

1. Strg-U nach dem Öffnen (!) bläst die Datei funktionsfertig auf. Nicht vergessen!
2. Gesetzliche Pausen D5:G5 können mit 0 überschrieben werden; sonst kürzen sie im zugehörigen Zeitfenster entsprechend.
3. Feiertage sind nicht berücksichtigt (da keine Anweisung dazu im Aufgabentext).
4. Das Ereignismakro kann verwendet werden, um die Endzeit auch ohne Datum eingeben zu können.
5. Für Nacht habe ich 25%, für WE 50% Zuschlag eingetragen.

Anm: Sigi und sulprobil haben sich mit der Thematik auf VBA-Ebene beschäftigt.

Ich habe die Datei des TE nicht geöffnet.

Betrifft: OT: Lupo, bitte inkl. STRG+U speichern
von: Sulprobil
Geschrieben am: 03.10.2020 11:55:54

Hallo Lupo,

Bitte speichere doch Deine Beispieldatei NACH STRG + U.
Dann funktioniert sie auch im UK und im US Excel Universum, denke ich. Ich gebe sie gern als Link in den entsprechenden Foren an, weil die meisten anderen "Formelfreaks" immer wieder Fehler machen.

Wenn es Dir recht ist, verweise ich dann auch gern von meiner Homepage darauf.
@Sigi: Das mit dem Link gilt auch für Dich, wenn Du magst. Deine Lösungen sind gut strukturiert, finde ich.

Viele Grüße,
Bernd

PS: Meine Hoffnung ist weiterhin, dass Microsoft bald eine interne Funktion analog sbTimeDiff zur Verfügung stellt.

Betrifft: old school
von: lupo1
Geschrieben am: 03.10.2020 12:36:30

Früher machte es (dateigrößenbezogen) sehr viel aus, wenn man Formeln erst selbst "expandierte".

Heute ist der Effekt eher bei 1:2, als 1:20 oder 1:200. Also könnte man das tatsächlich machen. Wobei ich durch die Hinzunahme der bundeslandbezogenen Feiertage schon jetzt bei knapp 50 KB bin. Herbers Grenze liegt bei 300. Als EDV-ler bleibt man möglichst weit weg von Grenzen.

Das mit US oder UK wundert mich aus dem Grund, weil dort diese Zuschläge, insb. die Pausenregelung, vermutlich anders und weniger kompliziert sein dürften (?). Aber das ist nur eine Vermutung.

Betrifft: Verlinkung: Vielen Dank! Aber:
von: lupo1
Geschrieben am: 03.10.2020 12:41:06

... die Zuschlags-Regelungen (bzw. Fragen dazu) sind derart individuell, dass jeweils an die Datei nicht unerheblich Hand angelegt werden muss, inhaltlich und strukturell.

Aber ich habe nichts dagegen.

Betrifft: OT: Danke
von: Sulprobil
Geschrieben am: 03.10.2020 12:51:42

Hallo Lupo,

Danke.
Die meisten Formelausprobierer bekommen nicht einmal die Nachtübergänge und die Feiertage richtig hin.
Da lohnt Deine Beispieldatei bereits, wenn die Pausen genullt werden.

Viele Grüße,
Bernd

Betrifft: AW: OT: @Bernd
von: Sigi.21
Geschrieben am: 03.10.2020 14:11:30

Hallo Bernd,

ich bin auch einverstanden, du kannst meine Lösung(en) gerne auf deiner HomePage verlinken. (Ich habe keine eigene.) Die Lösungen, die hier im Forum veröffentlicht werden, sind ja frei zugänglich.

Die Fkt. "ZeitAnt()" habe ich noch um einen Parameter ("Zeitart") erweitert. (s. Mappe)

Sigi

*)Ich war im falschen Zweig!

https://www.herber.de/bbs/user/140615.xlsm

Betrifft: Hier jetzt expandiert mit kleiner Doku dt/engl:
von: lupo1
Geschrieben am: 03.10.2020 15:33:54

https://www.herber.de/bbs/user/140616.xlsx (80 kb, also weniger als Faktor 1:2)

Kannst Du auch bei Dir direkt uploaden, damit Du nicht fremdlinkabhängig bist. Urhebernennung: "lupo1" reicht aus.

Deutsche Begriffe im Blatt dürfen anglisiert werden.

Hinweis: Die Formelkaskade =Dauer verzichtet auf WENNFEHLER(EXP(LN(; daher grds. auch vor 2007 einsetzbar.

Viele Grüße!
lupo1

Betrifft: OT: Danke, lupo1 und Sigi.21
von: Sulprobil
Geschrieben am: 03.10.2020 16:39:17

Hallo Sigi, hallo Lupo1,

Danke, ich habe Eure Ansätze unter Further Reading unten eingefügt:
https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/sbtimediff

Falls Ihr Änderungswünsche habt, bitte via Email über meine Webseite (siehe Punkt Contact).

Have fun,
Bernd

Betrifft: AW: OT: Lupo, bitte inkl. STRG+U speichern
von: Sigi.21
Geschrieben am: 03.10.2020 14:06:49

Hallo Bernd,

ich bin auch einverstanden, du kannst meine Lösung(en) gerne auf deiner HomePage verlinken. (Ich habe keine eigene.) Die Lösungen, die hier im Forum veröffentlicht werden, sind ja frei zugänglich.

Die Fkt. "ZeitAnt()" habe ich noch um einen Parameter ("Zeitart") erweitert. (s. Mappe)

Sigi

https://www.herber.de/bbs/user/140614.xlsm

Betrifft: AW: Ohne VBA
von: Grog Rodriguez
Geschrieben am: 04.10.2020 19:46:58

Vielen Dank!

Leider kann ich mit dem Ding nicht allzu viel anfangen. Ich hätte gerne verstanden, wie man die Zeiten so berechnet.
Ich habe da mit wenn- und index- funktionen herumgebastelt, weil ich ehrliuch gesagt auch nicht wirklich mehr verwenden kann.

Betrifft: AW: Weil es nicht ganz einfach ist,
von: Sulprobil
Geschrieben am: 05.10.2020 04:34:55

habe ich ja meine Funktion sbTimeDiff dafür geschrieben.

Mit Tabellenblattfunktionen ist es sehr fehleranfällig, die anrechenbaren Zeiten über Nacht, verschiedene Werktage und Feiertage, womöglich noch mit Pausenabzug zu berechnen.

Meines Erachtens hat dies bisher nur lupo1 richtig hinbekommen. Das Dumme ist nur, dass auch jedwede Anpassung an eigene Tabellen für die Anwender nicht einfach ist.

Nun, genau dafür sind benutzerdefinierte Funktionen wie meine ja gemacht worden. Die Komplexität wird verpackt, der Anwender muss die Funktion lediglich korrekt aufrufen.

Betrifft: Da geht es Dir wie 90% der Datei-Angucker so ...
von: lupo1
Geschrieben am: 05.10.2020 11:01:59

... und deshalb überlege ich, ob ich sie überhaupt noch als Lösung anbiete, weil eh immer Deine Antwort als Tenor zurückkommt. Manche bringen dann auch noch die Forderung, dass sie sich in ihre eigene Umgebung einpassen können muss - was sie immer tut, wenn man von der in sie zurückverweist.

Wie sulprobil schon schrieb: Das Thema ist nicht ohne.

In manchen Fällen gelten dann auch noch HlgAbend und Silvester erst ab 12 Uhr als Feiertag (oder freier Tag). Da gibt auch meine Lösung auf, denn sie betrachtet jeden Tag für sich. Bei den beiden halben Tagen müsste aber noch zusätzlich unterschieden werden, ob die erste Hälfte ein WT, SA oder SO wäre, und dann wäre möglicherweise dort die Zeit bis z.B. 6:00 Uhr vorher nicht unbedingt das Ende eines Nachtzuschlags, sondern eines abweichenden WE-Zuschlags. Das ginge dann für nur zwei Tage eines Kalenderjahres prophylaktisch in ca. 10-20 weitere "Spalten L:W".

Ein weiteres Problem, was über Regeln aber auch gelöst werden könnte, ist, wenn die fixen Zuschlagszeiten additiv noch von Überstundenregelungen wie 0-8, 8-10, 10+ Arbeitsstunden überlagert werden sollen. Es wird dann aber grenzwertig von der Verständlichkeit her. Unternehmen, die beides gleichzeitig geltend verwenden, gibt es vermutlich aber nicht so viele.

Fazit: Mir hat die Konstruktion Spaß gemacht, und ich nehme mir gern immer die ca. 20 Minuten, um sie auf ein spezielles Zuschlagszeiten-Problem angepasst auch weiterhin als Lösung anzubieten. Denn nur die speziellen Probleme lassen einen weitere Dinge erkennen.

Betrifft: AW: Heiligabend und Silvester erst ab 12 Uhr
von: Sulprobil
Geschrieben am: 05.10.2020 12:47:46

Dafür müsste man sbTimeDiff lediglich 3x aufrufen:
1. Für normale Feiertage (ohne 24.12. und ohne 31.12.) mit all denen in einer Feiertagsliste 0:00 - 24:00.
2. Für 24.12. und 31.12. mit nur diesen in einer Feiertagsliste 0:00 - 12:00.
3. Für 24.12. und 31.12. mit nur diesen in einer Feiertagsliste 12:00 - 24:00.

Es ist schon schade, dass lupo1's pfiffige Formellösung nicht so einfach verstanden und genutzt wird.

Betrifft: Hm ... da hast Du mich auf eine Idee gebracht.
von: lupo1
Geschrieben am: 05.10.2020 13:30:20

Man kann das auch bei der Formellösung in den bisherigen 4 Zeitschachtelzeilen L1:W4 als weitere Zeilen aufnehmen und in der Zuordnungsfunktion X5:... darauf verweisen. Vertikal schmerzt die Tabellenerweiterung nicht so sehr.

Danke für Deine Anregung!