Microsoft Excel

Herbers Excel/VBA-Archiv

Liste letzter Monat ohne Feiertage und Wochenende

Betrifft: Liste letzter Monat ohne Feiertage und Wochenende von: Tobias
Geschrieben am: 05.02.2020 17:30:04

Hallo Zusammen,

ich habe eine Liste entwickelt welche mir die letzten 31 ohne die Samstage und Sonntage anzeigt.

Dies habe ich folgendermaßen gemacht:

B3:            =WENN(TEXT(HEUTE()-1;"TTTT")="Sonntag";HEUTE()-3;HEUTE()-1)

B4-B26:        =WENNFEHLER(WENN(TEXT(B3-1;"TTTT")="Sonntag";WENN((B3-3)>(HEUTE()-31);B3-3;""); _
WENN((B3-1)>(HEUTE()-31);B3-1;""));"")

Jetzt würde ich allerdings gerne eine weitere Überprüfung drin haben. Und zwar ein Abgleich mit einer Liste wo die ganzen Feiertage und Betriesferien untereinander in einer Spalte P drin stehen.

Ich habe da schon etwas herumprobiert mit dieser Formel:
=WENNFEHLER(WENN((NICHT(ODER(TEXT(D28-1;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-1;P:P;0);0)>1))); D28-1;WENN((NICHT(ODER(TEXT(D28-3;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-3;P:P;0);0)>1)));D28-3; WENN((NICHT(ODER(TEXT(D28-4;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-4;P:P;0);0)>1)));D28-4; WENN((NICHT(ODER(TEXT(D28-5;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-5;P:P;0);0)>1)));D28-5; WENN((NICHT(ODER(TEXT(D28-6;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-6;P:P;0);0)>1)));D28-6; WENN((NICHT(ODER(TEXT(D28-7;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-7;P:P;0);0)>1)));D28-7; WENN((NICHT(ODER(TEXT(D28-8;"TTTT")="Sonntag";WENNFEHLER(VERGLEICH(D28-8;P:P;0);0)>1)));D28-8;""))))) ));"") 
Allerdings klappt es auch damit nicht richtig.
Habt ihr eine Idee wie ich das "einfach" realisieren kann?

Danke schon mal vorab für eure Bemühungen!

Gruß Tobias

Betrifft: AW: letzter Monat oder doch 31 Tage Mo - Fr ? ...
von: neopa C
Geschrieben am: 05.02.2020 17:55:55

Hallo Tobias,

... Deine ersten beiden Formeln ermitteln auch nur die ersten beiden Tage korrekt.

Die letzten 31 Tage ohne Samstage und Sonntage würde ich wie folgt ermitteln.

In B3:

=WENN(ZEILE(B1)>31;"";AGGREGAT(14;6;(HEUTE()-ZEILE(A$1:A$44))/(REST(HEUTE()-ZEILE(A$1:A$44);7)>1);ZEILE(B1)))

und weit genug nach unten ziehend kopieren.

Die Formel lässt sich natürlich noch erweitern, um auch die Feiertage und weitere freie Tage herauszurechnen. Stelle doch mal (D)eine Datentabelle dafür ein.

Gruß Werner
.. , - ...

Betrifft: AW: Formelerweiterung für Tage ohne freie Tage ...
von: neopa C
Geschrieben am: 05.02.2020 18:40:33

Hallo,

... dann in B3 so:

=WENN(ZEILE(C1)>31;"";AGGREGAT(14;6;(HEUTE()-ZEILE(A$1:A$44))/
(REST(HEUTE()-ZEILE(A$1:A$44);7)>1)/
(1-ZÄHLENWENN(_FreieTage[Frei_Tage];HEUTE()-ZEILE(A$1:A$44)))
;ZEILE(B1)))

Gruß Werner
.. , - ...

Betrifft: AW: Formelerweiterung für Tage ohne freie Tage ...
von: Tobias
Geschrieben am: 06.02.2020 09:56:58

Ich habe jetzt mal die Datei angehängt.

https://www.herber.de/bbs/user/135028.xlsx


Unten sind die versuche von mir.
Dein Beispiel funktioniert bei mir leider nicht so ganz.

Vielen Dank für deine Hilfe!

Gruß Tobias

Betrifft: AW: bei Deinen vielen "freien Tagen" ...
von: neopa C
Geschrieben am: 06.02.2020 10:22:25

Hallo Tobias,

... muss die Formel natürlich noch etwas angepasst werden.

Dann in B3 z.B. so:

=WENN(ZEILE(B1)>31;"";AGGREGAT(14;6;(HEUTE()-ZEILE(A$1:A$66))/
(REST(HEUTE()-ZEILE(A$1:A$66);7)>1)/
(1-ZÄHLENWENN(_FreieTage[Frei_Tage];HEUTE()-ZEILE(A$1:A$66)));ZEILE(B1)))

Deine "Betriebsferien oder Feiertage" hatte ich bei mir zu zu einer "intelligenten" Tabelle formatiert, diese von Tabelle1 zu Frei_Tage und Deine Überschrift zu "Frei_Tage" eingekürzt.

Damit passt sich die Formelauswertung automatisch geänderter Datenwertanzahl an und ist auch unabhängig davon, wo diese Liste steht oder hin verschoben wird.

Gruß Werner
.. , - ...

Beiträge aus dem Excel-Forum zum Thema "Liste letzter Monat ohne Feiertage und Wochenende"