Office: 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!