Karenztage bei Krankheitsfällen automatisch berechnen

[Mamamia]

Guten Morgen Liebes Forum,

Ich sitze nun schon lange an diesem Thema aber komme einfach nicht mehr weiter. Seit Januar dieses Jahres hat sich alles geändert und jede Mitarbeiterfunktion hat andere Bedingungen im Krankheitsfall.
In der Zeiterfassung habe jeweils die Daten mit Krankheit / Unfall etc. ausgelesen da ich dies danach für die Lohnabrechnung brauche.

Und bitte keine Kommentare ob dies so zulässig ist oder was wir hier machen. Ich führe nur aus ( versuche es zumindest) ich bin nicht der Chef..)

Nun ist es so dass während der Probezeit alle 3 Tage Abzug bei Krankheitsfall haben ( Unfall ist ausgeschlossen)
Nach der Probezeit haben Praktikanten 1 Tag Abzug / Fall           Lernende haben nach der Probezeit 2 Tage Abzug / Fall ( sogenannte Karenztage)

ab der Spalte AV sind meine Daten und den Berechnungen welche ich bräuchte. ( Spalte BD bis BN kann ignoriert werden da ich dies selber schaffe)
Ich habe im Beispiel alles reingeschrieben was relevant ist und wie die Berechnung sein soll.

Ich habe nun schon ettliche Male die Fomeln abgeändert aber irgendwie klappt es entweder beim Lernenden nicht oder wenn ich etwas ändere dann wieder beim Praktikanten nicht oder für die Probezeit nicht.
Kann mir jemand einen Tip geben wie ich dies am einfachsten lösen kann oder wo ich zumindest den Denkfehler begehe? 

Hatte auch versucht zuhinterst noch eine Spalte einzufügen welche mir die Karenztage zusammenzählt bis zum nächsten Ereignis welches ich in der Spalte BO sehe, aber auch das will nicht klappen ( deshalb wieder gelöscht)

Wer weiss mir einen Rat?

Ich danke Euch jetzt schon für Eure Tips und wünsche Euch einen guten Start in den Tag

Liebe Grüsse Mammamia

[schauan]

Hallöchen,

mal die ersten 3 Dinge, die mir aufgefallen sind.

1) in Spalte BB prüfst Du öfter AV30=AW29+1 - ich denke, damit ergibt sich, dass es sich um ein zusammenhängendes Ereignis handelt? In BC hast Du sowas anscheinend nicht
2) in BC beziehst Du dich öfter auf BD18 - da steht nix. Wird da manuell was eingetragen?
3) es gibt einige #BEZUG. OK, diese Spalten sollen wir nicht beachten ...

[slowboarder]

Hi
ist jetzt nicht so ganz einfach, sich da rein zu arbeiten (zumindest nicht in der Zeit, die du mir vergütest)
ich würde hier vielleicht erstmal so vor gehen und die Monatsübersicht in A-R noch erweitern:

1. Zähle in einer Zusatzspalte die die Anzahl der aufeinander folgenden Krankheitstage, dazu eine Formel nach dem Schema in Spalte N (oder wo auch immer)
=Wenn(L12<>"Krank";"";Wenn(L11<>"krank";1;N11+1))
dh immer wenn in der Absenzenspalte ein "krank" auftaucht, beginnst du mit 1 und zählst bei jedem weiteren Tag +1 hoch

2. in einer weitern Spalte berechnest du dann die Karenztage: 
=Wenn(L12<>"krank";0;Wenn(N12<=3;1;0))
dh ein karenzzag ist, wenn eine Krankmeldung vorliegt und der Zähler für die aufeinander folgenden Krankheitstage noch kleiner oder gleich 3 ist
diese Spalte kannst du dann du dann summieren um die Karenztage zu zählen. Wahrscheinlich musst du hier noch prüfen, ob es ein Feiertag oder Wochendende ist, aber es geht ja erstmal ums prinzip.
dein Zahlenwert in der Formel beim <=3 machst du natürlich als Bezug auf eine Zelle, in welche du dann flexiblel eintragen kannst, wieviele Karenztage für die betroffene Person vorgesehen sind.

Gruß Daniel

[Andreas Killer]

So wirst Du nie auf einen grünen Zweig kommen, das ist alles ein großes Durcheinander und ich räum einfach mal auf um eine klare Struktur in das Ding zu bringen:

  Karenztage automatisch berechnen.xlsx (Größe: 33,57 KB / Downloads: 13)

Ob ein Fall wechselt mache ich davon abhängig ob sich in Spalte L von einem zum nächsten Tag der Eintrag ändert... das kann man auch anders / von Spalte U abhängig machen, die Wochenenden berücksichtigen usw.. Wie auch immer man das genau macht, hier ist ein bisschen Phantasie, und Detailwissen wie es gerechnet werden soll, gefragt.

Diese Spalte ist der Schlüssel zu Deinem Glück, in Spalte S steht die Fallnummer.

Basierend auf dieser wird in Spalte T der Tag pro Fall hochgezählt, wie slowboarder das schon angedeutet hat.

Damit wir nun auch durch die ganzen verschiedenen Krank's und Unfall's durchsteigen und man sich nicht mit den Formeln die Finger bricht erstellt man sich eine Setup Tabelle in der der Typ und die jeweilige Wertzuweisung spezifiziert ist. Beide Werte holen wir uns per SVERWEIS.

Mit dem Gerüst werden die Auswertungsformeln ab Q45 sehr einfach.

Andreas.

[Mamamia]

Ihr Lieben,

Vielen Dank für Eure Inputs, ich weiss dass es ein riesen durcheinander ist jedoch hat es mal funktioniert als alle die gleichen Abzüge an Karenztagen hatten..)
@schauan, in BD 18 steht bei mir das Datum vom letzten Mal krank im Vormonat ( dient mir um zu wissen ob der Mitarbeiter am letzten Tag des vormonats auch krank / unfall etc. war /hatte um die Karenztage korrekt zu berechnen)

@slowboarder bei deinem Input bin ich noch nicht ganz gestiegen, probiere aber gleich noch aus.

@Andreas Killer, Deine Version ist auch nicht korrekt. Der 05.-12. ist zbsp.derselbe Krankheitsfall
Ausserdem brauche ich das so wie in der Vorgabe von mir, damit ich da auch die Lohnfortzahlung von 80% gleich korrekt berechnen, respektiv ausweisen kann. Dies wird dann monatlich in eine Tabelle in einem anderen Sheet aufgelistet und wird so automatisch in die Lohnabrechnung übertragen und berechnet anhand dieser Absenzen und Karenztage etc.

Ich weiss dass die Formeln mega komliziert sind bleibe aber dran....

Nun versuche ich noch das von @slowboarder und melde mich wieder. Ich danke Euch trotzdem vorab schon einmal...

Liebe Grüsse an EUch Mammamia

[Andreas Killer]

@Andreas Killer, Deine Version ist auch nicht korrekt. Der 05.-12. ist zbsp.derselbe Krankheitsfall
Ausserdem brauche ich das so wie in der Vorgabe von mir, damit ich da auch die Lohnfortzahlung von 80% gleich korrekt berechnen, respektiv ausweisen kann. Dies wird dann monatlich in eine Tabelle in einem anderen Sheet aufgelistet und wird so automatisch in die Lohnabrechnung übertragen und berechnet anhand dieser Absenzen und Karenztage etc.

Das was ich da gemacht habe ist das gleiche in grün was Daniel vorgeschlagen hat... ob Du nun seine Formeln nimmst oder meine ist wurscht, Qualitativ gibt es da IMHO keinen Unterschied. 
Ich bin da einfach einen Schritt weiter gegangen und hab die Datei gemacht damit Du sehen kannst wie das alles zusammen funktioniert.

Wenn verschiedene Krank's untereinander der gleiche Fall sind dann
S12:  =S11+(U11<>U12)
runterziehen.

Nochmal: Wie und wann der Fall wechselt ist der Schlüssel, so wie Du das in Deiner Datei gemacht hast ist das nur Zufall das es funktioniert, das habe ich mir auch angesehen. Man kann die Formel in Spalte S auch durchaus kreativer gestalten um mehrere verschiedene Bedingungen einfließen zu lassen... wenn Du dabei Hilfe brauchst musst Du exemplarische Beispiele vorgeben und uns was dazu erzählen.

Die Prozentwerte stehen in Spalte V, was welchen Prozentsatz hat kannst Du im Blatt Setup anpassen. Die Werte habe ich frei nach meiner Nase da reingeschrieben, daher sieht das anders aus.

Aus meiner Sicht sind das alles Details die Du selber nun anpassen musst, ich kann Dir das alles auch machen, aber dann als Auftragsarbeit gegen entsprechende Bezahlung.

Andreas.