Berechnung Anzahl Stunden in Abhängigkeit von Variablen

[carhug]

Hallo zusammen,

ich benötige Hilfe zur Berechnung der Anzahl von Stunden zwischen vorgegeben Uhrzeiten in Abhängigkeit von Variablen.

Prinzipiell geht es um die Berechnung von Zuschlägen (Nachtzuschlag, Feiertagszuschlag und Sonntagszuschlag)
Die Berechnung der Zuschläge für die Arbeitszeit erfolgt in einer separaten Spalte, hierbei geht es nur um die Berechnung der Stunden in Spalte K, L und M (siehe Anhang) für welche Zuschläge bezahlt werden müssen.

Somit bräuchte ich quasi eine Formel:
Wenn($C4=WW; dann berechne mir die Anzahl der Stunden zwischen 20:00 - 06:00 für den angegeben Arbeitszeitraum)
Wenn($C4=WS; dann berechne mir die Anzahl der Stunden von Arbeitsbeginn bis 00:00 ABER erst ab 20:00 UND die Anzahl an Stunden ab 00:00 bis Arbeitsende)
usw.

Vielleicht nochmal zur Erklärung der Berechnung der Stunden:
* Nachtzuschlag wird nur von 20:00 bis 06:00 bezahlt
* Feiertags- und Sonntagszuschlag gelten jedoch immer von 00:00 bis 00:00 Uhr (somit alle Stunden)
* Bsp. WS = Wenn Arbeitsbeginn am Samstagabend um 19:00 bekommt man 20:00 bis 00:00 den Nachtzuschlag und ab 00:00 bis Arbeitsende den Sonntagszuschlag.

Ich bin für jede Hilfe und auch andere Lösungansätze dankbar!

[Sigi.21]

Hallo curhug,

sucht du eine Formellösung? Mit Formeln ist das schwierig, wird gar ein Formelmonster.
Kann es deshalb auch eine VBA-Funktion sein? Da lässt sich eher was machen.

Gruß
Sigi

[carhug]

Hey Sigi,

Leider hab ich von VBA Codes gar keine Ahnung. 
Die „Monsterformel“ würde ich immerhin verstehen und jederzeit ggf. anpassen können ohne weitere Hilfe.

Viele Grüße

[Guschti]

Hallo

Es sind ja schon die Berechnungen für die verschiedenen Stundenberechnung nicht so einfach.
Es beruht vor allem auch auf ein Datum hin.

In E2 Nachstunde

Code:

=WENN(A2="";"";(MAX(0;MIN(D2+(C2>D2);MAX(($M$1>$N$1);$N$1))-MAX(C2;$M$1))+MAX(;MIN(D2;MAX(($M$1>$N$1);$N$1))-MAX(;$M$1))*(C2>D2)+MAX(;MIN(D2+(C2>D2);MIN(($M$1>$N$1);$N$1))-MAX(C2;))+MIN(D2;MIN(($M$1>$N$1);$N$1))*(C2>D2)))

In F2 Sonntagstunden:

Code:

=WENN(UND(WOCHENTAG(B2;2)=7;C2<D2);REST(D2-C2;1);WENN(UND(WOCHENTAG(B2;2)=7;C2>D2);REST(1-C2;1);WENN(UND(WOCHENTAG(B2+1;2)=7;C2>D2);D2;)))

In G2 Feiertagstunden: (wobei die Feiertage auf anderem Blatt stehen und der Bereich im Namens-Manager als FTAG benannt wird)

Code:

=WENN(UND(ZÄHLENWENN(FTAG;B2);C2<D2);REST(D2-C2;1);WENN(UND(ZÄHLENWENN(FTAG;B2);C2>D2);REST(1-C2;1);WENN(UND(ZÄHLENWENN(FTAG;B2+1);C2>D2);D2;)))

Nachtstundebeginn in Zelle M1, Nachtstundenende in Zelle N1

Gruss Guschti

[Sigi.21]

Hallo carhug,

so schwierig ist VBA gar nicht. (s. Mappe)

Gruß
Sigi

[carhug]

Danke euch Beiden!

Die VBA Lösung sieht natürlich bei weitem einfacher aus
@Sigi: könntest du dir nochmals die Datei (siehe Anahng) anschauen?

Hab hier 2 Zellen gelb markiert... da scheint noch irgendetwas nicht ganz zu passen.

Und ich würde, sobald ich mich in den nächsten Tagen genauer damit befassen kann, nochmal auf deine Hilfe zurück kommen
damit ich auch was davon verstehe was du mir hier gebastelt hast.

Danke nochmals!

Viele Grüße

[Sigi.21]

Hallo carhug,

in der Tat, da war noch ein kleiner Fehler.
Das A und O beim Entwickeln ist das Testen! Mit der Null hatte ich nicht gerechnet.

Anbei korrigierte Version (s. Mappe). Ich hoffe, dass alle Varianten nun berücksichtigt sind.

Grüße
Sigi

[carhug]

Hi Sigi,

leider hab ich noch einen weiteren kleinen Fehler im Excel gefunden.
Wenn ich könnte, würde ich das natürlich selbst beheben....

Habe das Excel nochmal angehängt und die Fälle markiert.

Viele Grüße

[radagast]

Heje Excelfreunde, carhug


der Tabellenansatz birgt schon den Fehler in sich, da versucht wird, sofort lohn und Brot auszurechnen.

1) Hilfstabellenblätter anlegen und Kalender-, sowie Feiertagsberechnung und Fallunterscheidungen, wie "WW" usw., generieren... 
    wenn alles nach beliebiger Datumseingabe funktioniert, dann....
2) Fokustabellenblatt anlegen und Lohn berechnen. Wenn in dieser Tabelle(blatt) dann ein Startdatum (Arbeitsbeginn) eingegeben wird, werden
    über die Hilfstabellenblätter die verschiedenen Fallunterscheidung automatisch zur Lohnberechnung bereitgestellt...

---

[Sigi.21]

Hallo carhug,

mit den genannten Fällen verlässt du aber den ursprünglichen Definitionsbereich, nämlich den Schritt über Mitternacht! Die Kategorien (FS, WS, WW, etc.) implizieren ja geradezu den Übergang von einem zum nächsten Tag!! So hatte ich es auch programmiert.

Ich habe noch Anpassungen vorgenommen, sodass es auch für deine genannten Fälle passen sollte. (s. Mappe)

Grüße
Sigi