Automatisches erkennen tägliche Sollarbeitszeit

[Andi Schulze]

Der letzte Schritt zum idealen Arbeitszeitkonto für unsere Firma fehlt noch. Hier kurz die Beschreibung des wohl letzten und (hoffentlich) lösbaren Hakens :

Zellen A3 bis A33 = Tagesdatum
Zellen B3 bis B33 = Wochentag (Mo-So)
Zellen C3 bis C33 = Hinweis / Besonderheit; betrifft z.B. Urlaub, Krank, Feiertag...

Zellen D3 bis D33 = Hilfsspalte für die Stundenzahl bei Urlaub, Krank, Feiertag... Das heißt das wenn z.B. jemand Urlaub hat, ihm im Arbeitszeitkonto die vertraglich vereinbarte tägliche Arbeitszeit angerechnet wird.
In den Zellen N1 bis T1 sind die Tagesbezeichnungen entsprechend Spalte B eingetragen
In den Zellen N2 bis T2 sind die täglichen Arbeitszeiten an den einzelnen Arbeitstagen eingetragen.
In den Zellen darunter sind die automatisch ermittelten durchschnittlichen täglichen Arbeitszeiten erfasst. Hierunter ist unter anderem in Spalte L die Bezeichnung "Feiertag" eingetragen, anhand der bei Eintrag in den Zellen C3 bis C33 die Stunden erfasst werden.



Jetzt aber zum Problem. Natürlich gibt es unter der Woche Feiertage an dem mancher wegen einer z.B. 4-Tage-Woche eben nicht arbeitet. Im Bild oben ist es der Donnerstag. Wenn nun an einem Donnerstag ein Feiertag ist, werden ihm aber trotzdem die Stunden angerechnet obwohl dies falsch ist. Wie kann ich dies umgehen, in dem erkannt wird, dass der Donnerstag in diesem Fall kein Arbeitstag ist und trotz "Feiertag" keine Stunden berechnet werden.

Bis jetzt ist in den Zellen D3 bis D33 folgende Formel eingetragen:

=WENN(ODER(C3="Urlaub";C3="Krank";C3="Feiertag";C3="Sonderurlaub";C3="Berufsschule";C3="Prüfung";C3="BG-Unfall");M3;0)

Ich weiß, alles hoch kompliziert und zum nachvollziehen nicht ganz einfach. Ich hoffe ihr versteht es dennoch und könnt mir in irgendeiner Weise helfen. Natürlich gibt es die Möglichkeit einfach die Bezeichnung "Feiertag" bei den betreffenden Mitarbeitern zu löschen aber was wenn es versehentlich doch einmal vergessen wird und schon ist die Berechnung falsch. Durch die Automatik würde diese Fehlerquelle ausgeschlossen. lade einfachheitshalber die Liste hoch, dürfte wohl eine deutliche Erleichterung zum Verständnis sein.

Hat jemand eine Idee ???

[steve1da]

Hola,

Der letzte Schritt zum idealen Arbeitszeitkonto

davon bist du noch einiges entfernt. Du berücksichtigst keine Schaltjahre automatisch, du musst deine Feiertage händisch eintragen und nicht automatisch durch Excel.
Das mit dem Feiertag kannst du so umgehen:

Code:

=WENNFEHLER(SVERWEIS(C3;$L$1:$T$15;VERGLEICH(B3;$L$1:$T$1;0);0);"")

Die Sollzeit in Spalte I kann man so ermitteln:

Code:

=WVERWEIS(B3;$N$1:$T$3;3;0)

Gruß,
steve1da

[Andi Schulze]

Es funktioniert und die Formel für die Ermittlung der Sollstunden ist deutlich entschlackt. 
 
Das mit den Schaltjahren und der manuellen Eingabe der Feiertage ist richtig und wird sich wohl nicht automatisieren lassen. Das einzige was mir dazu einfällt ist, dass ich mir eine eigene Vorlage für Schaltjahre anlege.

Dir steve1da einmal mehr vielen Dank.

[steve1da]

Die Feiertage eines Jahres kann man sich bequem von Excel anzeigen lassen. Die festen Feiertage sind ja immer gleich (bis auf das Jahr). Die beweglichen Feiertage ermittelt man dann so:
https://www.herber.de/excelformeln/src/call.pl?idx=3
Für die Schaltjahre, im Februar in A31:
=WENN(MONAT(A30+1)>MONAT(A30);"";A30+1)

[Andi Schulze]

Zu früh gefreut  
Zwei Problemchen habe ich jetzt noch.

1. Wenn in den Zellen D3 bis D33 ein Zahl ermittelt wird, muss ich diese ja in der Spalte "Arbeitszeit" unterbringen. Da bei Urlaub, Krankheit etc. die durchschnittlichen Stunden auch als Arbeitszeit angerechnet und bezahlt werden.

Im Moment stehr bei mir in den Zellen H3 bis H33 die Formel :
=WENN(ODER(C3="Unbezahlt";C3="KG-Bezug");0;(D3+(F3-E3-G3)*24))

Das hatte bisher geklappt aber dies geht wohl aktuell deswegen nicht mehr, da in der Hilfsspalte D nur dann ein Wert ausgegeben wird, wenn bei den Hinweisen / Besonderheiten eben eine Bezeichnung wie Urlaub, Krank ausgewiesen wird.


2. Das Problem ist der Bezug von Krankengeld (kurz KG-Bezug). Denn hier muss bei den Sollstunden ein "0:00" stehen. Ich habe zur Berechnung der Sollstunden deinen Vorschlag
=WVERWEIS(B3;$N$1:$T$3;3;0)
verwendet aber dabei werden wir ja nun auch den KG-Bezug berücksichtigen müssen. Kriegst Du das hin ?
Warum einfach wenn es auch kompliziert geht. 

Hänge einfach meine Excelliste bei, damit Du auch alle Formeln sehen kannst, falls das etwas hilft.

PS : Das mit dem automatischen festsetzen der Feiertage probier ich später aus. Wäre MEGA

[redeagle56]

Hallo,
ich habe mal versucht, dich etwas voranzubringen.
Wochenenden und Feiertage werden automatisch festgelegt.
Die Formatierung musst du nur auf die anderen Monate anwenden.
Die Zellen mit #WERT! habe ich bearbeitet.

MfG Günter

  Stundenliste VORLAGE für 2022_2.xlsm (Größe: 386,49 KB / Downloads: 3)

[Andi Schulze]

Passt
 

Dachte zuerst nur, warum das bei allen Bezeichnungen funktioniert, nur nicht bei "Krank".

Lösung : Der war bei den täglichen Arbeitsstunden mit "0,00" eingetragen und somit auch kein Arbeitstag.
Peinlich

Was wäre ich nur ohne Euch. Man lernt täglich dazu und Excel macht noch mehr Spaß als ohnehin schon.
 


PS: Als erledigt kennzeichnen ? Aber Logo.