Clever-Excel-Forum

Normale Version: Anzahl Feiertag in bestimmtem Zeitraum ermitteln
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2
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

ABCDEFGH
1BeginnEndeGesamttageNettotageAnzahl FTFeiertagsliste
206.05.201520.05.20151411101.11.15Allerheiligen
325.04.201508.06.20154433425.12.151. Weihnachtsfeiertag
421.12.201508.01.20161812426.12.152. Weihnachtsfeiertag
501.01.16Neujahr
6AnmeldetagAbmeldetag06.01.16Drei Könige
7zählt mitzählt nicht mit25.03.16Karfreitag
Formeln der Tabelle
ZelleFormel
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
Hallo,

ich habe hier im Moment kein XL zur Verfügung, aber Zählenwenns oder Summenprodukt könnte ich mir vorstellen.
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
Hola,

ungetestet:


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


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

Tabelle2

BCDEF
11BeginnEndeAnzahl FT01.11.15So
1221.12.201508.01.2016325.12.15Fr
1326.12.15Sa
1406.01.16Mi
Formeln der Tabelle
ZelleFormel
D12=ZÄHLENWENNS(E11:E14;">="&B12;E11:E14;"<="&C12)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf
Hallo,

mit Summenprodukt (auch ungetestet):

=SUMMENPRODUKT(($G$2:$G$7>=A4)*($G$2:$G$7<=B4))
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.
Hallo!

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

Tabelle11

ABCDEFGH
2Feiertage in Österreich mit Formeln ermitteln
32015
4
5Do 01.01.2015Neujahr
6Di 06.01.2015Heilige 3 Könige
7So 05.04.2015Ostersonntag
8Mo 06.04.2015Ostermontag
9Fr 01.05.2015Tag der Arbeit
10Do 14.05.2015Christi Himmelfahrt
11So 24.05.2015Pfingstsonntag
12Mo 25.05.2015Pfingstmontag
13Do 04.06.2015Fronleichnam
14Sa 15.08.2015Mariahimmelfahrt
15Mo 26.10.2015Nationalfeiertag
16So 01.11.2015Allerheiligen
17Di 08.12.2015Maria Empfängnis
18Fr 25.12.2015Christtag
19Sa 26.12.2015Stefanitag
20
21
22Nettoarbeitstage ohne Sa, So, und Feiertage
23
24oh.Sa,So GesamttageFreie Tageoh Sa,SoFeiertageFeiertage an
25oh FeiertagegesamtWerktage
26
27
28Sa 01.08.2015Mo 31.08.20152131102110
29Do 01.01.2015Do 31.12.20152513651142611510
Formeln der Tabelle
ZelleFormel
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
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")
Hallo,

ich hätte das so gemacht:

[html]
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHI
1BeginnEndeGesamttageNettotageAnzahl FT Feiertagsliste  
206.05.201520.05.201514121 01.01.2015 Do
325.04.201508.06.201544344 06.01.2015 Di
421.12.201508.01.201618143 03.04.2015 Fr
5      05.04.2015 So
6AnmeldetagAbmeldetag    06.04.2015 Mo
7zählt mitzä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.2015AllerheiligenSo
13      25.12.20151. WeihnachtsfeiertagFr
14      26.12.20152. WeihnachtsfeiertagSa
15      01.01.2016NeujahrFr
16      06.01.2016Drei KönigeMi
17      25.03.2016KarfreitagFr

ZelleFormel
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.
Seiten: 1 2