Anzahl Dienstage ohne Feiertage?

Forumthread: Anzahl Dienstage ohne Feiertage?

JensH.

Hallo zusammen,
ich hätte mal wieder ein kleines Problem:
Benötigt wird eine Formel zur Berechnung der Anzahl der Dienstage in einem Monat (Anfangs und Enddatum vorhanden) exclusive der Feiertage aus einem Array (ähnlich wie Nettoarbeitstage).
Nettoarbeitstage (ohne Feiertage) und auch die Anzahl der Dienstage zu berechnen bekomme ich jeweils einzeln hin, aber wie kombiniere ich das um die Anzahl einzelner Wochentage exclusive der Feiertage heraus zu bekommen ?
Gruß
Jens

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 09:26:22

Matze,Matthias

Moin Jens,
Hier ein Muster von : http://www.excelformeln.de/formeln.html?welcher=251

	A	B	C
1	01.01.2012	2	5
2	31.01.2012

Formeln der Tabelle

Zelle	Formel
C1	=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A1&":"&A2));2)=B1)*1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
A1 : Statdatum
A2 : Enddatum
B1 : Wochentag der gesucht wird 1-7 Mo-So
C1 : Formel
Matze

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 09:48:01

JensH.

Moin Matze,
schöne Antwort .. beantwortet aber irgendwie nicht meine Frage, oder übersehe ich etwas?
Das ich die Anzahl des jeweiligen Wochentages berechnen kann hatte ich doch geschrieben. Mir geht es darum, die Anzahl der Wochentage abzüglich eventueller Feiertage zu berechnen.
Beispiel: Dezember 2012 hat 4 Dienstage gesamt, aber nur 3 Dienstage die auch Arbeitstage sind
Bekannt ist die Formel für Wochentage (siehe deine Antwort) und auch die Möglichkeit per NETTOARBEITSTAGE() die Arbeitstage zu berechnen.
Gesucht ist aber eine Kombination aus beiden!
Gruß
Jens

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 10:13:58

fcs

Hallo Jens,
Formel muss dann etwa so aussehen: =SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A1&":"&A2));2)=$B$1)*1) -SUMMENPRODUKT((A1=Feiertage)*(WOCHENTAG(Feiertage;2)=$B$1)) Gruß
Franz

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 10:41:33

JensH.

Hallo Franz,
nach einem ersten Test sage ich mal: Funktioniert ! ;-)
In B1 steht der gesuchte Wochentag?
Vielen Dank!
Gruß
Jens

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 10:15:28

Matze,Matthias

Hallo Jens,
kenne deinen Aufbau der Tabelle nicht, sind denn die Feiertage mit einem Namen deklariert?
Stehen die getrennt gelistet ? Welche Feiertage in Hessen?...
Ich bin da selbst noch am grübeln,....aber online kannste hier ganz schnell ein Ergibnis sehen:
http://www.schnelle-online.info/Arbeitstage-berechnen.html?arbeitstag=0&arbeitstag=1&arbeitstag=2&arbeitstag=3&arbeitstag=4&state=Hessen&from=01.01.2012&to=31.12.2012
Zu deiner Formel habe ich noch keinen Lösungsansatz, da mir die Informationen fehlen, ist aber auch knifflig.
VG Matze

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 10:32:34

JensH.

Der Rechner ist ja mal hübsch :-)
Der Aufbau der eigentlichen Tabelle ist doch nicht so relevant oder meinst du die Feiertage?
Die Feiertage sind als benannter Bereich (Array) angeordnet, wie bei NETTOARBEITSTAGE()
Beispielformel: =NETTOARBEITSTAGE(B5;B6;Feiertage)

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 11:04:21

Wilfried

Hallo!
Passt da was?
[html]

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K
1	Anzahl Wochentage+ Feiertage eines Monats auflisten
2
3	beliebiges Monatsdatum	Mo	Di	Mi	Do	Fr	Sa	So	FT Anzahl		FT
4	01.01.2012	5	5	4	4	3	4	4	2		So 01.01.2012
5	01.02.2012	4	4	5	4	4	4	4	0		Fr 06.01.2012
6	01.03.2012	4	4	4	5	5	5	4	0		So 08.04.2012
7	01.04.2012	4	4	4	4	4	4	4	2		Mo 09.04.2012
8	01.05.2012	3	4	5	4	4	4	3	4		Di 01.05.2012
9	01.06.2012	4	4	4	3	5	5	4	1		Do 17.05.2012
10	01.07.2012	5	5	4	4	4	4	5	0		So 27.05.2012
11	01.08.2012	4	4	4	5	5	4	4	1		Mo 28.05.2012
12	01.09.2012	4	4	4	4	4	5	5	0		Do 07.06.2012
13	01.10.2012	5	5	5	4	3	4	4	1		Mi 15.08.2012
14	01.11.2012	4	4	4	4	5	4	4	1		Fr 26.10.2012
15	01.12.2012	5	3	3	4	4	4	5	3		Do 01.11.2012
16											Sa 08.12.2012
17											Di 25.12.2012
18											Mi 26.12.2012
19

Zelle	Formel
B4	=RUNDEN((REST($A4-TAG($A4)-REST(SPALTE();7);7)+29-TAG($A4+32-TAG($A4)))/7;0)-SUMMENPRODUKT(($K$4:$K$20>=$A4)($K$4:$K$20DATUM(JAHR($A4);MONAT($A4)+1;1))(WOCHENTAG($K$4:$K$20;2)=SPALTE(A3)))

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

[/html]
Gruß
Wilfried

AW: Anzahl Dienstage ohne Feiertage?

02.08.2012 11:15:37

JensH.

Hallo Wilfried,
vielen Dank für deinen Vorschlag.
Ich habe jetzt allerdins angefangen Franz seinen Vorschlag umzusetzen
https://www.herber.de/forum/archiv/1272to1276/t1273471.htm#1273485
Gruß
Jens

Infobox / Tutorial

Anzahl der Dienstage ohne Feiertage in Excel berechnen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Daten eingeben: Lege in Excel ein Arbeitsblatt an und füge die folgenden Daten ein:
- In Zelle A1: Startdatum (z.B. 01.12.2023).
- In Zelle A2: Enddatum (z.B. 31.12.2023).
- In Zelle B1: Wochentag, den du berechnen möchtest (z.B. 2 für Dienstag).
Feiertage definieren: Erstelle einen benannten Bereich für die Feiertage. Trage die relevanten Feiertage in ein separates Feld ein (z.B. in den Zellen D1:D10) und benenne diesen Bereich als "Feiertage".

Formel für die Berechnung: Verwende die folgende Formel in Zelle C1, um die Anzahl der Dienstage ohne Feiertage zu berechnen:

=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A1&":"&A2));2)=B1)*1) - SUMMENPRODUKT((Feiertage>=A1)*(Feiertage<=A2)*(WOCHENTAG(Feiertage;2)=B1))

Ergebnis überprüfen: Das Ergebnis in Zelle C1 zeigt dir nun die Anzahl der Dienstage im angegebenen Zeitraum, abzüglich der Feiertage.

Häufige Fehler und Lösungen

Fehler: Die Formel gibt einen Fehler zurück oder das Ergebnis ist nicht wie erwartet.
- Lösung: Überprüfe, ob die Feiertage korrekt im benannten Bereich definiert sind und ob die Daten in den Zellen A1 und A2 richtig eingegeben wurden.
Fehler: Das Ergebnis zählt mehr Dienstage als erwartet.
- Lösung: Stelle sicher, dass die Feiertage korrekt im Array enthalten sind und dass du keine Feiertage doppelt erfasst hast.

Alternative Methoden

Pivot-Tabellen: Eine Pivot-Tabelle kann genutzt werden, um die Anzahl der Dienstage in einem festgelegten Zeitraum zu aggregieren. Dies ist besonders nützlich, wenn du eine große Datenmenge hast.
Power Query: In Excel kannst du auch Power Query verwenden, um eine dynamische Abfrage zu erstellen, die die Wochentage basierend auf deinem Datensatz automatisch berechnet.

Praktische Beispiele

Beispiel 1: Berechnung der Dienstage im Dezember 2023:
- Startdatum: 01.12.2023
- Enddatum: 31.12.2023
- Feiertage: 25.12.2023 (1. Weihnachtstag)
Beispiel 2: Berechnung der Dienstage im Jahr 2024:
- Startdatum: 01.01.2024
- Enddatum: 31.12.2024
- Feiertage: 01.01.2024 (Neujahr) und 25.12.2024 (1. Weihnachtstag)

Tipps für Profis

Nutze die Funktion NETTOARBEITSTAGE(), um schnell die Anzahl der Arbeitstage zu berechnen, wenn du mit mehreren Wochentagen arbeiten möchtest.
Wenn du regelmäßig die Anzahl der Wochen- oder Arbeitstage für unterschiedliche Jahre benötigst, erstelle eine Vorlage, die du einfach anpassen kannst.
Achte darauf, die Feiertage für jedes Jahr zu aktualisieren, um immer die korrekten Nettoarbeitstage zu ermitteln.

FAQ: Häufige Fragen

1. Wie viele Dienstage hat das Jahr 2023?
Das Jahr 2023 hat insgesamt 52 Dienstage.

2. Wie viele Dienstage hat das Jahr 2024?
Im Jahr 2024 gibt es 52 Dienstage, jedoch in einem Schaltjahr könnte es einen zusätzlichen Dienstag geben.

3. Wie kann ich die Anzahl der Brückentage 2012 berechnen?
Verwende die Funktion NETTOARBEITSTAGE(), um die Arbeitstage zwischen Feiertagen zu zählen und die Brückentage zu ermitteln.

4. Kann ich die Formel auch für andere Wochentage nutzen?
Ja, ändere einfach den Wert in Zelle B1 auf den gewünschten Wochentag (1 für Montag, 2 für Dienstag, etc.).

Beliebteste Forumthreads (12 Monate)

Forumthread: Anzahl Dienstage ohne Feiertage?

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Anzahl der Dienstage ohne Feiertage in Excel berechnen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Anzahl Dienstage ohne Feiertage?

Forumthread: Anzahl Dienstage ohne Feiertage?

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Anzahl der Dienstage ohne Feiertage in Excel berechnen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread