Feiertage berücksichtigen

[Thomas78]

Hallo,

ich habe folgende Formel um die Anzahl der Freitage zu berechnen (zwecks Spesen):

=SUMMENPRODUKT((Urlaubsplan!D7:NJ7="Fr")*(Urlaubsplan!D8:NJ8>=DATWERT("01.01.2017"))*(Urlaubsplan!D8:NJ8<=DATWERT("31.01.2017"))*(Urlaubsplan!D9:NJ9=""))

Ich würde jetzt noch gerne die Feiertage die ich im Bereich "Feiertage" hinterlegt habe ausschliessen. Kann mir jemand sagen wie ich die Formel hierfür abändern muss?

Außerdem würde ich noch gerne eine Formel für Mo-Do erstellen, d.h. alle Tage außer Freitag, Samstag, Sonntag, Feiertag im Januar 2017 sollen gezählt werden. Auch hier meine Frage wie ich die Formel dafür abändern müsste.

Vielen Dank für eure Hilfe!

Grüße Thomas

[BoskoBiati]

Hallo,

es wäre u.U. geschickt, wenn in den Zellen Datumswerte stehen würde, dann könnte man das leichter abfragen. Ein Muster der Tabelle wäre für uns hilfreich.

Code:

=SUMMENPRODUKT((rest(Urlaubsplan!D8:NJ8;7)=Zeile(a2))*(monat(Urlaubsplan!D8:NJ8)=Zeile(a1))*(Urlaubsplan!D9:NJ9="")*(Urlaubsplan!D8:NJ8<>Mtrans(Feiertage))

ungetestet! Matrixformel! Mit STRG+SHIFT+ENTER abschließen!

Gilt für Montag. Für alle anderen Tage nach unten ziehen!

[Thomas78]

Hallo BoskoBiati,

danke für die schnelle Antwort. Die Zellen sind Datumswerte nur als Mo,Di usw formatiert.

Die von dir angegebene Formel funktioniert bei mir leider nicht.

Bezüglich der anderen Tage ist es so, dass ich Mo-Do in einer Formel brauche. Wir bekommen von Mo-Do einen Spesensatz und am Freitag einen anderen. Das heisst ich kann die Formel nicht nach unten ziehen sondern brauche eine die die Tage Mo-Do zählt (ohne Feiertage).

Eine Datei habe ich in einem anderen Thema "Drehfeld Programmierung bzw. Datenübertrag" bereits eingefügt. 

Achja und gibt es evtl. eine Verbesserung, dass er nur den Monat Januar zählt, ohne dass ich in der Formel einen Datumsbereich 01.01. bis 31.01. vorgeben muss?

Grüße Thomas

[lupo1]

Schade, so große Dateien öffne ich nicht.

Zu Deiner Frage bei Excel-Datümern: ...(TEXT(Urlaubsplan!D8:NJ;"JMM")=TEXT(--"01.01.2017";"JMM"))... statt

...(Urlaubsplan!D8:NJ8>=DATWERT("01.01.2017"))*(Urlaubsplan!D8:NJ8<=DATWERT("31.01.2017"))...

[atilla]

Hallo Edgar,

zwei Dinge sind mir Aufgefallen, wobei ich mir nicht sicher bin, ob Du es nicht anders meinst.

1.

es wäre u.U. geschickt, wenn in den Zellen Datumswerte stehen würde

könnt es sein, dass Du das so schreiben wolltest:

es wäre u.U. geschickt, wenn in den Zellen keine Datumswerte stehen würde

und
2.
Muss Summenprodukt als Matrixformel eingegeben werden, ist doch schon eine Matrixformel?

[BoskoBiati]

Hallo atilla,

ich kenne den Tabellenaufbau nicht, sehe aber, dass in einer Zeile die Wochentage als Text stehen, was in der Regel nicht hilfreich ist.
Summenprodukt muß dann als Matrixformel eingegeben werden, wenn man mit MTRANS arbeitet. Wenn die Feiertage in einer senkrechten Liste stehen, dann ist sie falsch.

[atilla]

Hallo Edgar,

uuuups, wie ungeschickt. Habe "u.U geschickt" als ungeschickt gelesen. Sorry.


Das Mit Summenprodukt und Mtrans wusste ich nicht, danke für die Erklärung

[Thomas78]

Hallo Edgar, Attila und lupo1,

vielen Dank für eure Mühen, bisher hat leider nichts funktioniert...

Die Feiertage stehen C26 bis C44.

Ich dachte ich hätte mit der Formel 

=SUMMENPRODUKT((Urlaubsplan!D7:NJ7="Fr")*(Urlaubsplan!D8:NJ8>=DATWERT("01.01.2017"))*(Urlaubsplan!D8:NJ8<=DATWERT("31.01.2017"))*(Urlaubsplan!D9:NJ9="")*(Urlaubsplan!D8:NJ8=Feiertage))

Erfolg gehabt. Allerdings ändert sich am Ergebnis nichts wenn ich Text eingebe, also urlaub oder so.


Grüße Thomas

[atilla]

Hallo Thomas,

bevor Du weitermachst würde ich an Deiner Stelle jetzt etwas Zeit investieren und in Deiner Mappe die Angaben bezüglich Daten z.B Wochentage in Datumswerte ändern.
Du kannst die Optik mit der Formatierung hinbekommen.

Schau mal:

Arbeitsblatt mit dem Namen 'Tabelle1'   C D 2 01.01.2016 01 3   Fr 4   01. Jan 16 5   01. Jan 6   01.01.2016
Zelle Format Wert C2 TT.MM.JJJJ 01.01.2016 D2 TT 01.01.2016 C3 TT.MM.JJJJ   D3 TTT 01.01.2016 C4 TT.MM.JJJJ   D4 TT. MMM JJ 01.01.2016 C5 TT.MM.JJJJ   D5 TT. MMM 01.01.2016 C6 TT.MM.JJJJ   D6 TT.MM.JJJJ 01.01.2016	Zelle Formel D2 =$C$2 D3 =$C$2 D4 =$C$2 D5 =$C$2 D6 =$C$2

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

In der Bedingten Formatirung musst Du Deine Formeln auch anpassen.

Eine Abfrage auf Samtag oder Sonntag ginge Dann so:

Arbeitsblatt mit dem Namen 'Tabelle1'   C D E F 2 datum datum Samstag Sontag 3 02.01.2016 Sa WAHR FALSCH 4 03.01.2016 So FALSCH WAHR
Zelle Format Wert C2 TTT datum D2 TTT datum C3 TT.MM.JJJJ 02.01.2016 D3 TTT 02.01.2016 C4 TT.MM.JJJJ 03.01.2016 D4 TTT 03.01.2016	Zelle Formel C2 =$C1 D2 =$C1 D3 =$C3 E3 =WOCHENTAG(C3;2)=6 F3 =WOCHENTAG(C3;2)=7 D4 =$C4 E4 =WOCHENTAG(C4;2)=6 F4 =WOCHENTAG(C4;2)=7

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Die Formeln mit Wahr/Falsch müssten in der Bedingten Formatierung stehen.

Wenn Du Dir jetzt die Arbeit machst, wirst Du in Zukunft weniger Probleme mit Auswertungen bekommen.

[Thomas78]

Hallo Attila,

puh jetzt steh ich irgendwie auf dem Schlauch glaub ich.

Meinst du meine Zeilen 5-8 im Arbeitsblatt "Urlaubsplan"? Das sind doch Datumswerte die nur entsprechend formatiert sind. Oder hab ich jetzt nen kompletten Denkfehler?