Diese Aufgabe erhielt ich kürzlich: Die Außendienstmitarbeiter sollen beim Erfassen ihrer Besuchsberichte in einem Excel-Formular neben den stattgefundenen Besuchen auch die Besuchstermine vermerken, die für die kommenden 6 Kalenderwochen vorgesehen sind. Da bei der Planung die konkreten Termine noch nicht vorliegen, soll zunächst die Kalenderwoche plus Jahresangabe im Format 02/2019 erfasst werden.
So weit, so gut. Doch wie kann sichergestellt werden, dass die geplanten Termine in der richtigen Form eingeben werden, denn später sollen alle Plantermine automatisch ausgewertet werden? Und wie lässt sich die für die Außendienstler aufwändige Tipparbeit vermeiden? Ein Dropdown scheint die richtige Lösung zu sein. Aber wie muss es beschaffen sein, damit stets die kommenden 6 Kalenderwochen zur Auswahl stehen (Bild 1)? Dazu kann der Dropdownliste eine dynamisch berechnete Datenquelle zugeordnet werden.
Bild 1: Per dynamischem Dropdown fehlerhafte Kalenderwochen-Eingaben vermeiden
Die Datenquelle für die Dropdownliste einrichten
Das Ermitteln der Kalenderwochen ist kein Problem. Kniffliger wird es bei der ebenfalls erforderlichen Jahreszahl, die hinter der KW stehen soll. Die Jahreszahl des auszuwertenden Datums entspricht nicht immer dem Kalenderwochenjahr. Beispielsweise gehört der 31.12.2018 zur KW 01 des darauffolgenden Jahres oder der 1. bis 3.1.2021 gehören zur KW53 des vorhergehenden Jahres.
Die kommende Kalenderwoche und das dazugehörende Jahr korrekt ermitteln
Für die korrekte Angabe von Kalenderwochen stellt Excel seit Version 2013 die Funktion ISOKALENDERWOCHE zur Verfügung. Sie berechnet die aktuelle Kalenderwoche nach ISO-Standard. In älteren Excel-Versionen muss auf die Funktion KALENDERWOCHE mit Parameter 21 als zweitem Argument zurückgegriffen werden.
Eine Formel zur Ermittlung der kommenden Kalenderwoche im Format 02/2019 kann so aussehen:
=TEXT(ISOKALENDERWOCHE(HEUTE()+7);“00″)
&“/“&
WENN(UND(ISOKALENDERWOCHE(HEUTE()+7)=1;MONAT(HEUTE()+7)=12);JAHR(HEUTE()+7)+1;
WENN(UND(ISOKALENDERWOCHE(HEUTE()+7)>50;MONAT(HEUTE()+7)=1);JAHR(HEUTE()+7)-1;
JAHR(HEUTE()+7)))
Der erste Teil der Funktion …
ermittelt die ISO-Kalenderwoche vom heutigen Datum aus gesehen 7 Tage weiter und formatiert das Ergebnis über die Funktion TEXT auf zwei Stellen:
TEXT(ISOKALENDERWOCHE(HEUTE()+7);“00″)
Der zweite Teil der Funktion …
sorgt für den Schrägstrich zwischen Kalenderwoche und Kalenderjahr mit folgender Textverkettung:
&“/“&
Der dritte Teil der Funktion …
berechnet die korrekte Jahreszahl für die ermittelte Kalenderwoche:
WENN(UND(ISOKALENDERWOCHE(HEUTE()+7)=1;MONAT(HEUTE()+7)=12);JAHR(HEUTE()+7)+1;
WENN(UND(ISOKALENDERWOCHE(HEUTE()+7)>50;MONAT(HEUTE()+7)=1);JAHR(HEUTE()+7)-1;
JAHR(HEUTE()+7)))
Zur Erläuterung: Was ist für die Jahresermittung erforderlich?
Bei der Berechnung müssen drei Zustände unterschieden werden:
- Handelt es sich bei der ermittelten ISO-Kalenderwoche um Woche 1 und ist der Monat 12, dann liegt das Kalenderwochenjahr im Folgejahr. Er wird JAHR(HEUTE()+7)+1 gerechnet.
- Ist hingegen die ISO-Kalenderwoche größer als 50 und der Monat 1, muss das Kalenderwochenjahr ein Jahr zurück liegen. Es wird 1 subtrahiert: JAHR(HEUTE()+7)-1
- In allen anderen Fällen ist das Kalenderwochenjahr gleich dem Jahr, das aus dem vorliegenden Datum mit der Funktion JAHR berechnet wird: JAHR(HEUTE()+7)
Die kommenden 6 Kalenderwochen in einer Liste bereitstellen
Um die kommenden Kalenderwochen (seien es 6 oder auch mehr) mit EINER Formel berechnen zu können, wird eine Liste mit zwei Spalten erstellt. Die erste enthält eine aufsteigende Nummerierung von 1 bis gewünschter Anzahl Kalenderwochen, die zweite die Formel zur Kalenderwochenberechnung. Diese Formel verwendet die Nummer aus der ersten Spalte, um ein Vielfaches von 7 Tagen für die kommenden Wochen zu addieren (Bild 2[1]).
Bild 2: Die kommenden 6 Kalenderwochen per Formel in einer Liste berechnen [1] und diese dann der Dropdownliste als Datenquelle zuordnen [2]
Die Dropdownliste kann dann über Daten > Datenüberprüfung > Zulassen: Liste und Angabe der Quelle erstellt werden (Bild 2[2]).
Werden weitere Kalenderwochen benötigt, kann die Liste einfach per AutoAusfüllen erweitert werden.
Bild 3: Weitere Kalenderwochen per AutoAusfüllen einfach zur Verfügung stellen