Office: Farbige, bedingte Formatierungsfelder zählen

Hallo, hab ein Problem mit meiner Urlaubsplanungsdatei:
Soweit alles gut, nur möchte ich für jeden Tag unten ein Feld mit der Summe über die Mitarbeiter welche für den jeweiligen Tag Urlaub eingetragen haben. Da aber kein Text, sondern nur eine Farbe in dem Feld steht, kann ich keine Summe ermitteln. Wie mache ich dies denn?
MbD
Hab die Datei mal eingetragen

 

Moin!
Farbauswertungen sind in Excel ungünstig.
Man kann etwas basteln, ändert aber nichts an der Tatsache, dass ein manueller Farbwechsel keine Neuberechnung auslöst.
Ergo:
Mache die ostfriesische Nationalflagge (weißer Adler auf weißem Grund).
Bedeutet: Lege für den Urlaub eine bedingte Formatierung an:
=B5="U"
wird angewendet auf $B$5:$NC$23
Format: grüne Schrift auf grünem Hintergrund
Jetzt kannst Du die Spalten mit =ANZAHL2(B5:B23)
oder mit =ZÄHLENWENN(B5:B23;"U") auswerten.

Gruß Ralf

 

Hallo, danke für deine schnelle Antwort.
Leider klappt es bei mir absolut nicht. Soll ich eine komplett neue bedingte Formatierung erstellen oder die bestehenden bearbeiten?
Komme leider noch nicht weiter

 

Ersteres!
Du musst dann natürlich auch jeweils ein U für die Urlaubstage eintragen.
Bin jetzt aber unterwegs und erst heute Abend wieder online.

 

Hallo,

da du offenbar Excel2021 oder Excel365 besitzt, habe ich in der beiliegenden Datei einige Formeln geändert bzw. eingefügt:
In Zelle B4: =LET(nj;DATUM(A2;1;1); njn;DATUM(A2+1;1;1); SEQUENZ(;njn-nj;nj))
In Zelle B3: =WENN(WOCHENTAG(B4#)=2;ISOKALENDERWOCHE(B4#);"")
Durch die Formel in B4 weiß Excel, ob es sich um ein Schaltjahr oder Gemeinjahr handelt.

Da du anschließend an die 366 Kalendertage 12 Doppelspalten zum Eintragen von Von-Bis-Urlaubszeiten hast (Bereich ND5:OA58) , auf die die bedingte Formatierung (grün) zugreift,
habe ich dir in Zeile 60 folgende Formel zum Zählen der Urlaube pro Tag eingefügt:
Ab Zelle B60 bis NC60: =LET(Dt; B$4; Url; ($ND$5:$ND$58 <= Dt) * (Dt <= $NE$5:$NE$58) + ($NF$5:$NF$58 <= Dt) * (Dt <= $NG$5:$NG$58) + ($NH$5:$NH$58 <= Dt) * (Dt <= $NI$5:$NI$58) + ($NJ$5:$NJ$58 <= Dt) * (Dt <= $NK$5:$NK$58) + ($NL$5:$NL$58 <= Dt) * (Dt <= $NM$5:$NM$58) + ($NN$5:$NN$58 <= Dt) * (Dt <= $NO$5:$NO$58) + ($NP$5:$NP$58 <= Dt) * (Dt <= $NQ$5:$NQ$58) + ($NR$5:$NR$58 <= Dt) * (Dt <= $NS$5:$NS$58) + ($NT$5:$NT$58 <= Dt) * (Dt <= $NU$5:$NU$58) + ($NV$5:$NV$58 <= Dt) * (Dt <= $NW$5:$NW$58) + ($NX$5:$NX$58 <= Dt) * (Dt <= $NY$5:$NY$58) + ($NZ$5:$NZ$58 <= Dt) * (Dt <= $OA$5:$OA$58); SUMME(Url))

In strukturierter Form und deshalb etwas übersichtlicher sieht diese wie folgt aus:

Modifizierte Datei liegt bei.

 

Hallo,

ich habe die Datei nochmals überarbeitet, indem ich eine Feiertagstabelle in OD5:OF23 hinzugefügt habe (für 100%- und 50%-Feiertage), ferner die bedingte Formatierung dieser Feiertage.
Entsprechend habe ich die Formel für die Urlaubstage-Summierung in Zeile 60 angepasst:
Formel in B60 (bis NC60): =LET(Dt; B$4; WENN(WOCHENTAG(Dt; 2) < 6; LET(Feiertage; $OE$5:$OF$23; FProz; WENNFEHLER(SVERWEIS(Dt; Feiertage; 2; 0); 0); Url; ($ND$5:$ND$58 <= Dt) * (Dt <= $NE$5:$NE$58) + ($NF$5:$NF$58 <= Dt) * (Dt <= $NG$5:$NG$58) + ($NH$5:$NH$58 <= Dt) * (Dt <= $NI$5:$NI$58) + ($NJ$5:$NJ$58 <= Dt) * (Dt <= $NK$5:$NK$58) + ($NL$5:$NL$58 <= Dt) * (Dt <= $NM$5:$NM$58) + ($NN$5:$NN$58 <= Dt) * (Dt <= $NO$5:$NO$58) + ($NP$5:$NP$58 <= Dt) * (Dt <= $NQ$5:$NQ$58) + ($NR$5:$NR$58 <= Dt) * (Dt <= $NS$5:$NS$58) + ($NT$5:$NT$58 <= Dt) * (Dt <= $NU$5:$NU$58) + ($NV$5:$NV$58 <= Dt) * (Dt <= $NW$5:$NW$58) + ($NX$5:$NX$58 <= Dt) * (Dt <= $NY$5:$NY$58) + ($NZ$5:$NZ$58 <= Dt) * (Dt <= $OA$5:$OA$58); SUMME(Url) * (1 - FProz)); 0))
bzw. in strukturierter Form:

Datei liegt bei.

 

Mein Gott, ich ziehe den Hut vor solch ein Wissen.
Danke vielmals für die Hilfen.
Bisher klappt alles.
Toll!!!

 

Hallo,

falls du Excel365 hast, kannst du statt der langen obigen Funktion auch folgende Formel

im Namensmanager unter "Bezieht sich auf:" abspeichern:
=LAMBDA(Dt; WENN(WOCHENTAG(Dt; 2) < 6; LET(Feiertage; Urlaubsplan!$OE$5:$OF$23; Urlaube; Urlaubsplan!$ND$5:$OA$58; FtgProz; WENNFEHLER(SVERWEIS(Dt; Feiertage; 2; 0); 0); UrlAnzahl; REDUCE(0; SEQUENZ(1; 12; 1; 2); LAMBDA(Akk;Idx; Akk + (INDEX(Urlaube; 0; Idx) <= Dt) * (Dt <= INDEX(Urlaube; 0; Idx + 1)))); SUMME(UrlAnzahl) * (1 - FtgProz)); 0))
mit "Namen:" Urlaube_Tagessumme

Dann kannst du sie wie eine eingebaute Funktion verwenden, zum Beispiel um die Urlaubsanzahl vom 1.1. zu ermitteln:
=Urlaube_Tagessumme(B$4)

In strukturierter Form sieht die Funktion so aus:

 

Hallo lieber Exl121150
Ich trau mich kaum noch einen Wunsch zu äußern.
Sie hatten sich so doll Mühe gemacht, nur die Wochentage als Urlaubstage zu errechnen. Wir arbeiten aber in der Pflege und brauchen die Schichtstärke an allen Tagen des Jahres. Mit "Lit" in excel hab ich mich noch nicht beschäftigt. Wäre es Ihnen möglich, die Datei nochmals zu verändern?
Darüber würde ich mich sehr freuen!!!!!
Gruß
Gelegenheitsexcler

 

Hallo,

nachdem ich nicht weiß, was "Lit" ist, habe ich mich offenbar auch noch nicht damit beschäftigt.

Ich habe mehrmals angedeutet, dass für mich wichtig wäre, zu wissen, welche Excel-Version du hast: Excel2021 oder Excel365 ?

Die Abfrage, welcher Wochentag/Arbeitstag vorliegt oder ob es ein Wochenende ist, das ja doch durch eine simple WENN(WOCHENTAG(Dt;2)<6;RestlicheFormel; 0) Formel enthalten, die dann durch RestlicheFormel zu ersetzen ist.
Ich habe daher in Zeile 60 (B60:NC60) die enthaltenen LET-Formeln umgestellt, indem ich den hellblauen Formelteil entfernt habe.

Falls du Excel365 hast, habe ich in Zeile 61 (B61:NC61) die benutzerdefinierten Funktionen =Urlaube_Tagessumme(B$4) eingefügt, wobei ich im Namensmanager die unter dem Namen "Urlaube_Tagessumme" enthaltene LAMBDA-Funktion auch entsprechend umgestellt habe.

Da du nichts von den Feiertagen erwähnt hast, habe ich die Feiertage in den Formeln belassen. Falls du auch die Feiertage in der Formel nicht berücksichtigt haben willst, müsstest du einfach die Datei aus meinem Posting #5 (gestern 16:28 Uhr) nochmals herunterladen. Dort sind einfach nur die Urlaubseingaben im Bereich ND5:OA58 berücksichtigt.

Weiters habe ich die Darstellung der bedingten Formatierung für die einzelnen Urlaubstage umgestellt, sodass die grün gefärbten Urlaubstage nicht mehr durch die Graufärbung der Wochenenden und auch nicht durch die Orangefärbung der Feiertage unterbrochen werden.

Datei liegt bei.

 

Oh Mann, bei mir liegt echt im Argen. Meinte natürlich die "LET" Funktion.
Aber Spaß beiseite.
Nochmals herzlichsten Dank!
Nutze auf dem Tablet Microsoft 365, hab aber am computer noch ein altes Excel drauf. Nochmals vielen Dank
Gelegenheitsexcler