Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Feiertage berücksichtigen
#1
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
Antwortento top
#2
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!
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antwortento top
#3
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
Antwortento top
#4
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"))...
Antwortento top
#5
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?
Gruß Atilla
Antwortento top
#6
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.
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antwortento top
#7
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
Gruß Atilla
Antwortento top
#8
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
Antwortento top
#9
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.
Gruß Atilla
Antwortento top
#10
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?


Angehängte Dateien
.xlsm   Mappe1.xlsm (Größe: 869,31 KB / Downloads: 1)
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste