Zahlungsziel Stichtag ohne Wochenende und Feiertag

Bild

Betrifft: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 24.04.2015 14:45:31

Hallo,
für die nachstehende Aufgabe finde ich nach längerem Probieren und Suchen leider keinen Lösungsansatz.
Für Rechnungen besteht ein Zahlungsziel von 30 Tagen. In dem Fall, dass der "letzte Stichtag" auf ein Wochenende und/oder auf einen Feiertag fällt, soll der nächste Werktag als Datum ausgegeben werden. Die Feiertage sind entsprechend angelegt. Die Add-In-Analyse ist aktiviert.
Beispiel:
RE - Datum 01.04.2015
Stichtag 01.05.2015 (Feiertag)
Wochenende 02.- 03.2015
Fälligkeit 04.05.2015
Userbild
Hoffe, mir kann jemand jemand helfen. Für eine Lösung wäre ich dankbar.
Gruß Norbert

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Daniel
Geschrieben am: 24.04.2015 14:56:32
Hi
die BruteForce-Lösung wäre folgende:
1. liste in einer Spalte (A) untereinander alle Werktage auf (dh bspw erste gesamtliste erstellen und dann alle WE und Feiertage löschen)
Sortiere diese Spalte absteigend (jüngstes Datum oben)
2. wenn diese Werktagsliste steht (bspw in Spalte A), kannst du das Zahlungsziel so berechnen:
=Index(A:A;Vergleich(ReDatum+30;A:A;-1))
Gruß Daniel

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 24.04.2015 15:41:35
Vielen Dank für schnelle Antwort Daniel,
insgesamt handelt es sich um mehrere Zahlungsziele die abgedeckt werden müsssen. Die Exceldatei wird für jeden Kunden separat angelegt. Rückliegende Daten müssen erhalten bleiben und zukünfige abgedeckt sein. Bei deinem Vorschlag müsste eine Gesamtliste für das aktuelle und jedes Folgejahr angelegt werden. Dazu noch die Feiertage je nach Bundesland für die Kalenderjahre. Quasi ein separates Arbeitsblatt für die Berechnung des Falligkeitstages jeglicher Kalenderjahre.
Gibt es da keine einfachere Lösung?? Im Grunde Suche ich nach einer Formel, die das abdeckt:
Eingabe: Rechnungsadtum + angelegte Tage = Falligkeitstag mit Berücksichtigung des Wochenendes und/oder des Feiertages auf den nächsten Werktag.

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Daniel
Geschrieben am: 24.04.2015 15:42:27
Hi
probiers mal mit folgender Matrix-Formel (Eingabe immer mit STRG+SHIFT+ENTER abschließen):

=MIN(WENN(WOCHENTAG(A1+ZEILE($A$30:$A$35);2)<6;WENN(ZÄHLENWENN(Feiertage;A1+ZEILE($A$30:$A$35))=0; A1+ZEILE($A$30:$A$35)))) 
das Rechungsdatum steht in A1, die Feiertage im benannten Bereich "Feiertage".
das Zahlungsziel sind 30 Tage, dieses kann sich maximal um 4 Tage verschieben (2x WE + 2 aufeinander folgende Feiertage (Weihnachten).
der Zahlungszeitraum wird über den Bereich Zeile(A30:A35) festgelegt.
wenn du mit einem flexiblen Zahlungziel arbeiten willst, dann ersetzte das Zeile($A$30:$A$35) durch Zahlungsziel-1+Zeile($A$1:$A$5)
sollten mehr als 4 Feiertage direkt hintereinander möglich sein, musst du halt den Bereich für ZEILE() entsprechend vergrössern.
Gruß Daniel

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 24.04.2015 16:14:09
Habe das ausprobiert. Bekomme aber kein Datum ausgegeben.
Rechnungsdatum in A1: 01.04.2015 Zahlungsziel in A2: 30
Userbild

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Daniel
Geschrieben am: 24.04.2015 17:43:39
naja, einerseits hast du meine Formel gegenüber meiner Vorgabe startk verkürzt, da kann nichts richtiges bei rauskommen.
andererseits, wenn bei der Formel eine Zahl grösser 40.000 rauskommt, dann ist das ein Datum.
Du musst nur die Zelle noch mit einem Datum-Zahlenformat formatieren.
Gruß Daniel

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 24.04.2015 23:23:37
Hallo Daniel,
habe in diversen Versuchen versucht die Formel auf meine angelegtes Format anzupassen. Mit meinen begrenzten Kenntnissen leider nach wie vor mir einem fehlerhaften Ergebnis.
Der Screenshot zeigt mein derzeit angelegtes Format. Hoffe, das Du mir dabei nochmal weiterhelfen kannst. Wie formatiere ich denn das Datum-Zahlenformat??
Userbild

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Daniel
Geschrieben am: 24.04.2015 23:43:55
mit einem Bild kann ich nicht viel anfangen. Ich sehe ja nicht mal Zeilen- und Spaltennummern um dir eine passende Formel zu formulieren.
wie man einer Zeile ein bestimmtes Zahlenformat verpasst, ist eigentlich grundwissen.
Gruß Daniel

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 25.04.2015 00:20:06
noch jemand der spät arbeitet! Nachstehend nochmal der Screenshot mit Zeilen- und Spaltennummern. In der Spalte F sind bereits Formeln angelegt die das Wochenende berücksichtigen, so das der nächste Werktag als Stichtag ausgegeben wird. In Spalte H soll nun der Feiertag berücksichtigt werden und der darauffolgende Werktag als Stichtag ausgegeben werden.
Mein Grundwissen versuche ich schon zu erweiteren. Gebe mir schon Mühe. Alles auf einmal schaffe ich nicht.
Gruß Norbert
Userbild

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Daniel
Geschrieben am: 25.04.2015 00:34:42
statt vieler Worte:
https://www.herber.de/bbs/user/97302.xlsx
die Formel in C2 ist eine Matrixformel. Die Eingabe muss immer STRG+SHIFT+ENTER abgeschlossen werden
Gruß Daniel

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 25.04.2015 01:03:34
Ok, gelöst. Vielen Dank für deine Mühe.
Gruß Norbert

Bild

Betrifft: AW: Zahlungsziel Stichtag ohne Wochenende und Feiertag
von: Norbert
Geschrieben am: 24.04.2015 23:33:08
Habe vergessen, die Frage als offenes Thema zu aktivieren.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zahlungsziel Stichtag ohne Wochenende und Feiertag"