Feiertage zählen

Forumthread: Feiertage zählen

Feiertage zählen

Ralf

Hallo Forum,
ich möchte für eine Maschinenauslastungsberechnung Feiertage zählen.
Wie in der Beispieldatei zu sehen ist, habe ich die Feiertage eingetragen bzw. berechnet. Nun möchte ich in der Zeile 19 die gesetzlichen Feiertage (also ohne die rot eingefärbten) aus dem Bereich B25:D41 dem entsprechenden Monat zu ordnen und zusätzlich die Feiertage, die auf Arbeitstage fallen (nur die sind für mich entscheidend) dem entsprechenden Monat zu ordnen.
https://www.herber.de/bbs/user/69752.xls
Wer kann mir helfen?
Vielen Dank schon mal im Voraus für Eure Hilfe.
Gruß
Ralf

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Feiertage zählen

26.05.2010 19:14:58

Backowe

Hi Ralf,
Tabelle1

	A	B	C	D	E	F	G	H	I	J	K	L	M
19	Feiertage	2	0	0	2	2	1	0	0	0	0	1	0

Formeln der Tabelle

Zelle	Formel
B19	=SUMMENPRODUKT(($B$25:$B$41>=B21)($B$25:$B$41<=B22)(WOCHENTAG($B$25:$B$41;2)<6))
C19	=SUMMENPRODUKT(($B$25:$B$41>=C21)($B$25:$B$41<=C22)(WOCHENTAG($B$25:$B$41;2)<6))
D19	=SUMMENPRODUKT(($B$25:$B$41>=D21)($B$25:$B$41<=D22)(WOCHENTAG($B$25:$B$41;2)<6))
E19	=SUMMENPRODUKT(($B$25:$B$41>=E21)($B$25:$B$41<=E22)(WOCHENTAG($B$25:$B$41;2)<6))
F19	=SUMMENPRODUKT(($B$25:$B$41>=F21)($B$25:$B$41<=F22)(WOCHENTAG($B$25:$B$41;2)<6))
G19	=SUMMENPRODUKT(($B$25:$B$41>=G21)($B$25:$B$41<=G22)(WOCHENTAG($B$25:$B$41;2)<6))
H19	=SUMMENPRODUKT(($B$25:$B$41>=H21)($B$25:$B$41<=H22)(WOCHENTAG($B$25:$B$41;2)<6))
I19	=SUMMENPRODUKT(($B$25:$B$41>=I21)($B$25:$B$41<=I22)(WOCHENTAG($B$25:$B$41;2)<6))
J19	=SUMMENPRODUKT(($B$25:$B$41>=J21)($B$25:$B$41<=J22)(WOCHENTAG($B$25:$B$41;2)<6))
K19	=SUMMENPRODUKT(($B$25:$B$41>=K21)($B$25:$B$41<=K22)(WOCHENTAG($B$25:$B$41;2)<6))
L19	=SUMMENPRODUKT(($B$25:$B$41>=L21)($B$25:$B$41<=L22)(WOCHENTAG($B$25:$B$41;2)<6))
M19	=SUMMENPRODUKT(($B$25:$B$41>=M21)($B$25:$B$41<=M22)(WOCHENTAG($B$25:$B$41;2)<6))-2

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Jürgen

Kleine Änderung

26.05.2010 20:58:38

Backowe

Hi,
Tabelle1

	A	B	C	D	E	F	G	H	I	J	K	L	M
19	Feiertage	2	0	0	3	4	1	0	1	0	1	1	2
20	Feiertage an einem Wochentag	2	0	0	2	2	1	0	0	0	0	1	0

Formeln der Tabelle

Zelle	Formel
B19	=SUMMENPRODUKT(($B$25:$B$41>=B$21)*($B$25:$B$41<=B$22))
C19	=SUMMENPRODUKT(($B$25:$B$41>=C$21)*($B$25:$B$41<=C$22))
D19	=SUMMENPRODUKT(($B$25:$B$41>=D$21)*($B$25:$B$41<=D$22))
E19	=SUMMENPRODUKT(($B$25:$B$41>=E$21)*($B$25:$B$41<=E$22))
F19	=SUMMENPRODUKT(($B$25:$B$41>=F$21)*($B$25:$B$41<=F$22))
G19	=SUMMENPRODUKT(($B$25:$B$41>=G$21)*($B$25:$B$41<=G$22))
H19	=SUMMENPRODUKT(($B$25:$B$41>=H$21)*($B$25:$B$41<=H$22))
I19	=SUMMENPRODUKT(($B$25:$B$41>=I$21)*($B$25:$B$41<=I$22))
J19	=SUMMENPRODUKT(($B$25:$B$41>=J$21)*($B$25:$B$41<=J$22))
K19	=SUMMENPRODUKT(($B$25:$B$41>=K$21)*($B$25:$B$41<=K$22))
L19	=SUMMENPRODUKT(($B$25:$B$41>=L$21)*($B$25:$B$41<=L$22))
M19	=SUMMENPRODUKT(($B$25:$B$41>=M$21)*($B$25:$B$41<=M$22))-2
B20	=SUMMENPRODUKT(($B$25:$B$41>=B$21)($B$25:$B$41<=B$22)(WOCHENTAG($B$25:$B$41;2)<6))
C20	=SUMMENPRODUKT(($B$25:$B$41>=C$21)($B$25:$B$41<=C$22)(WOCHENTAG($B$25:$B$41;2)<6))
D20	=SUMMENPRODUKT(($B$25:$B$41>=D$21)($B$25:$B$41<=D$22)(WOCHENTAG($B$25:$B$41;2)<6))
E20	=SUMMENPRODUKT(($B$25:$B$41>=E$21)($B$25:$B$41<=E$22)(WOCHENTAG($B$25:$B$41;2)<6))
F20	=SUMMENPRODUKT(($B$25:$B$41>=F$21)($B$25:$B$41<=F$22)(WOCHENTAG($B$25:$B$41;2)<6))
G20	=SUMMENPRODUKT(($B$25:$B$41>=G$21)($B$25:$B$41<=G$22)(WOCHENTAG($B$25:$B$41;2)<6))
H20	=SUMMENPRODUKT(($B$25:$B$41>=H$21)($B$25:$B$41<=H$22)(WOCHENTAG($B$25:$B$41;2)<6))
I20	=SUMMENPRODUKT(($B$25:$B$41>=I$21)($B$25:$B$41<=I$22)(WOCHENTAG($B$25:$B$41;2)<6))
J20	=SUMMENPRODUKT(($B$25:$B$41>=J$21)($B$25:$B$41<=J$22)(WOCHENTAG($B$25:$B$41;2)<6))
K20	=SUMMENPRODUKT(($B$25:$B$41>=K$21)($B$25:$B$41<=K$22)(WOCHENTAG($B$25:$B$41;2)<6))
L20	=SUMMENPRODUKT(($B$25:$B$41>=L$21)($B$25:$B$41<=L$22)(WOCHENTAG($B$25:$B$41;2)<6))
M20	=SUMMENPRODUKT(($B$25:$B$41>=M$21)($B$25:$B$41<=M$22)(WOCHENTAG($B$25:$B$41;2)<6))-2

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

AW: Kleine Änderung

26.05.2010 22:02:47

Ralf

Hallo Backowe,
vielen Dank für die Hilfe.
Fast Gut. Bei dem "bedingten Format" in meinem Beispiel im Monat Dezember hast Du ja ein wenig getrickst in dem Du einfach 2 Tage subtrahierst. Da wäre meine Vorstellung eigentlich, dies über eine bedingte Formatierung zu lösen. Es gibt ja mehrere unterschiedliche regionale "Feiertage" in Deutschland. Z. B. Friedensfest in Augsburg am 8.8., Rosenmontag und Altweiber im Rheinland, Faschingsdienstag in Bayern, Buß- und Bettag, usw., die ich gerne über diese Formatierung (Zelleneinfärbung in rot) bei Bedarf ein- bzw. ausblenden möchte.
Ansonsten funktioniert dies tadellos. Vielleicht kannst meinen speziellen wunsch noch berücksichtigen.
Vielen dank für Deine Hilfe.
Gruß
Ralf

AW: Kleine Änderung

27.05.2010 11:27:21

Wilfried

Hallo!
Hilft Dir das weiter.
Die gesamt Arbeitstage/Jahr sind bei Dir 259 Tage richtig sind es 261 Tage. Jänner?
Tabelle2

	A	B	C	D	E	F	G	H	I	J
2	Nettoarbeitstage ohne Sa, So, und Feiertage
3
4	Fr 01.01.2010	Neujahr
5	Mi 06.01.2010	Heilige 3 Könige
6	So 04.04.2010	Ostersonntag
7	Mo 05.04.2010	Ostermontag
8	Sa 01.05.2010	Tag der Arbeit
9	Do 13.05.2010	Christi Himmelfahrt
10	So 23.05.2010	Pfingstsonntag
11	Mo 24.05.2010	Pfingstmontag
12	Do 03.06.2010	Fronleichnam
13	So 15.08.2010	Mariahimmelfahrt
14	Di 26.10.2010	Nationalfeiertag
15	Mo 01.11.2010	Allerheiligen
16	Mi 08.12.2010	Maria Empfängnis
17	Sa 25.12.2010	Christtag
18	So 26.12.2010	Stefanitag
19
20									Wieviele	Wieviele
21			oh.Sa,So	Gesamttage	Freie Tage	oh Sa,So	Wieviele	Wieviele	Feiertage	Feiertage an
22			oh Feiertage				Sa	So	gesamt	Werktage
23	Fr 01.01.2010	So 31.01.2010	19	31	12	21	5	5	2	2
24	Mo 01.02.2010	So 28.02.2010	20	28	8	20	4	4	0	0
25	Mo 01.03.2010	Mi 31.03.2010	23	31	8	23	4	4	0	0
26	Do 01.04.2010	Fr 30.04.2010	21	30	9	22	4	4	2	1
27	Sa 01.05.2010	Mo 31.05.2010	19	31	12	21	5	5	4	2
28	Di 01.06.2010	Mi 30.06.2010	21	30	9	22	4	4	1	1
29	Do 01.07.2010	Sa 31.07.2010	22	31	9	22	5	4	0	0
30	So 01.08.2010	Di 31.08.2010	22	31	9	22	4	5	1	0
31	Mi 01.09.2010	Do 30.09.2010	22	30	8	22	4	4	0	0
32	Fr 01.10.2010	So 31.10.2010	20	31	11	21	5	5	1	1
33	Mo 01.11.2010	Di 30.11.2010	21	30	9	22	4	4	1	1
34	Mi 01.12.2010	Fr 31.12.2010	22	31	9	23	4	4	3	1
35
36			252	365	113	261	52	52	15	9

verwendete Formeln
Zelle	Formel	Bereich
B23	=DATUM(JAHR(A23);MONAT(A23)+1;0)
C23	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A23&":"&B23));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A23)($A$4:$A$18<=B23)*(WOCHENTAG($A$4:$A$18;2)<6))
D23	=B23-A23+1
E23	=D23-C23
F23	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A23&":"&B23));2)<6)*1)
G23	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A23&":"&B23));2)=6))
H23	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A23&":"&B23));2)=7))
I23	=SUMMENPRODUKT(($A$4:$A$18>=A23)*($A$4:$A$18<=B23))
J23	=F23-C23
B24	=DATUM(JAHR(A24);MONAT(A24)+1;0)
C24	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A24&":"&B24));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A24)($A$4:$A$18<=B24)*(WOCHENTAG($A$4:$A$18;2)<6))
D24	=B24-A24+1
E24	=D24-C24
F24	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A24&":"&B24));2)<6)*1)
G24	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A24&":"&B24));2)=6))
H24	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A24&":"&B24));2)=7))
I24	=SUMMENPRODUKT(($A$4:$A$18>=A24)*($A$4:$A$18<=B24))
J24	=F24-C24
B25	=DATUM(JAHR(A25);MONAT(A25)+1;0)
C25	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A25&":"&B25));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A25)($A$4:$A$18<=B25)*(WOCHENTAG($A$4:$A$18;2)<6))
D25	=B25-A25+1
E25	=D25-C25
F25	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A25&":"&B25));2)<6)*1)
G25	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A25&":"&B25));2)=6))
H25	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A25&":"&B25));2)=7))
I25	=SUMMENPRODUKT(($A$4:$A$18>=A25)*($A$4:$A$18<=B25))
J25	=F25-C25
B26	=DATUM(JAHR(A26);MONAT(A26)+1;0)
C26	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A26&":"&B26));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A26)($A$4:$A$18<=B26)*(WOCHENTAG($A$4:$A$18;2)<6))
D26	=B26-A26+1
E26	=D26-C26
F26	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A26&":"&B26));2)<6)*1)
G26	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A26&":"&B26));2)=6))
H26	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A26&":"&B26));2)=7))
I26	=SUMMENPRODUKT(($A$4:$A$18>=A26)*($A$4:$A$18<=B26))
J26	=F26-C26
B27	=DATUM(JAHR(A27);MONAT(A27)+1;0)
C27	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A27&":"&B27));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A27)($A$4:$A$18<=B27)*(WOCHENTAG($A$4:$A$18;2)<6))
D27	=B27-A27+1
E27	=D27-C27
F27	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A27&":"&B27));2)<6)*1)
G27	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A27&":"&B27));2)=6))
H27	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A27&":"&B27));2)=7))
I27	=SUMMENPRODUKT(($A$4:$A$18>=A27)*($A$4:$A$18<=B27))
J27	=F27-C27
B28	=DATUM(JAHR(A28);MONAT(A28)+1;0)
C28	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A28&":"&B28));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A28)($A$4:$A$18<=B28)*(WOCHENTAG($A$4:$A$18;2)<6))
D28	=B28-A28+1
E28	=D28-C28
F28	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A28&":"&B28));2)<6)*1)
G28	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A28&":"&B28));2)=6))
H28	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A28&":"&B28));2)=7))
I28	=SUMMENPRODUKT(($A$4:$A$18>=A28)*($A$4:$A$18<=B28))
J28	=F28-C28
B29	=DATUM(JAHR(A29);MONAT(A29)+1;0)
C29	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A29&":"&B29));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A29)($A$4:$A$18<=B29)*(WOCHENTAG($A$4:$A$18;2)<6))
D29	=B29-A29+1
E29	=D29-C29
F29	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A29&":"&B29));2)<6)*1)
G29	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A29&":"&B29));2)=6))
H29	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A29&":"&B29));2)=7))
I29	=SUMMENPRODUKT(($A$4:$A$18>=A29)*($A$4:$A$18<=B29))
J29	=F29-C29
B30	=DATUM(JAHR(A30);MONAT(A30)+1;0)
C30	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A30&":"&B30));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A30)($A$4:$A$18<=B30)*(WOCHENTAG($A$4:$A$18;2)<6))
D30	=B30-A30+1
E30	=D30-C30
F30	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A30&":"&B30));2)<6)*1)
G30	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A30&":"&B30));2)=6))
H30	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A30&":"&B30));2)=7))
I30	=SUMMENPRODUKT(($A$4:$A$18>=A30)*($A$4:$A$18<=B30))
J30	=F30-C30
B31	=DATUM(JAHR(A31);MONAT(A31)+1;0)
C31	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A31&":"&B31));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A31)($A$4:$A$18<=B31)*(WOCHENTAG($A$4:$A$18;2)<6))
D31	=B31-A31+1
E31	=D31-C31
F31	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A31&":"&B31));2)<6)*1)
G31	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A31&":"&B31));2)=6))
H31	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A31&":"&B31));2)=7))
I31	=SUMMENPRODUKT(($A$4:$A$18>=A31)*($A$4:$A$18<=B31))
J31	=F31-C31
B32	=DATUM(JAHR(A32);MONAT(A32)+1;0)
C32	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A32&":"&B32));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A32)($A$4:$A$18<=B32)*(WOCHENTAG($A$4:$A$18;2)<6))
D32	=B32-A32+1
E32	=D32-C32
F32	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A32&":"&B32));2)<6)*1)
G32	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A32&":"&B32));2)=6))
H32	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A32&":"&B32));2)=7))
I32	=SUMMENPRODUKT(($A$4:$A$18>=A32)*($A$4:$A$18<=B32))
J32	=F32-C32
B33	=DATUM(JAHR(A33);MONAT(A33)+1;0)
C33	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A33&":"&B33));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A33)($A$4:$A$18<=B33)*(WOCHENTAG($A$4:$A$18;2)<6))
D33	=B33-A33+1
E33	=D33-C33
F33	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A33&":"&B33));2)<6)*1)
G33	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A33&":"&B33));2)=6))
H33	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A33&":"&B33));2)=7))
I33	=SUMMENPRODUKT(($A$4:$A$18>=A33)*($A$4:$A$18<=B33))
J33	=F33-C33
B34	=DATUM(JAHR(A34);MONAT(A34)+1;0)
C34	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A34&":"&B34));2)<6)1)-SUMMENPRODUKT(($A$4:$A$18>=A34)($A$4:$A$18<=B34)*(WOCHENTAG($A$4:$A$18;2)<6))
D34	=B34-A34+1
E34	=D34-C34
F34	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A34&":"&B34));2)<6)*1)
G34	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A34&":"&B34));2)=6))
H34	=SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A34&":"&B34));2)=7))
I34	=SUMMENPRODUKT(($A$4:$A$18>=A34)*($A$4:$A$18<=B34))
J34	=F34-C34
C36	=SUMME(C23:C35)
D36	=SUMME(D23:D35)
E36	=SUMME(E23:E35)
F36	=SUMME(F23:F35)
G36	=SUMME(G23:G35)
H36	=SUMME(H23:H35)
I36	=SUMME(I23:I35)
J36	=SUMME(J23:J35)

Tabellendarstellung in Foren Version 5.24

Gruß
Wilfried

AW: Kleine Änderung

27.05.2010 16:15:34

Wilfried

Hallo Ralf!
Habe ein wenig an Deiner Tabelle gebastelt, passt es jetzt?
Bei Feiertage gebe ein "X" ein und das Datum wird übernommen, ist die Zelle leer erscheint FALSCH und
kein Datum wird übernommen. Keine elegante Lösung, wenn kein "X" sollte die Nachbarzelle auch leer bleiben.
Vielleicht hat jemand eine bessere Formel.
https://www.herber.de/bbs/user/69770.xls
Gruß
Wilfried

AW: Kleine Änderung

28.05.2010 11:35:08

Ralf

Hallo Wilfried,
passt.
Danke für Deine Hilfe.
Gruß
Ralf

Beliebteste Forumthreads (12 Monate)

Forumthread: Feiertage zählen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread