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. Gantt: Darstellung in Kalenderwochen unter Berücksichtigung des Jahrs

    in Microsoft Excel Hilfe
    Gantt: Darstellung in Kalenderwochen unter Berücksichtigung des Jahrs: Hallo an alle, ich stehe gerade etwas auf dem Schlauch. Mein Gantt Diagramm möchte ich in Kalenderwochen darstellen, da es bei einem Projektzeitraum vom 01.03.2021- 30.09.2022 mir in...
  3. Auswertung Kalenderwochen Jahreswechsel

    in Microsoft Excel Hilfe
    Auswertung Kalenderwochen Jahreswechsel: Hallo liebe Excel Freunde, ich habe folgenden Aufbau. Die KW können per drop out ausgewählt werden, um Datensätze in dem ausgewählten Zeitraum zu betrachten. Meine Frage ist, wie ich das ganze...
  4. Datum in Zelle als Kalenderwoche anzeigen

    in Microsoft Excel Hilfe
    Datum in Zelle als Kalenderwoche anzeigen: Hallo zusammen, gibt es eine Möglichkeit das Datum in einer Zelle als Kalenderwoche anzuzeigen ohne das eigentliche Datum zu löschen? z.B. 05.05.20 in KW 19 formatieren und bei bedarf rückgängig...
  5. Auswertung erstellen Datum/Kalenderwoche mit Gesamtwert und Filtern

    in Microsoft Excel Hilfe
    Auswertung erstellen Datum/Kalenderwoche mit Gesamtwert und Filtern: Guten Abend , ich stehe irgendwie vor einem Problem das ich trotz stundenlanger Versuche nicht gelöst kriege ….. Ich habe eine Tabelle wo mir Käufernamen , Bereiche , Verkäufer und Umsatz pro Tag...
  6. Kalenderwochen einblenden/ausblenden

    in Microsoft Excel Hilfe
    Kalenderwochen einblenden/ausblenden: Hallo liebe Leute, Ich habe folgendes Problem: Ich habe in den Spalten A-L Namen von Mitarbeitern und deren Daten usw. Ab der Spalte M habe ich Kalenderwochen und Tage Von KW1 (darunter die...
  7. KALENDERWOCHE (Funktion)

    in Microsoft Excel Tutorials
    KALENDERWOCHE (Funktion): KALENDERWOCHE (Funktion) Excel für Microsoft 365 Excel für Microsoft 365 für Mac Excel für das Web Excel 2019 Excel 2016 Excel 2019 für Mac...
  8. monatsübergreifende Kalenderwochen

    in Microsoft Excel Hilfe
    monatsübergreifende Kalenderwochen: Hallo Zusammen, sitze gerade an einem Problem in Excel und komme nicht weiter. In Tabellenblatt (Tabelle 2) sollen z.B. unter den Monaten Jan bis Dez und für die entsprechende Anlage die...

Users found this page by searching for:

  1. in pivot tabelle zeilen nach datum (kalenderwoche sortieren

    ,
  2. excel vba falsche kalenderwoche

  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