Stundenzettel Feiertage und Urlaub erkennen excel
Moin moin, ich möchte bei meinen Gesamtstunden gerne erkennen lassen wenn ich Urlaub habe bzw. ein Feiertag ist der an einem Tag von Montag bis Freitag liegt. Meine jetzige Formel ist: =WENN(UND(C11<>"";D11<>"");MAX(8/24;REST(D11-C11;1)-(B11="Az")*0,5/24);"") Er soll jetzt noch zusätzlich überprüfen ob in I11 das Wort Urlaub steht und das Datum in Kalender!J36:Kalender!J46 steht und dieses Datum Ein Arbeitstag ist sprich Montag bis Freitag ist dann soll er 8h eintragen. Ich hoffe mein Anliegen ist verständlich. Schon mal besten dank!
2 Antworten
Ich würde mit einem SVERWEIS und ergänzend dann die WENN(ODER einbauen. Für den SVERWEIS wird eine extra Tabelle angelegt. Wo man dann die Wochentage in eine Spalte und die entsprechenden Stunden in die zweite Spalte einträgt. Funktion durchsucht die Suchspalte senkrecht nach dem Suchbegriff. Gesucht wird immer in der ersten Spalte der Matrix. Sprich Schreibst du in deinem Stundenzettel das Wort Montag durchsucht die Funktion die erste Spalte der Extratabelle nach Montag und gibt den geünschten Wert aus der zweiten spalte wieder.
montag 8,5 Dienstag 8,5 ..... Sonntag 0
Ergänzend mit dem WENN(ODER wäre das denn so, dass wenn dort urlaub oder krank steht, dass dann nur 8 stunden und ansonsten der sverweis greift.. ist für mich die einfachste lösung damit dein stundenzettel sowohl zwischen werktag und wochenende unterscheidet als auch ergänzend urlaub und krank
reines UND(...; ...; ...; ...) ist halbwegs nachvollziehbar, ebenso
reines ODER(...; ...; ...; ...)
Sowie man das aber mischt, gibts bei Ungeübten nur noch Knoten im Hirn!
"zusätzlich überprüfen ob in I11 das Wort Urlaub steht": und was, wenns nicht drinsteht? Das Ergebnis =0 setzen? (in meiner Formel reicht übrigens stattdessen der Eintrag "U".
Dann schreib vor Deine Formel =WENN(LINKS(I11;1)="U";0;Formel)
Beim Wochentag bevorzuge ich wegen des Unterschieds am <=> europ die eindeutige Formulierung
=wenn(Rest(Kalender!J36;7)<2;0;vorige Formel)
(Sa ist 0, So 1, Mo 2 ...Fr 6)
zusammengefasst in Deiner Formel:
=WENN(UND(C11<>"";D11<>"";LINKS(I11;1)<>"U";Rest(Kalender!J36;7)>=2);MAX(. . . .
ich nehme dabei an, dass das Datum in J36... eindeutig zuzuordnen ist, wobei ich nicht verstehje, warum Du 11 Zellen dafür brauchst.
Du hast leider nicht ausgeführt, wie man das nach Deinem Vorschlag genau und kurz macht, sonst hätte ich mir das sparen können.
Die Quintessenz hab ich fett markiert,
alles vorherige ist fürs Verständnis,
ich versuche, die Entwicklung der Formeln nachvollziehbar zu machen
Liegt an Jedem selber, ob er das nutzen will oder gar nicht braucht!
vllt habe ich Dich missverstanden. aber vllt hillt Dir folgende -viel einfachere- Formel weiter:
in i11 kann "So"."Mo"."Di"."Mi"."Do"."Fr"."Sa"."U"."Fe" stehen und Du kannst in der Liste jeden Tag einzeln mit Vorgabestunden bestücken:
=INDEX({0;8;8;5;8;4;0;0;0};VERGLEICH(I11;{"So"."Mo"."Di"."Mi"."Do"."Fr"."Sa"."U"."Fe"};0))
(U=Urlaub, Fe=Feiertag, kannst auch andere Kürzel verwenden.) der Reihenfolge nach stehen im ersten indexvektor die dem jeweiligen Eintrag entsprechenden Stunden (hab hier für Mi u. Fr abweichende Stunden gesetzt).
Vergleich gibt die Position des Eintrags im Vektor wieder (1..9). Diese soundsovielte Position wird aus dem index-Vektor ausgelesen.
Du bekommst also in einer wenig komplizierten Formel sofort die Stundenzahl angezeigt.
Da Du die Zellen nach unten kopieren kannst, kannst Du lange Tageslisten erstellen.
Du musst allerdings beachten, dass 1h = 1/24 Tag ist, dass Du also ggf bei der Berechnung den Faktor 24 einführen musst, wenn du die Darstellungsform Tageszahl im DATUMSFORMAT brauchst!
(bei nur 9 Tagestypen ist das evtl einfacher als ein SVerweis)
in i11 steht das natürlich ohne Gänsefüßchen und Punkt!
und wieder mal keinerlei Rückmeldung!
Auch hier.. viel zu kompliziert. Warum kompliziert wenn es auch einfach geht!