Office: Kalenderwochen korrekt sortieren (insb. in einer Pivot-Tabelle)

Helfe beim Thema Kalenderwochen korrekt sortieren (insb. in einer Pivot-Tabelle) in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Einleitung Wenn man Kalenderwochen ermittelt, ist die Datumsinformation weg. Also werden die Wochen 52/53 des Vorjahres und die Woche 1 des... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von lupo1, 20. August 2017.

  1. lupo1
    lupo1 Tutorial Guru

    Kalenderwochen korrekt sortieren (insb. in einer Pivot-Tabelle)



    Einleitung


    Wenn man Kalenderwochen ermittelt, ist die Datumsinformation weg. Also werden die Wochen 52/53 des Vorjahres und die Woche 1 des Folgejahres im laufenden Jahr falsch - wenn nach KW - sortiert.

    Gruppiert man in einer Pivot-Tabelle ein Datum nach 1) Monaten und 2) darunter die KW, passiert dort das gleiche:

    DEZ
    01 falsch! Gehört ans Ende vom Dezember! Auch bei Sortierung!
    48
    49
    50
    51
    52

    JAN
    01
    02
    03
    04
    05
    52 falsch! Gehört an den Anfang vom Januar! Auch bei Sortierung!

    Problematik bei der Lösung

    Es gibt leider kein benutzerdefinierbares Zahlenformat, welches nur die beiden letzten Stellen einer größeren Zahl vor dem Vorkomma darstellt (also wie REST(A4;100) inhaltlich, aber ohne Informationsverlust der davor stehenden Stellen).

    Lösung mittels "schmutzigen Workarounds"

    A4: Datum
    B4: =TEXT(KÜRZEN((A4-2)/7)*700+KALENDERWOCHE(A4;21);"00000""____________""00")

    Für die korrekte KALENDERWOCHE auch vor xl2010 (!) siehe unten "Nachbemerkung".

    ____________ steht hier für 12 Leerzeichen, die von der Forumssoftware auf eins verringert werden.

    Die Spalte B wird rechts ausgerichtet und schmal genug gemacht, damit das Datum, welches als Seriennummer vor der Kalenderwoche steht (so dass diese richtig sortiert werden kann), nicht sichtbar ist. Das kombinierte "Datum_KW"

    40901____________52

    (40901 ist der 24.12.2011, welches der Samstag vor dem Montag der ersten Woche ist, deren Ende (Sonntag) in 2012 ist)

    wird somit "nach vorn hin versteckt" (hier im Sinne einer "Animation", damit man das Versteckspiel mitbekommt):

    40901____________52
    0901____________52
    901____________52
    01____________52
    1____________52
    ____________52
    ___________52
    __________52
    _________52
    ________52
    _______52
    ______52
    _____52
    ____52
    ___52
    __52
    _52
    52


    Nun kann man das Gleiche in der Pivottabelle unternehmen. Die Kalenderwochen erscheinen korrekt sortiert. Pivottabellenformat-Voraussetzung: "Tabellenformat" - "Alle Elementnamen" - und der Monat in der Vorspalte!

    Warum? Im "Kurzformat" lässt sich die (dann) alleinige Spalte "Datumseriennummer_KW" nämlich nicht rechts ausrichten.

    Warum keine Hilfsspalte? Weil man die in der Pivottabelle dann auch benötigte! Und nichts ist schrecklicher, als dort Spalten ausblenden zu müssen.

    Nachbemerkung

    Von Franz Pölt ist folgender KALENDERWOCHE-Ersatz (vor xl2010 nötig) seit langem in Excelformeln.de gelistet:

    A1: Datum
    KW: =KÜRZEN((A1-DATUM(JAHR(A1+3-REST(A1-2;7));1;REST(A1-2;7)-9))/7)

    Datum und KW in einer Zelle (TT.MM.JJJJ hh:mm - und separat: s)

    Die Spalte A soll Datum und KW nach der Eingabe eines Datums enthalten. Für die korrekte Eingabe des Datums ist der User zuständig (keine Fehlerbehandlung). Die Makros verwenden die XLF-Nr. 7 (Franz Pölt) für Uralt-Excel und sind somit auch vor XL2010 verwendbar. Mit Strg-d schaltet man zwischen Datum und KW hin und her.

    In den Code der Tabelle kommt folgendes Ereignis:

    Private Sub Worksheet_Change(ByVal T As Range)
    If T.Column = 1 Then
    Application.EnableEvents = False
    T.Offset(0, 255).FormulaR1C1 = "=TRUNC((RC[-255]-DATE(YEAR(RC[-255]+3" & _
    "-MOD(RC[-255]-2,7)),1,MOD(RC[-255]-2,7)-9))/7)/86400+RC[-255]"
    T = T.Offset(0, 255): T.Offset(0, 255).Delete
    Application.EnableEvents = True
    End If
    End Sub

    In ein normales Modul der Mappe kommt folgender Code (mit einem Shortcut, z.B. Strg-d):

    Sub ToggleDatumSekundeAlsKW() 'Strg-d nicht vergessen!
    [A:A].NumberFormat = IIf([A1].NumberFormat = "s", "mm/dd/yyyy hh:mm", "s")
    End Sub

    Anmerkung zum Ereignis: Ich tue mich mit Target und VBA-Funktionen, die es behandeln, immer schwer; daher habe ich die unsaubere Methode einer temporären Spalte IV genommen. Darf ein VBA'ler gern anpassen!
     
    Zuletzt bearbeitet: 6. Mai 2020
Thema:

Kalenderwochen korrekt sortieren (insb. in einer Pivot-Tabelle)

Die Seite wird geladen...
  1. Kalenderwochen korrekt sortieren (insb. in einer Pivot-Tabelle) - Similar Threads - Kalenderwochen korrekt sortieren

  2. Arbeitsstunden Kalenderwoche

    in Microsoft Excel Hilfe
    Arbeitsstunden Kalenderwoche: Hey zusammen, ich bin über die Suche nicht so richtig fündig geworden. Falls ich das passende übersehen habe, dann sagt mir gerne kurz Bescheid. Folgende Thematik: Ich habe in der sehr einfach...
  3. Wert aus Kalenderwoche in eine andere Tabelle übernehmen

    in Microsoft Excel Hilfe
    Wert aus Kalenderwoche in eine andere Tabelle übernehmen: Hallo, ich habe verschiedene Tabellenblätter in einer Mappe. In dem ersten werden wöchentlich Werte eingetragen, in dem zweiten nur monatlich. Das dritte Tabellenblatt füllt sich selbständig aus....
  4. MsgBox mit Berechnung und Eintrag in Tabelle

    in Microsoft Access Hilfe
    MsgBox mit Berechnung und Eintrag in Tabelle: Moin moin liebe Formengemeinschaft. Nun bin ich schon das WWW am Durchsuchen und habe nichts passendes gefunden. Ausgangspostion: Ich habe eine Datenbank und überwache hier Daten mit einem...
  5. Ausgabe Liste Verrechnung bei Kalenderwoche

    in Microsoft Excel Hilfe
    Ausgabe Liste Verrechnung bei Kalenderwoche: Hallo zusammen, ich stehe aktuell vor einem Großen Problem und finde keinen passenden Ansatz um das meiner Liste zu Lösen. Was meine Liste Aktuell macht: Es handelt sich hierbei um eine Liste in...
  6. Kalenderwoche durch datum des freitags dieser Kalenderwoche ersetzen

    in Microsoft Excel Hilfe
    Kalenderwoche durch datum des freitags dieser Kalenderwoche ersetzen: Hallo, Ich muss in einer Tabelle , wenn in Spalte T eine Kalenderwoche steht ( zB: "KW 42"), diese durch das Datum des Freitags in selbiger Kalenderwoche ersetzen. Wenn bereits ein datum...
  7. Bedingte Formatierung Gantt ISO-Kalenderwochen

    in Microsoft Excel Hilfe
    Bedingte Formatierung Gantt ISO-Kalenderwochen: Hallo zusammen, ich möchte ein Gantt-Chart über Kalenderwochen und Jahre erstellen. Habe schon verschiedene Funktionen ausprobiert, scheitere aber stets an einer Konstellation mit der Bedingten...
  8. Jede Kalenderwoche andere Namen von einer Liste?

    in Microsoft Excel Hilfe
    Jede Kalenderwoche andere Namen von einer Liste?: Hello, ich bräuchte eine Excel Datei, in der mir jedes Jahr alle Kalenderwochen angezeigt werden und pro Kalenderwoche sollte Excel aus einem zweiten Sheet mit einer Namensliste, jede Woche 5...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden