Wer jemals ver­sucht hat, mit Excel Berech­nun­gen jeglich­er Art mit kalen­darischen Dat­en vor dem 20. Jahrhun­dert durchzuführen die oder der weiß, dass Excel (Win­dows-Ver­sion) da ganz schön „zick­ig” ist
… 😎

Sie find­en zwar den einen oder anderen Tipp bei Excelformeln.de, aber da gibt es ja noch die „etwas” älteren Her­ren Gre­gor und Julius, welche in Sachen Kalen­der für einen tiefen Ein­schnitt ver­ant­wortlich waren. Nach dem Römis­chen Kaiser Julius Cäsar ist der frühere Julian­is­che Kalen­der benan­nt, nach Papst Gre­gor XIII wurde der heute in den größten Teilen der Welt gebräuch­liche Gre­go­ri­an­is­che Kalen­der benan­nt. Mehr Hin­ter­grund­wis­sen bekom­men Sie beispiel­sweise hier bei Wikipedia.

▲ nach oben …

Vorgeplänkel

Frage: Auf welchen Wochen­tag fiel der 1.1.1900? Tra­gen Sie gerne in A1 die Über­schrift Datum ein und in A2 das Datum 1.1.1900. In B1 schreiben Sie bitte Wochen­tag und auch gle­ich in C1 Wochnen­tag (Text) als Über­schriften. Excel „beant­wortet” die Frage nach dem Wochen­tag, wenn Sie in B2 diese Formel ver­wen­den: =WOCHENTAG(A2; 2). Das Ergeb­nis ist 7, da hier im europäis­chen Raum die Woche mit dem Mon­tag begin­nt. Und in C2 ver­wen­den Sie die Formel =TEXT(A2; "TTTT, T.MMMM JJJJ"). Ide­al­er­weise for­matieren Sie diese kleine Liste noch als Intel­li­gente Tabelle. Das Ganze stellt sich nun beispiel­sweise so dar:

Der 1.1.1900, ver­schieden For­matiert

Okay, der 1. Jan­u­ar 1900 war also ein Son­ntag. Markieren Sie die Zelle C2, ein Klick auf Tab und tra­gen Sie dann in A3 das Datum des 2. Jan­u­ar 1900 ein. Die bei­den Nach­barzellen wer­den automa­tisch mit den entsprechen­den Werten des Fol­ge­tages aus­ge­füllt. Und wenn Sie schon ein­mal dabei sind, lassen Sie Excel den Wochen­tag von Sil­vester 1899 berech­nen, also für den 31.12.1899:

Offen­sichtlich eine Gren­züber­schre­itung?

Aha… 😯  Und ja, ich hat­te auch schon zu Beginn ange­merkt, dass Excel nicht so ganz alleine ohne Umwege mit kalen­darischen Dat­en vor dem 20. Jahrhun­dert umge­hen kann. Als Test soll­ten Sie in A5 noch ein­mal das Datum 1.4.1899 (in dieser Schreib­weise) ein­tra­gen, auch wenn es kein Aprilscherz ist oder sein soll. 😎

Und nun treiben wir es auf die Spitze: Schreiben Sie in A6 den 28.02.1900, in A7 die Formel =A6+1 und in A8 noch =A7+1.

29. Feb­ru­ar 1900 – ???

So weit, so gut; oder vielle­icht doch so schlecht? Hier hat Excel näm­lich einem Tag, den es gar nicht gibt, als Datum gew­ertet und auch einen Wochen­tag „ver­passt”. Den 29. Feb­ru­ar 1900 gibt es näm­lich gar nicht in der Real­ität, obwohl die Jahreszahl ohne Rest durch 4 teil­bar ist, han­delt es sich keineswegs um ein Schalt­jahr. Die Begrün­dung kön­nen Sie auch im oben ver­Link­ten Wikipedia-Artikel nach­le­sen.

▲ nach oben …

Power Query ist „schlauer” 😉 

Importieren Sie diese eben erstellte Tabelle in den Pow­er Query-Edi­tor. Und irgend­wie hat sich auf den ersten Blick gar nicht so viel geän­dert:

Direkt nach dem Import beste­ht eine hohe Ähn­lichkeit

Die Spal­ten Datum und Wochen­tag (Text) habe ich händisch ver­bre­it­ert, damit die Daten­typen bess­er erkennbar und auch die kom­plet­ten Ein­träge sicht­bar sind. Ab Ver­sion Excel 2019 erledigt das übri­gens Pow­er Query alleine für Sie 🙂 .

Sie erken­nen schon an der Aus­rich­tung des Zellinhalts, dass in Datum teils kalen­darische Dat­en, teils Texte ste­hen. Und in der let­zten Spalte Wochen­tag (Text) sind es wie auch im Excel Arbeits­blatt nur Texte.

Um auch in Pow­er Query bess­er den Ver­gle­ich zum Excel-Ergeb­nis erken­nen zu kön­nen, erstellen Sie von der ersten Spalte eine Kopie. Das geht ide­al per Recht­sklick in die Über­schrift und dann Spalte duplizieren. Ich benenne die neue Spalte gle­ich um und nenne diese PQ-Datum. Recht­sklick in die Über­schrift dieser neuen Spalte, Typ ändern | Datum. Sie erken­nen rasch, dass nun alle Werte rechts­bündig sind und damit offen­sichtlich auch ein „echt­es” Datum enthal­ten.

Wech­seln Sie nun zum Menü Spalte hinzufü­gen und acht­en Sie darauf, dass auch die Spalte PQ-Datum markiert ist. In der Gruppe Aus Datum & Uhrzeit wählen Sie Datum | Tag | Name des Tags. Und pri­ma, der Wochen­tag wird aus­gegeben. Sog­ar auch für die Tage das 19. Jahrhun­derts. Und wenn Sie ein­mal nur die Beze­ich­nun­gen der Wochen­t­age der Spal­ten Wochen­tag (Text) und PQ-Datum ver­gle­ichen, wer­den Sie fest­stellen, dass alle Tages­beze­ich­nun­gen der Dat­en des 20. Jahrhun­derts gle­ich sind. Pri­ma!

Bedenken Sie, dass die Dat­en in Excel in Zeile 2 begin­nen!

„Pri­ma”, wirk­lich? Ich habe einige Absätze weit­er oben nicht ohne Grund „auf den ersten Blick” geschrieben. Um etwas mehr Licht in diese Anmerkung zu brin­gen, duplizieren Sie der Ein­fach­heit hal­ber erst ein­mal diese Abfrage. Für diese Aktion sind die Dat­en aus dem 19. Jahrhun­dert nicht rel­e­vant, diese sollen her­aus­ge­filtert wer­den.

Gehen Sie dazu den sicheren Weg über PQ-Datum und fil­tern diese Spalte nach den Datum-Werten, die größer oder gle­ich dem 1. Jan­u­ar 1900 sind. Anschließend sind nur noch 4 Datensätze/Zeilen übrig. Okay, und was kön­nte dabei auf­fäl­lig oder zu bemän­geln sein? Bei diesen weni­gen Zeilen mute ich Ihnen zu, alles, aber wirk­lich alles Zelle für Zelle nachzuprüfen und auf Übere­in­stim­mung zu kon­trol­lieren. Da soll­ten Sie auch schon fündig wer­den. – Wenn Sie als fort­geschrit­ten­er PQ-Anwen­der in ein­er großen Datei nach Ungereimtheit­en dieser Art suchen, bietet sich fol­gen­des Vorge­hen an:

• Menü Spalte hinzufü­gen | Benutzerdefinierte Spalte
• Neuer Spal­tenname: Ver­gle­ich
• Benutzerdefinierte Spal­tenformel:
  = Date.From([#"Wochentag (Text)"])=[#"PQ-Datum"]

… und Über­raschung:

Gle­ich zu Beginn 3 Mal Error, warum?

Eigentlich hätte ich ja auss­chließlich ein TRUE oder ein FALSE erwartet. Sie gewiss auch. Wenn Sie aber rechts des Wortes Error   (beispiel­sweise in Zeile 3) in den leeren Raum der Zelle Klick­en, wird Ihnen im unteren Teil des Fen­sters markant gelb hin­ter­legt die Ursache angezeigt:

Hier „liegt der Hund begraben” 😉 (deutsches Sprich­wort)

Na ja, die Ursache wird aufge­führt. Aber warum ist eine Kon­vertierung nicht möglich? Wahrschein­lich wis­sen Sie es schon, aber es muss doch auch mit PQ-Werkzeu­gen möglich sein, der Sache auf den Grund zu gehen und bei der Gele­gen­heit auch gle­ich als Ergeb­nis möglichst ein TRUE oder FALSE zu bekom­men. Gesagt, getan…

• Duplizieren Sie die Spalte Wochen­tag (Text).
• Recht­sklick in Wochen­tag (Text) – Kopie und Spalte teilen | Nach Trennze­ichen | Leerze­ichen | Beim äußer­sten linken Leerze­ichen.
• Die Spalte Wochen­tag (Text) – Kopie.1 löschen.
• Spalte hinzufü­gen | Benutzerdefinierte Spalte.
• Als Über­schrift Vergleich_2  ver­wen­den und als Formel tra­gen Sie ein:
  = [#"PQ-Datum"]=Date.From([#"Wochentag (Text) - Kopie.2"]) 
   
• OK und „na ja…”.

Auch wenn sich da ein Error „eingeschlichen” hat ist das Ergeb­nis voll in Ord­nung. Die ersten bei­den Zeilen und auch die let­zte enthal­ten den erwarteten Logik-Wert. Und Pow­er Query sagt Ihnen in Zeile 3 ganz klar, dass der 29. Feb­ru­ar 1900 ein Fehler ist. Stimmt ja auch! Darum ist das Ergeb­nis aus mein­er Sicht vol­lkom­men in Ord­nung.

▲ nach oben …

Eine wichtige Erkenntnis

Sie wis­sen, dass beim fil­tern ein­er Tabelle oder Liste der Inhalt der Zeilen an sich nicht verän­dert wird; es wer­den je nach Sit­u­a­tion mehr oder weniger Zeilen aus­ge­blendet (in Excel) oder physisch gelöscht (in Pow­er Query). Aber irgend­wie ist oder scheint das hier anders als gewohnt zu sein oder auch den Regeln entsprechend.

Wenn ich solchen Ungereimtheit­en auf den Grund gehen will, dann fange ich so gut wie immer ganz von vorne an und arbeite mich Schritt für Schritt bis zu dem Punkt vor, wo das uner­wartete Ergeb­nis zum ersten Mal auf­taucht. Für die Analyse kann es dann dur­chaus vorkom­men, dass ich den einen oder anderen Schritt in den Ablauf ein­füge. Und genau das wird auch hier geschehen.

Immer unter dem Aspekt, dass es sich ja auch um zig tausende von Zeilen han­deln kön­nte, tue ich hier so, als wenn genau das der Fall ist. Und da ich den aktuellen Sta­tus der Abfrage Tabelle1 (2) sich­ern möchte, dupliziere ich wiederum diese Abfrage. Nun klicke ich im recht­en Seit­en­fen­ster bei Angewen­dete Schritte auf den ersten Ein­trag. Okay, der erste Ein­trag ist der 31.12.1899, als Wochen­tag ist 7 (also Son­ntag) einge­tra­gen und bei Wochen­tag (Text) ste­ht Son­ntag, 1.Januar 1900. Und auch in der zweit­en Zeile find­en Sie diese Diskrepanz. Und was so richtig irri­tierend sein kön­nte: Zeile 7 hat das Datum des 1.3.1900 und dort stimmt auch der Wert in Wochen­tag (Text) mit dem in der Spalte Datum übere­in!

Um Import­fehler auszuschließen gehe ich nun erst ein­mal den Weg über Datei | Schließen & laden in… | Nur Verbindung erstellen, die Abfra­gen wer­den geschlossen und das als Quelle dienende Arbeits­blatt ist voll umfänglich sicht­bar. Hmmm, in A2 (der ersten Dat­en-Zeile) ste­ht als Datum der 01.01.1900, der berech­nete Wochen­tag 7 und das eben­falls durch Excel berech­nete Datum mit alphanu­merischem Wochen­tag. Und in PQ war das doch anders, oder? – Ich habe ein­mal das Abfrage­fen­ster so posi­tion­iert, dass die Excel-Quelle und die PQ-Abfrage nebeneinan­der auf dem Bild­schirm sicht­bar sind:

Bedenken Sie, dass die Dat­en in Excel in Zeile 2 begin­nen!

In der Edi­ti­er-Zeile der Pow­er Query-Abfrage ist klar erkennbar, dass die Daten­quelle der Inhalt ([Con­tent]) der Tabelle1 der aktuellen Arbeitsmappe (Cur­rent­Work­book) ist. Und prinzip­iell wer­den ja alle Werte 1:1 über­nom­men; alle Zellen wer­den als Wert, also unfor­matiert über­nom­men. Die berech­nete Zahl 7 aus B2 wird als numerisch­er Wert in die Abfrage Zeile 1, Spalte Wochen­tag über­nom­men, prak­tisch gle­ich wie in Wochen­tag (Text).

Bei Datum ist gewohn­heits­gemäß die Uhrzeit 00:00:00 ange­hängt wor­den, aber aus dem 1. Jan­u­ar 1900 hat PQ den 31. Dezem­ber 1899 gemacht. Sie wis­sen auch, dass in Plain Excel ein als Datum eingegeben­er Wert als Zahl gespe­ichert wird und der 1.1.1900 entspricht der 1. Und wie sieht das in Pow­er Query aus? Das kön­nen Sie ganz leicht kon­trol­lieren, indem Sie den Daten­typ der Spalte Datum auf Ganze Zahl ändern. Und siehe da, PQ hat den Wert 1 über­nom­men. Und da PQ den Fehler mit dem nicht existieren­den 29.2.1900 aus­ge­merzt hat, dif­ferieren alle kalen­darischen Dat­en vom 1.1.1900 bis zum 28.2.1900 im den Wert 1 (je nach Sichtweise +1 oder ‑1). Und alles, was vor dem 20. Jahrhun­dert liegt, kann Excel nur als Text werten und ohne Hil­f­s­mit­tel (beispiel­sweis ±400 Jahre) nicht berech­nen, Pow­er Query aber sehr wohl.

Ent­fer­nen Sie nun die eben einge­fügte Änderung des Daten­typs¿, damit wieder in der ersten Spalte kalen­darische Dat­en ste­hen. Bei großen Daten­men­gen kön­nten Sie nun noch bei Angewen­dete Schritte auf Name des Tages einge­fügt Klick­en und über Spalte hinzufü­gen | Indexs­palte | Von 1 eine durchge­hende Num­merierung gener­ieren, welche der aktuellen Zeilen­num­mer entspricht. Und wenn Sie nun im recht­en Seit­en­fen­ster auf die let­zte Zeile Klick­en wer­den Sie erken­nen, dass der 01.01.1900 tat­säch­lich der Zeile 2 des Ursprungs entspricht und nur wegen des Fil­terns nun in der ersten Daten­zeile dargestellt wird.

▲ nach oben …

… und der Wochentag?

Diese Hil­fs-Abfrage hat gewiss ihre Schuldigkeit getan. Ich würde sie aus Grün­den der Per­for­mance löschen, Sie kön­nen sie aber gerne beibehal­ten und wieder zu Tabelle1 (2) wech­seln. Excel „behauptet” ja, dass der 1. Jan­u­ar 1900 ein Son­ntag sei. Und PQ berech­net dieses Datum als Mon­tag; siehe (in der PQ-Abfrage) Spalte Datum, PQ-Datum und Name des Tages. Wer hat denn nun Recht, was die ersten „echt­en” 60 Tage des Jahres 1900 bet­rifft?

Meine Lösung ist sim­pel und sollte auch Sie überzeu­gen: Nutzen Sie die Such­mas­chine Ihrer Wahl und geben als Such­be­griff Wochen­tag 1.1.1900 ein. Und siehe da, Pow­er Query hat Recht. Es ist der Mon­tag. Kon­se­quenter­weise kön­nen Sie ja noch den 1. März 1900 per Such­mas­chine aus­geben lassen und Sie wer­den fest­stellen, dass hier eine Übere­in­stim­mung vor­liegt, wie auch in der Abfrage, wo in den Spal­ten Wochen­tag (Text) und Name des Tages iden­tis­che Werte ste­hen.

Faz­it: Wenn Sie in Plain Excel den Wochen­tag eines Datums zwis­chen dem 1.1.1900 und (ein­schließlich) dem 28.2.1900 berech­nen, dann ist das Ergeb­nis falsch. Sie müssen den fol­gen­den Wochen­tag anwen­den. Und den 29. Feb­ru­ar 1900 gibt es ja bekan­ntlich so wie soicht…

Als let­zten Schritt kön­nen Sie der Kom­plet­theit wegen im recht­en Seit­en­fen­ster die Zeile mit dem Ein­trag Gefilterte Zeilen wieder löschen und auch alle Spal­ten außer Datum und Name des Tages ent­fer­nen. Dann sehen Sie passend zum jew­eili­gen Datum den kor­rek­ten Wochen­tag, was ja auch der eigentliche Sinn der Übung ist.

▲ nach oben …