Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Wie Feiertage in Formel für Stundenberechnung berücksichtigen?





Frage

Hallo Exel-Fans, ich habe ein Tabellenblatt zur Stundenberechnung erstellt und darin am Ende jeder Zeile (pro Mitarbeiter) folgende Formel zur Summenbildung: =WENN(ISTLEER(T5);REST(D5-B5;1)+REST(G5-E5;1); INDEX(Arbeitzeiten!$A$2:$H$8;VERGLEICH(SVERWEIS(A5;Mitarbeiter!$C$3:$F$21;3;FALSCH);Arbeitzeiten!$A:$A;0)-1;VERGLEICH(WOCHENTAG($A$2);Arbeitzeiten!$2:$2;0))) Kurzerklärung: A5: der Mitarbeiter B5+E5: Von (sprich Arbeitszeitbeginn) D5+G5: Bis (sprich Arbeitszeitende) T5: Kombinationsfeld für Auswahl K (Krank) oder U (Urlaub) Die Formel macht Folgendes: Wenn keine Auswahl in T5, dann werden die Von-Bis-Werte ausgerechnet. Wenn ein Eintrag in T5, dann wird die Sollarbeitszeit für den Mitarbeiter aus dem Blatt 'Arbeitszeiten' in Abhängigkeit von seiner Tarifgruppe aus dem Blatt 'Mitarbeiter' geholt. Nun meine Frage: Wie kann ich in dieser Formel die Feiertage berücksichtigen? Problem ist, dass der Mitarbeiter ja an einem freien Tag, welcher Feiertag und Wochentag ist, seine Sollarbeitszeit geschrieben bekommt. Diese kann allerdings nach Wochentag variieren. Deshalb schaut das Blatt 'Arbeitszeiten' ja auch in etwa so aus: A | B | C | D | E | F | G | H | I | Tarif Mo Di Mi Do Fr Sa So Feiertag?? H1 6:00 6:00 6:00 6:00 6:00 0:00 0:00 H2 8:30 8:30 8:30 8:30 8:30 0:00 0:00 Die Spalte I (Feiertag) war nur eine Idee von mir. Sie wird aber das Problem nicht erschlagen, da ja die Sollarbeitszeit an einem Feiertag eben auch vom Wochentag abhängig ist. Wer kann mir also einen anderen Lösungsansatz bieten? Ach ja... die Feiertage sind natürlich auch in einem Tabellenblatt aufgelistet. Nehmen wir mal an, es heißt: Feiertage!D3:D14 Danke im voraus! Gruß, torsten

Antwort 1 von rainberg

Hallo Torsten,

Du hast zwar alles gut beschrieben aber leider fehlt mir die Zeit zum Nachbau.
Ohne Testobjekt ist es wiederum schwer Formeln zu entwickeln.

Wie wär's also mit einer Beispielmappe, die Namen kannst Du ja verfälschen.

Gruß
Rainer

Antwort 2 von fantalight

Hallo Rainer,

hoffe das hilft Dir oder besser gesagt MIR weiter ;-)
http://www.netupload.de/detail.php?img=c14383d6a8ef5d1d067b1f4ca252...

Danke schon jetzt für's Drüberschauen!!!
Gruß
Torsten

Antwort 3 von Aliba

Hi torsten,

deine Feiertage hast Du ja bereits im Tabellenblatt "Feiertage".

Die Arbeitszeiten holst Du Dir ja , wenn nicht anders eingetragen aus den Wochentagesschichten.

Eine Abfrage des Datums auf die Feiertagsliste ist auch kein Problem.
Nun ist aber die Frage: Was soll denn geschehen, wenn dieser Tag ein Feiertag ist. Gibts dann Feiertagszuschläge, die auf die Stunden aufgeschlagen werden? Wenn ja, wie hoch ist der Aufschlag, welche Kürzel werden berücksichtigt K und U ja wohl sicherlich nicht. usw.....

Bitte die Fragen noch klären und noch mal posten. Der Rest sollte dann kein Problem sein.

CU Aliba

Antwort 4 von rainberg

Hallo Torsten,

so weit wie Aliba hatte ich jetzt gar nicht gedacht und versucht die Formel nur so anzupassen, dass nur die dem Wochentag entsprechende Zeit eingetragen wird.
Schreibe dazu in die Hilfszelle C2 folgende Formel:

=WENN(ISTNV(SVERWEIS(A2;Feiertage!D:D;1;0));"";"F")

Die für U5 angepsste Formel lautet dann:

=WENN(ODER(T5<>"";UND(SUMME($B5:$S5)=0;$C$2="F"));INDEX(Arbeitzeiten!$A$2:$H$8;VERGLEICH(SVERWEIS(A5;Mitarbeiter!$C$3:$F$21;3;FALSCH);Arbeitzeiten!$A:$A;0)-1;VERGLEICH(WOCHENTAG($A$2);Arbeitzeiten!$2:$2;0));REST(D5-B5;1)+REST(G5-E5;1)+REST(J5-H5;1)+REST(M5-K5;1)+REST(P5-N5;1))

Gruß
Rainer

Antwort 5 von fantalight

@Aliba: Danke für Deinen Beitrag. So weit mußtest Du allerdings nicht denken. Das Tool berechnet keine Arbeitsgelder; demnach brauchts keine Berücksichtigung von Feiertagszuschlägen. Mein Tool soll helfen, einen Dienstplan zu erstellen, einen Kurzauszug daraus generieren (Namen und Zeiten), die Tagesstunden ausrechnen und diese dann vielleicht auch noch auf eine erforderliche Statistik aufteilen. Bisher alles einzelne 'Anwendungen' verteilt auf WORD und EXEL.
:-(

@Rainer: Danke auch an Dich für Deine Lösung. Klappt natürlich.. wie immer!
;-)

Ich möchte nicht nur Lösungen übernehmen/kopieren, sondern befasse mich auch intensiv damit und gehe jede Formel meist unter Zuhilfenahme der Onlinehilfe durch. Deshalb versuche ich auch mal hier mein Verständnis der Formel auszudrücken:

Hilfszelle C2: ISTNV liefert Wahr oder FALSCH-Werte.. die Wenn-Funktion dann bei Feiertag entsprechend das 'F' in C2

Jetzt versuche ich mal noch die Formel von U5 zu zerpflücken:
WENN-Funktion..
WENN-Bedingung.. jetzt kommt ODER-Zweig (sprich 1 Zweig muß zutreffen, damit Dann_Wert der Funktion eintritt)...
also entweder ein Standardeintrag (U,K) ist gewählt bedeutet ODER Zweig=Wahr und die Sollarbeitszeit für den Mitarbeiter wird aus 'Arbeitszeiten' geholt.
Was aber wenn kein Eintag in T5 erfolgte (Sonst_Wert der WENN-Funktion).. der 2. ODER-Zweig nur wahr, wenn Feiertag und keine Einträge in den Von/Bis-Spalten (UND-Funktion).
Sehe ich das so richtig?
Wie leite ich mir so etwas selbst her?

Noch eine Frage am Schluss: Wie stelle ich Feldabhängigkeiten her? Zum Beispiel soll bei Eintrag in T5 alle Einträge von B5:S5 gelöscht werden und umgekehrt natürlich. Geht das nur über Makro oder gibts auch andere Möglichkeiten?

Danke und Gruß
Torsten

Antwort 6 von rainberg

Hallo Torsten,

vielleicht noch ein Hinweis zu meiner angepassten Formel.

Ich bin davon ausgegangen, dass, wenn ein Feiertag oder in Spalte T ein Eintrag ist, dann sind die von-bis Spalten sowieso leer.

Zumindest würde ich das für logisch halten und nur unter diesen Bedingungen funktioniert die Formel richtig (wenn ich nichts übersehen habe).

Deine letzte Frage hast Du Dir schon selbst beantwortet - dies ist nur mit Makro möglich.
Wenn Du sowas vor hast, dann würde ich Dir aber empfehlen, auch die Berechnungen gleich per Makro auszuführen.

Vorbeugend möchte ich aber gleich sagen, dass ich mir die Erstellung des Makros zwar zutraue aber aufgrund keiner profihaften Kenntnisse auf diesem Gebiet zu lange daran sitzen würde.

Die Zeit habe ich im Moment nicht, aber hier gibt es genügend Experten die das können.

Gruß
Rainer