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
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
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
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
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
:-(
@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
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