zurück
 Download als PDF-Datei ( 13 KB)
Hinweis zum Download:
Beim Microsoft-Internet-Explorer
den Download mit rechter Maustaste einleiten.
Im Kontextmenü dann "Ziel speichern unter..." wählen.

CVR-Seniorentreff am 26. Nov. 2003
Thema: Excel - Formel-Entwicklung zur Berechnung der lfd. Woche  

In Excel wird die Funktion KALENDERWOCHE zur Verfügung gestellt. Diese Funktion steht aber erst nach Aktivieren des Add-Ins „Analyse-Funktionen“ im Add-Ins-Manager (Menü: Extras > Add-Ins-Manager...) zur Verfügung. Ggf. ist vorher noch eine Installation von der Microsoft Office-CD erforderlich.

In dieser Microsoft-Excel-Funktion KALENDERWOCHE wird die 1.Woche des Jahres immer die Woche in der sich der 1.1. des Jahres befindet. Hierzulande ist dieses jedoch nur die 1.Woche, wenn in diese Woche auch ein Werktag fällt. Sonst ist erst die nächste Woche die 1.Woche. Die Excel-Funktion KALENDERWOCHE gibt hier bereits die 2.Woche an. Aus diesem Grunde entwickeln wir hier eine Formel, die dieses Novum in Deutschland berücksichtigt. Da stets mehr als 1 Weg nach Rom führt, gibt es auch hier mehrere Lösungen, die ggf. auch kürzer oder/und einfacher sind als der hier gefundene Lösungsweg.

Zum Vergleich: In den Kalendern auf der Homepage von H.Greschner: http://hgreschner.bei.t-online.de werden die Kalenderwochen für deutsche Kalender korrekt ermittelt.

Bedingungen - Vorgehensweise:
  1. Eingabe des Datums.
  2. Feststellen des Jahres aus dem Datum.
  3. Feststellen auf welchen Wochentag der 1.1. fällt.
  4. Fällt der 1.1. auf einen Wochentag Montag bis Donnerstag, so ist dieses die 1. Woche, da diese Woche mindestens einen Werktag hat.
  5. Fällt der 1.1. auf einen Wochentag Freitag bis Sonntag, so ist die darauffolgende Woche die 1. Woche.
  6. Ermitteln der Anzahl Tage vom 31.12. des Vorjahres plus oder minus der Tage, die aus 4. oder 5. zu berücksichtigen sind, bis zum Datum.
  7. Anzahl der Tage durch 7 teilen.

Formelgewinnung: Bei diesem Beispiel wird das Datum in Zelle B2 geschrieben. Die weiteren Formeln werden dann nacheinander in B3, B4, B5 usw, jeweils eine Zeile weiter, in die selbe Spalte B geschrieben. So ist der Weg leichter zu verfolgen. In der letzten Zeile in Spalte B steht die fertige Formel.
  • Eingabe des Datums in Zelle B2 eines Excel-Tabellenblattes.
  • Zelle B3: Feststellen des Jahres: =JAHR(B2)
  • Zelle B4: Festlegen des 1.1.: =VERKETTEN("1.1.";B3), ganze Formel:
    =VERKETTEN("1.1."; JAHR(B2))
    - Der Ausdruck "B3" in Zelle B4 wird mit der Formel aus Zelle B3 ersetzt. -
  • Zelle B5: Datumswert des 1.1.: =DATWERT(B4), ganze Formel: =DATWERT(VERKETTEN("1.1."; JAHR(B2)))
  • Zelle B6: Datumswert des 31.12. des Vorjahres: =B5-1, ganze Formel: =DATWERT(VERKETTEN("1.1."; JAHR(B2)))-1
Weiter unter der Annahme: Wenn der 1.1. ein Montag ist:
  • Zelle B7: Anzahl Tage im Jahr: =B2-B6, ganze Formel:
    =B2-DATWERT(VERKETTEN("1.1."; JAHR(B2)))-1
  • Zelle B8: Differenz in Wochen: =B7/7, ganze Formel:
    =(B2-DATWERT(VERKETTEN("1.1."; JAHR(B2)))-1)/7 Ergebnis für den 6.Tag in der 1.Woche: 0,857.....
  • Zelle B9: Um auf eine glatte 1 zu kommen: =GANZZAHL(B8+0,9) , ganze Formel: =GANZZAHL((B2-DATWERT(VERKETTEN("1.1."; JAHR(B2)))-1)/7+0,9)
    • 0,9 ist kleiner als 1, würde 1 addiert wird der 7.Tag (So) in die nächste Woche gerechnet, das Ergebnis wird = 2 bei 7/7+1.
    • 0,9 ist größer als 1 - 1/7, so wird für den 1. Tag (Mo) das Ergebnis > 1.
  • Ergebnis für einen Tag in der 1.Woche: 1.
 
Korrekturformel: abhängig vom Wochentag des 1.1.:

Hinweis : Mo = 1, Di = 2, Mi = 3, Do = 4, Fr = 5, Sa = 6, So = 7

Beispiel 1: Wenn der 1.1. ein Dienstag ist (Tag 2) muss für die 1. Woche 1 Tag hinzugezogen werden – Formel Wochentag 2 – 1 = 1. Fällt der 1.1. auf einen Mittwoch (Tag 3), so fehlen 2 Tage in der 1. Woche – Formel Wochentag 3 – 1 = 2. Der Korrekturwert ist –1.
Korrekturergebnis = 0, wenn der 1.1. = Mo = Tag 1, Tag 1 + Korrektur -1 = 1-1=0 !
Korrekturergebnis = 2, wenn der 1.1. = Mi = Tag 3, Tag 3 + Korrektur -1 = 3-1=2 !

Beispiel 2: Wenn der 1.1. ein Freitag ist (Tag 5), ist dieses nicht die 1. Woche, da in diese Woche kein Arbeitstag fällt. Es sind also 3 Tage zuviel: Freitag, Samstag u. Sonntag - Formel Wochentag 5 – 8 = -3. Ist der 1.1. ein Sonntag (Tag 7), so ist 1 Tag zuviel – Formel Wochentag 7 – 8 = -1.  Der Korrekturwert ist –8.

  • Zelle B11: Wochentag des 1.1.: =WOCHENTAG(B5;2), ganze Formel: =WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)
  • Zelle B12: Korrektur: wenn der 1.1.>=Fr also >=5, dann Ergebnis =WOCHENTAG(B5;2)-8
  • Zelle B14: Korrektur: wenn der 1.1.<=Do also <=4, dann Ergebnis =WOCHENTAG(B5;2)-1
  • Zelle B16: Aus der Korrektur resultierende WENN-Formel: =WENN(WOCHENTAG(B5;2)>=5;WOCHENTAG(B5;2)-8;
    WOCHENTAG(B5;2)-1), ganze Formel:
    =WENN(WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)>=5; WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)-8; WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)-1)

Zusammengesetzte Formel aus GANZZAHL:

=GANZZAHL((B2-DATWERT(VERKETTEN("1.1."; JAHR(B2)))-1)/7+0,9)


und Korrekturformel:

=WENN(WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)>=5;
WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)-8;
WOCHENTAG(DATWERT(VERKETTEN("1.1."; JAHR(B2)));2)-1)

Ergebnis:


=GANZZAHL(
(WENN(WOCHENTAG(DATWERT(VERKETTEN("1.1.";JAHR(B2)));2)>=5;
WOCHENTAG(DATWERT(VERKETTEN("1.1.";JAHR(B2)));2)-8;
WOCHENTAG(DATWERT(VERKETTEN("1.1.";JAHR(B2)));2)-1)
+B2-(DATWERT(VERKETTEN("1.1.";JAHR(B2)))-1))/7+0,9)


Klartext – fertige Formel:

=GANZZAHL(
(WENN(WOCHENTAG(DATWERT(VERKETTEN("1.1.";JAHR(B2)));2)>=5;
WOCHENTAG(DATWERT(VERKETTEN("1.1.";JAHR(B2)));2)-8;
WOCHENTAG(DATWERT(VERKETTEN("1.1.";JAHR(B2)));2)-1)
+B2-(DATWERT(VERKETTEN("1.1.";JAHR(B2)))-1))/7+0,9)


Das Datum wird in Zelle B2 geschrieben. Die fertige Formel – siehe Klartext - kann in jeder beliebigen anderen Zelle des Blattes stehen.
Beim Kopieren der Formel sollten für B2 feste Bezüge stehen: $B$2.
nach oben