Office: (Office 2016) Fortlaufender Kalender/Dienstplan mit durch Wochentage definiertem Beginn

Ich kämpfe gerade recht erfolglos mit unserem Dienstplan und benötige eure Hilfe. Unser Dienstplan wird immer quartalsweise erstellt. In „A1“ steht der erste Tag des anstehenden Quartals. Das Kürzel FT in Spalte “A“ steht für Feiertage und wird manuell eingetragen (könnt ihr ignorieren). In Spalte „B“ stehen die Wochentage fest drin (Montag bis Sonntag).

In Spalte „C“ soll nun das entsprechende Datum eingetragen werden:
Das erste Quartal 2020 („A1“ = 1.1.2020) beginnt an einem Mittwoch, also muss auf „C5“ mit dem 01.01.2020 beginnen. Die Datumszählung sollte mit dem 31.03. auf „C95“ wieder aufhören.

Das zweite Quartal 2020 („A1“ = 1.4.2020) beginnt an einem Samstag, also muss auf „C8“ mit dem 01.04.2020 beginnen. Die Datumszählung mit dem 30.06 auf „C98“ wieder aufhören.

Die Angehängte Tabelle dient als Veranschaulichung, die Daten sind jedoch bisher manuell eingetragen.

 

Mal abgesehen davon, dass der 01.04.2020 auch ein Mittwoch ist, ist die Lösung die folgende:

Hier die Formeln für die entsprechenden Zellen

Code:

C3  =WENN(WOCHENTAG($A$1;2)=1;$A$1;"")
C4  =WENN(C3="";WENN(WOCHENTAG($A$1;2)=2;$A$1;"");C3+1)
C5  =WENN(C4="";WENN(WOCHENTAG($A$1;2)=3;$A$1;"");C4+1)
C6  =WENN(C5="";WENN(WOCHENTAG($A$1;2)=4;$A$1;"");C5+1)
C7  =WENN(C6="";WENN(WOCHENTAG($A$1;2)=5;$A$1;"");C6+1)
C8  =WENN(C7="";WENN(WOCHENTAG($A$1;2)=6;$A$1;"");C7+1)
C9  =WENN(C8="";WENN(WOCHENTAG($A$1;2)=7;$A$1;"");C8+1)

Die Funktion WOCHENTAG gibt dir den Wochentag eines Datums zurück. Mit der 2 als zweiten Parameter mit den Rückgabewerten 1 für Montag bis 7 für Sonntag.

Mit dem ersten WENN prüfst du, ob in der Zelle oberhalb bereits etwas steht.

Die Zellen C10 bis C92 bleiben bestehen.
Für das Ende kannst du die folgenden Funktionen verwenden.

Code:

C93 =WENN(C92="";"";WENN(ODER(UND(TAG(C92)=31;MONAT(C92)=3);UND(TAG(C92)=30;MONAT(C92)=6);UND(TAG(C92)=30;MONAT(C92)=9);UND(TAG(C92)=31;MONAT(C92)=12));"";C92+1))
C94 =WENN(C93="";"";WENN(ODER(UND(TAG(C93)=31;MONAT(C93)=3);UND(TAG(C93)=30;MONAT(C93)=6);UND(TAG(C93)=30;MONAT(C93)=9);UND(TAG(C93)=31;MONAT(C93)=12));"";C93+1))
C95 =WENN(C94="";"";WENN(ODER(UND(TAG(C94)=31;MONAT(C94)=3);UND(TAG(C94)=30;MONAT(C94)=6);UND(TAG(C94)=30;MONAT(C94)=9);UND(TAG(C94)=31;MONAT(C94)=12));"";C94+1))

Du prüfst zunächst, ob die Zelle oberhalb leer ist. Ist dies der Fall ist die entsprechende Zelle leer. Ist dies nicht der Fall prüfst du, ob das Datum einem Quartalsende entspricht. Ist dies der Fall bleibt die Zelle leer. Ist dies nicht der Fall wird ein Tag drauf gerechnet.

Für die Zellen C96 bis C100 müssen die oben stehenden Formeln entsprechend angepasst werden.
Ich hoffe, dass ich dir hiermit helfen konnte.

MfG
M.Winter

 

Hallo,

ich habe in der beiliegenden Exceldatei in Zelle C3 die Formel =A1-WOCHENTAG(A1;2)+1 eingegeben
und ab Zelle C4 die Formel =C3+1, die ich hinunterkopiert habe bis Zelle C100.

Das Ausblenden der dann entstehenden überzähligen Tage erreiche ich durch bedingte Formatierung:
1) Alle Tage im Bereich C3:C8, die nicht den gleichen Monat haben wie Zelle C9, werden weggeblendet;
2) Alle Tage im Bereich C94:C100, die nicht den gleichen Monat haben wie Zelle C93, werden ebenfalls weggeblendet.

 

Was soll ich sagen, genial! Vielen Dank für eure schnelle Hilfe, hätte ich so nicht hingekriegt. Ich habe dabei wieder sehr viel gelernt. Vielleicht muss ich euer Wissen/Hilfe später nochmal in Anspruch nehmen, aber bisher sieht es ganz gut aus.