Clever-Excel-Forum

Normale Version: Feiertage berücksichtigen
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2 3
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
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!
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übertragbereits 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
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"))...
Hallo Edgar,

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

1.

Zitat: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?
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.
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
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
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'
 CD
201.01.201601
3 Fr
4 01. Jan 16
5 01. Jan
6 01.01.2016
ZelleFormatWert
C2TT.MM.JJJJ01.01.2016
D2TT01.01.2016
C3TT.MM.JJJJ 
D3TTT01.01.2016
C4TT.MM.JJJJ 
D4TT. MMM JJ01.01.2016
C5TT.MM.JJJJ 
D5TT. MMM01.01.2016
C6TT.MM.JJJJ 
D6TT.MM.JJJJ01.01.2016
ZelleFormel
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'
 CDEF
2datumdatumSamstagSontag
302.01.2016SaWAHRFALSCH
403.01.2016SoFALSCHWAHR
ZelleFormatWert
C2TTTdatum
D2TTTdatum
C3TT.MM.JJJJ02.01.2016
D3TTT02.01.2016
C4TT.MM.JJJJ03.01.2016
D4TTT03.01.2016
ZelleFormel
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.
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?
Seiten: 1 2 3