Excel-Tabelle mit Datumsfilter bis zu einem bestimmten Tag und Monat?

[roe]

Ich habe eine grosse Excel-Tabelle. In einer Spalte habe ich das Datum. Die Daten gehen bis ins Jahr 2010 zurück. Nun möchte ich einen Filter setzen auf dieser Spalte. Eigentlich möchte ich Jahr bis heute benutzen aber einfach über alle Jahre und nicht nur vom aktuellen Jahr

Zur Veranschaulichung:

Sämtiche Daten resp. Jahre aber immer nur bis zum 15.8. Die Daten sollen angezeigt werden. Die Daten vom 16.8. bis zum Jahresende sollen für sämtliche Jahre ausgeblendet sein:

1.1.2011 - 15.8.2011
1.1.2011 - 15.8.2011
1.1.2012 - 15.8.2012
1.1.2013 - 15.8.2013
1.1.2014 - 15.8.2014
1.1.2015 - 15.8.2015
1.1.2016 - 15.8.2016

ausgeblendet sollen folgende Daten sein:
16.8.2010 - 31.12.2010
16.8.2011 - 31.12.2011
16.8.2012 - 31.12.2012
16.8.2013 - 31.12.2013
16.8.2014 - 31.12.2014
16.8.2015 - 31.12.2015
16.8.2016 - 31.12.2016

Hat hier jemand eine gute Lösung bereit?

 

[Turakos]

Hallo roe

Eine gute Lösung ...? Eine Idee auf der Basis folgenden Ansatzes:

Erster Schritt - Bedingte Formatierung auf die Spalte mit dem Datum:
Bedingung mit einer Formel als Regel, z.B. mit der Formel =TAG(A2)<=15 angewendet auf die Datum-Spalte A als Formatierung wählen "Zelle ausfüllen" z.B. mit der Farbe rot

Zweiter Schritt - Auf die Spalten der Excel-Tabelle den Filter setzen
Menü Daten > Sortieren und Filtern > Filtern
Im Dropdown Nach Farbe filtern > Nach Zellfarbe filtern (rot)

Ergebnis - Eingeblendet sind Daten (Plural von Datum): alle Jahre, alle Monate und die Tage 1 bis 15

Voraussetzung in der Spalte A (Datumspalte der Excel-Tabelle) korrektes Excel-Datum z.B. formatiert in TT.MM.JJJJ

Idee ausgetestet in Excel 2013

 

[weer]

Spezialfilter

Grüezi roe

Es gibt auch eine interessante Lösungsmöglichkeit mit dem erweiterten Filter, die dafür wie geschaffen ist. In meinem Beispiel enthält Spalte A die Datümer (mein Plural für Datum). Die Spaltenüberschrift heisst Datum. Im dreispaltigen Beispiel füge ich im Kriterienbereich ein zusätzliches Filterkriterium ein - hier in D2 - mit der Formel:
=UND(MONAT(Datum)<8;TAG(Datum)<16)

Durch die Fehlermeldung #NAME? darf man sich nicht irritieren lassen.

Ich hoffe, das hilft Dir. Grüsse Niclaus

 

[Turakos]

Hallo roe und weer

Die Beschreibung des Problems las ich noch einmal genau:

Die Formel für die Bedingte Formatierung heisst korrekt =UND(MONAT(A2)<8;TAG(A2)<=15)

 

[roe]

Besten Dank

Habs gleich ausprobiert.

Irgenwie klappt es bei mir noch nicht mit der bedingten Formatierung

Ich habe im Feld F12 das Datum 28.01.2010. Eigentlich sollte doch dieses Feld formatiert werden mit der Formel "=UND(MONAT($F$12)<8;TAG($F$12)<15)". Dies ist aber nicht der Fall

Wenn ich das Datum im Feld F12 ersetze mit dem Datum "04.01.2010" dann wird dieses formatiert

Es scheint mir dass trotz der "und" Bedingung nur der Tag berücksichtigt wird. Hat jemand eine Idee, aus meiner Sicht sieht doch die Syntax ok aus.

 

[weer]

KORREKTUR

Hallo roe

Vertrauen ist gut, Kontrolle ist besser!

Turakos und ich haben beide einen Fehler gemacht mit der Formel:
=UND(MONAT(A2)<8;TAG(A2)<=15)

Der 28.01.2010 sollte ja in der gefilterten Liste erscheinen, also WAHR sein. Wenn ich die obige Formel anwende, wird er als FALSCH ausgegeben. Er erscheint also in der Liste nicht. Die Rechnerei mit den Datümern ist immer etwas diffizil, weil Excel ja jedes Datum in einen Wert umsetzt, beginnend mit dem 1. 1. 1900. Und der 28.01.2010 wird zu 40206. Und womit soll diese Zahl verglichen werden?

Es gibt zwei Lösungsmöglichkeiten:

Man macht aus jedem Datum ein Datum mit dem Jahr 1900. Der 28.01.2010 würde dann zum 28.01.1900 und ist kleiner oder gleich 15.08.1900.

Oder man macht die Prüfung so:

=MONAT(Datum)*100+TAG(Datum)<=815

Mit dieser "0815"-Formel sollte es klappen.
Grüsse Niclaus

 

[Turakos]

Hallo zäme

Lösungsvorschlag auf Basis der bedingten Formatierung mit Filtern nach einer Zellfarbe in meiner Beilage ist die Zellfarbe weiss gewählt.

Für die bedingte Formatierung sind zwei Regeln mit Formeln enthalten beide Regeln haben die Zellfarbe weiss als Format.

=UND(MONAT(A1)=8;TAG(A1)<=15)
=MONAT(A1)<8

Vereinfachung nur 1 Regel, dazu die Formel:

=ODER(MONAT(A1)<8;UND(MONAT(A1)=8;TAG(A1)<=15))

 

[roe]

Danke, wird immer besser.

Habe nun diese Formel angewendet:
=ODER(MONAT(A1)<8;UND(MONAT(A1)=8;TAG(A1)<=15))

Sieht nun wie gewünscht aus. Mit einer Ausnahme; es erscheint immer noch der 31.12. des jeweiligen Jahres. Wieso wird dieser nicht rausgefiltert?

 

[Turakos]

Hallo roe

Als Beilage die Test-Arbeitsmappe mit der Zellfarbe bei WAHR hellorange, wegen der besseren Erkennbarkeit.

Im Testfile wird der 31.12.JJJJ nicht eingeblendet, es sollte nicht schwierig sein meine Festlegungen nachzubauen.

 

[roe]

es funktionert nun. Hatte wohl irgendwo noch einen Fehler drin.

Besten Dank!

 

[roe]

Ist wohl doch noch komplizierter als erwartet...

Nun habe ich nämlich festgestellt, dass die Summenfelder der nun ausgeblendeten Zeilen (Zeilen in welcher das Datum kleiner als der 15.8.) trotzdem mitgerechnet werden (in der Pivot-Tabelle)

Wie kriege ich es hin dass die Pivot-Tabelle die ausgeblendeten Summenfelder nicht mitrechnet. Normalerweise kann ich dies mit "Teilergebnis" abfragen. Dies kann ich aber in den Optionen der Pivot-Tabelle nicht finden. Dort hat es nur "Summe"

Sorry, dass ich bisher nichts von der Pivot-Tabelle erzählt habe. Hätte ich mit dem Folgeproblem gerechnet hätte ich dies natürlich erwähnt.

Falls ich es nicht hinkriege müsste ich den Filter wohl in der Pivot-Tabelle setzen und nicht in der Quell-Tabelle. Hier stehe ich aber dann wieder vor demselben Problem, d.h. wie zeige ich sämtiche Jahre bis zum 15.8. an

 

[weer]

Pivot-Filter

Hallo roe

Ich kenne keine Möglichkeit, in Pivot ähnlich zu filtern, wie es bei Deinem "Problem" in Excel möglich ist. "Teilergebnis" in Pivot bedeutet etwas anderes als die Formel TEILERGEBNIS() in Excel. Vielleicht geht das mit PowerPivot, aber da kenne ich mich nicht aus.

Ich würde das so lösen: In der Excel-Tabelle füge ich eine Hilfsspalte ein, in der die Zahlenwerte für die Datümer > 15.08. mit 0.00 ausgewiesen werden. Siehe meine angehängte Excel-Datei.

In der Pivot-Tabelle arbeite ich mit dieser Hilfsspalte und filtere dort die 0-Werte aus. – Falls Du nicht weisst, wie man in Pivot filtert: Es gibt zwei Möglichkeiten.

Variante 1: Tabelle PivotA
Rechtsklick auf A4 / Filter / Wertefilter
Summe von HiSp ist grösser als 0

Variante 2: Tabelle PivotB
in B2 eingeben: >0
Rechtsklick auf B2 / Filter / Nach dem Wert der ausgewählten Zelle filtern.

Ich hoffe, das hilft Dir, und grüsse
Niclaus

 

[weer]

Farbfilter in Pivot und überhaupt

Hallo roe und Turakos

Ich bin noch einmal hinter die Farb-Filterung gegangen. Man kann in der Pivot-Tabelle die Datümer ebenfalls direkt (ohne Hilfsspalte) nach Farben filtern. Aber bei mir haben sich Fehler eingeschlichen: Es erscheinen z. B. auch 26.08.2013, 24.08.2014, 26.08.2016. Ich habe im Netz einen (älteren) Hinweis gefunden, der sich mit dem gleichen Problem beschäftigt, wofür es scheinbar keine Lösung gibt und wo ein Bug vermutet wird.
http://answers.microsoft.com/de-de/...g-fehler/210c04d2-0382-e011-9b4b-68b599b31bf5

Das erklärt vielleicht auch die Notiz von roe vom 11.09.2016 um 12:50 h:

es erscheint immer noch der 31.12. des jeweiligen Jahres.

Grüsse Niclaus

 

[Turakos]

Grüezi weer und roe

Vielen Dank für die Informationen von "weer". Die Threads der Beantworter der Frage "Datumfilter" zeigt den Aufwand der erbracht werden musste, zu unvollständigen Angaben des Fragenden:

Was habe ich?
> Besser wäre eine anonymisierte Musterdatei (korrekt abgebildete Datenstruktur der Arbeitsmappe).
Eine Musterdatei hilft beim Helfen! Ich selber habe keien Lust Musterdateien von einigen hundert Datensätzen nachzubauen!

Was möchte ich haben
> Eine Musterdatei, die enthalten soll wohin der Fragende will, was er im Endzustand (die Lösung) haben möchte.

Informationen wie Office-Version und ggf. Betriebssystem dürften nicht fehlen.

Den Lösungsansatz von "weer" kann ich nachvollziehen und kann so realisiert werden, falls nicht noch etwas im Verborgenen liegt.