Anzahl Feiertag in bestimmtem Zeitraum ermitteln

[WillWissen]

Hallo Freunde,

stehe gerade auf dem Schlauch, der Leitung und neben mir. ;)

Für eine Statistik benötige ich gesamt angemeldete Tage sowie Nettoarbeitstage in bestimmten Zeiträumen. Soweit, wie im Ausschnitt ersichtlich, kein Problem. Allerdings schaffe ich es nicht, die Anzahl der Feiertage (in Spalte "E") in den jeweiligen Zeiträumen zu ermitteln, damit ich diese ebenfalls noch abziehen kann.
Vllt. kann mir jemand anhand des Beispiels 21.12.15 / 8.1.16 auf die Sprünge helfen.



Tabelle2

	A	B	C	D	E	F	G	H
1	Beginn	Ende	Gesamttage	Nettotage	Anzahl FT		Feiertagsliste
2	06.05.2015	20.05.2015	14	11	1		01.11.15	Allerheiligen
3	25.04.2015	08.06.2015	44	33	4		25.12.15	1. Weihnachtsfeiertag
4	21.12.2015	08.01.2016	18	12	4		26.12.15	2. Weihnachtsfeiertag
5							01.01.16	Neujahr
6	Anmeldetag	Abmeldetag					06.01.16	Drei Könige
7	zählt mit	zählt nicht mit					25.03.16	Karfreitag

Formeln der Tabelle

Zelle Formel C2 =DATEDIF(A2;B2;"d") D2 =NETTOARBEITSTAGE.INTL(A2;B2;17)-1-E2 C3 =DATEDIF(A3;B3;"d") D3 =NETTOARBEITSTAGE.INTL(A3;B3;17)-1-E3 C4 =DATEDIF(A4;B4;"d") D4 =NETTOARBEITSTAGE.INTL(A4;B4;17)-1-E4

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Peter]

Hallo,

ich habe hier im Moment kein XL zur Verfügung, aber Zählenwenns oder Summenprodukt könnte ich mir vorstellen.

[WillWissen]

Hi Peter,

ich habe mit Verweisfunktionen und mit Summenprodukt gespielt. Mir fehlt da irgendwie das berühmte "Klick". Bin aber weiterhin am tüfteln und Tante-Gugl-auf-die-Nerven-gehen. :D

[steve1da]

Hola,

ungetestet:

Code:

=zählenwenns(G2:G7;">="&A2;G2:G7;"<="&B2)

Gruß,
steve1da

[RPP63]

Hallo Günter!
Geht schon mit Zählenwenns.
Aber: Was willst Du mit Wochenend-Feiertagen machen (der 1.11.15 ist ein Sonntag)?

Tabelle2

	B	C	D	E	F
11	Beginn	Ende	Anzahl FT	01.11.15	So
12	21.12.2015	08.01.2016	3	25.12.15	Fr
13				26.12.15	Sa
14				06.01.16	Mi

Formeln der Tabelle

Zelle Formel D12 =ZÄHLENWENNS(E11:E14;">="&B12;E11:E14;"<="&C12)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[Peter]

Hallo,

mit Summenprodukt (auch ungetestet):

=SUMMENPRODUKT(($G$2:$G$7>=A4)*($G$2:$G$7<=B4))

[WillWissen]

Hallo Peter, Ralf und Steve,

vielen Dank für eure Hilfe. Problem gelöst.

@ Ralf

Sofern ein Feiertag auf einen Sonntag fällt, nehme ich ihn aus meiner Feiertagsliste raus - das Datum im Original ist bereits  mit TTT TT... formatiert, so dass ich auf Anhieb sehe, welche Daten betroffen sind.

[Wilfried Höttl]

Hallo!

Auch wenn es schon gelöst ist, noch meine Variante.

Tabelle11

	A	B	C	D	E	F	G	H
2	Feiertage in Österreich mit Formeln ermitteln
3	2015
4
5	Do 01.01.2015	Neujahr
6	Di 06.01.2015	Heilige 3 Könige
7	So 05.04.2015	Ostersonntag
8	Mo 06.04.2015	Ostermontag
9	Fr 01.05.2015	Tag der Arbeit
10	Do 14.05.2015	Christi Himmelfahrt
11	So 24.05.2015	Pfingstsonntag
12	Mo 25.05.2015	Pfingstmontag
13	Do 04.06.2015	Fronleichnam
14	Sa 15.08.2015	Mariahimmelfahrt
15	Mo 26.10.2015	Nationalfeiertag
16	So 01.11.2015	Allerheiligen
17	Di 08.12.2015	Maria Empfängnis
18	Fr 25.12.2015	Christtag
19	Sa 26.12.2015	Stefanitag
20
21
22	Nettoarbeitstage ohne Sa, So, und Feiertage
23
24			oh.Sa,So	Gesamttage	Freie Tage	oh Sa,So	Feiertage	Feiertage an
25			oh Feiertage				gesamt	Werktage
26
27
28	Sa 01.08.2015	Mo 31.08.2015	21	31	10	21	1	0
29	Do 01.01.2015	Do 31.12.2015	251	365	114	261	15	10

Formeln der Tabelle

Zelle Formel A5 =DATUM($A$3;1;1) A6 =DATUM($A$3;1;6) A7 =7*DM((4&-A3)/7+REST(19*REST(A3;19)-7;30)*14%;)-6 A8 =$A$7+1 A9 =DATUM($A$3;5;1) A10 =$A$7+39 A11 =$A$7+49 A12 =$A$7+50 A13 =$A$7+60 A14 =DATUM($A$3;8;15) A15 =DATUM($A$3;10;26) A16 =DATUM($A$3;11;1) A17 =DATUM($A$3;12;8) A18 =DATUM($A$3;12;25) A19 =DATUM($A$3;12;26) C28 =WENN(ANZAHL(A28:B28)=2;NETTOARBEITSTAGE(A28;B28;$A$5:$A$20);"") D28 =B28-A28+1 E28 =D28-C28 F28 =NETTOARBEITSTAGE(A28;B28) G28 =SUMMENPRODUKT(($A$5:$A$19>=A28)*($A$5:$A$19<=B28)) H28 =F28-C28

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4



Gruß
Wilfried

[WillWissen]

Hi Wilfried,

danke auch für deinen Beitrag.

Ich habe die ZÄHLENWENNS-Variante gewählt, da meine Originaldatei eh schon Matrixformeln beinhaltet und dadurch die Performance etwas leidet.

@ Ralf und alle anderen Interessierten.

Feiertage, die auf einen Sonntag fallen, zähle ich nicht mit. Dafür habe ich die Feiertagsliste mit einer Spalte ergänzt, in der ein "x" geschrieben wird, sollte der Feiertag ein Sonntag sein und die Zählenwenns-Formel um einen Kriterienbereich ergänzt (.....;Feiertage!C:C;"<>"&"x")

[BoskoBiati]

Hallo,

ich hätte das so gemacht:

[html]

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
1	Beginn	Ende	Gesamttage	Nettotage	Anzahl FT		Feiertagsliste
2	06.05.2015	20.05.2015	14	12	1		01.01.2015		Do
3	25.04.2015	08.06.2015	44	34	4		06.01.2015		Di
4	21.12.2015	08.01.2016	18	14	3		03.04.2015		Fr
5							05.04.2015		So
6	Anmeldetag	Abmeldetag					06.04.2015		Mo
7	zählt mit	zählt nicht mit					01.05.2015		Fr
8							14.05.2015		Do
9							25.05.2015		Mo
10							04.06.2015		Do
11							03.10.2015		Sa
12							01.11.2015	Allerheiligen	So
13							25.12.2015	1. Weihnachtsfeiertag	Fr
14							26.12.2015	2. Weihnachtsfeiertag	Sa
15							01.01.2016	Neujahr	Fr
16							06.01.2016	Drei Könige	Mi
17							25.03.2016	Karfreitag	Fr

Zelle	Formel
C2	=B2-A2
D2	=NETTOARBEITSTAGE.INTL(A2;B2;17;FT)
E2	=NETTOARBEITSTAGE.INTL(A2;B2;17)-NETTOARBEITSTAGE.INTL(A2;B2;17;FT)

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

[/html]


Bitte beachten: NETTOARBEITSTAGE.INTL(x;y;17) bedeutet, dass nur der Sa als freier Tag gewertet wird! Ich weiß nicht, ob das Deine Intention ist.
Bedeutet für meine Formeln, dass der 26.12.15 nicht als Feiertag gewertet wird, da es ein Sa ist!


So, jetzt geht´s zum Betriebsausflug.