Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1888to1892
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1888to1892
1888to1892
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

ältestes Datum jünger als 30 Jahre

ältestes Datum jünger als 30 Jahre
07.07.2022 21:20:49
Christian
https://www.herber.de/bbs/user/154052.xlsx
Hallo,
ich wäre für eure Hilfe sehr dankbar. Ich nutze Excel 2021, warum kann man das nicht auswählen?
Es geht um die Liste mit Daten in der Beispieldatei.
Ich suche das am weitesten zurückliegende Datum, welches folgende beiden Bedingungen erfüllt:
1. es liegt weniger als 30 Jahre zurück
2. Es kommt NICHT in der Tabelle vor.
Mit welcher Formel kann ich das herausfinden?
Danke
Christian
PS: Falls ihr diese Info braucht, es gibt in der Liste in D762 ein Datum das älter als 30 Jahre ist, alle anderen Daten sind jünger.

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ältestes Datum jünger als 30 Jahre
07.07.2022 22:59:56
Luschi
Hallo Christian,
bei mir klappt das so: 

=FILTER(Tabelle2[Birth Date];Tabelle2[Birth Date]
Gruß von Luschi
aus klein-Paris
AW: ältestes Datum jünger als 30 Jahre
07.07.2022 23:16:26
Christian
Hallo Luschi,
erstmal vielen Dank, nur irgendwie klappt es bei mir nicht.
Wenn ich die Formel in der Bsp Datei eingebe, gibt sie den 19.4.88 aus, welches nicht das gesuchte Datum sein kann, einmal weil es in der Tabelle vorkommt und zum anderen weil es mehr als 30 Jahre zurückliegt:
Das nächste ich hab die Formel versucht zu ändern in 

FILTER(Leute_alle[Birth Date];Leute_alle[Birth Date]
weil die Tabelle in der Originaldatei Leute_alle heißt hier gibt die Formel nur #KALK! aus mit dem Hinweis "Leere Matrix".
Schaust du bitte nochmal?
Danke
Christian
Anzeige
zur Info
07.07.2022 23:20:11
Christian
Ausgeben soll die Formel den 25.11.1992.
Das ist das älteste Datum, das weniger als 30 Jahre her ist und in der Tabelle fehlt.
Gruß
Christian
AW: zur Info
08.07.2022 05:27:44
Luschi
Hallo Christian,
mit dieser Formel: 

=MIN(FILTER(Tabelle2[Birth Date];Tabelle2[Birth Date]>EDATUM(HEUTE();-30*12)))
wird das kleinste vorhandene Datum herausgefiltert, das größer ist ist als heute (08.07.2022) vor 30 Jahren (08.07.1092).
Bei mir ist das Ergebnis: 09.07.1992.
Ein fehlendes Datum per Formel herauszubekommen sollte kaum möglich sein, mal sehen ob das Power Query kann.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: PQ-Lösung
08.07.2022 08:38:35
Christian
Hallo Luschi,
ja mit der zweiten Formel wird bei mir auch der 9.7.92 ausgegeben, allerdings sind deine beiden Formeln ja auch nicht identisch.
Werde dann mal versuchen die PQ Lösung in die Originaldatei zu übertragen (und sehe mich schon krachend scheitern). Melde mich dann wieder.
Danke auf jeden Fall schonmal für die viele Mühe. Wie ich mir das so anschaue war die PQ Abfrage sicher keine Sache von 5 Minuten. Vielen Dank auf jeden Fall.
Christian
Anzeige
kleiner Nachtrag zu FM von gestern
08.07.2022 08:42:17
gestern
ich kann es mir selbst nicht erklären, ich hab gestern einfach nur die Originaldatei gespeichert und heute morgen wieder geöffnet, ohne jegliche Änderung
jetzt gibt auch die Formel 

=FILTER(Leute_alle[Birth Date];Leute_alle[Birth Date]
etwas aus, nicht mehr wie gestern #KALK!
Ich verstehs nicht.
Testergebnis
08.07.2022 09:02:08
Christian
Hallo Luschi,
habs doch geschafft das ganze in die Originaldatei zu übertragen, ok, fast. Wenn ich den PQ Editor schließen will kann ich nur Schließen und Laden auswählen, Schließen und Laden in ist grau geschrieben also nicht auswählbar. Wollte dafür eigentlich kein komplett neues Blatt erstellen, sondern wie du auch in ein vorhandenes Blatt integrieren. Was mache ich da falsch dass ich "Schließen und Laden in" nicht auswählen kann?
Danke
Christian
Anzeige
AW: dazu ...
08.07.2022 09:31:39
neopa
Hallo Christian,
... aktiviere mit rechter Maustaste die PQ-Abdrage. und da im Menü die Funktion [Laden in ...] und da ändere zunächst in "Nur Verbindung". Anschließend aktiviere wieder mit rechter Maustaste die PQ-Abdrage. und da im Menü die Funktion [Laden in ...] und nun kannst Du die Ergebnistabelle in Dein aktuelle Arbeitsblatt einfügen.
Gruß Werner
.. , - ...
AW: dazu ...
08.07.2022 09:41:21
Christian
Hallo Werner,
das hat funktioniert. Vielen Dank.
Aber mal die Frage an dich gerichtet, gibt es da wirklich keine Formellösung, mit der ich das älteste, fehlende Datum, das weniger als 30 Jahre zurückliegt ermitteln kann oder hast du da eine Idee?
Danke
Christian
PS: Die PQ Lösung funktioniert jetzt zu 100%
Anzeige
AW: natürlich gibt es auch eine Formellösung, ...
08.07.2022 09:50:00
neopa
Hallo Christian,
... ich hab allerdings nur eine 2016er -XL Version, in der eine solche aufwendiger zu erstellen ist, als in Deiner Version und Luschi hat Dir doch auch eine Formel aufgezeigt. Da ich diese nicht prüfen kann, kann ich mich dazu nicht weiter äußern.
Gruß Werner
.. , - ...
AW: natürlich gibt es auch eine Formellösung, ...
08.07.2022 10:23:17
Christian
Hallo Werner,
ok, du kannst sie nicht prüfen, aber Luschis Formel hat ein Datum ausgegeben, dass in der Tabelle vorhanden war und ich habe ja eines gesucht, dass in der Tabelle fehlt. Dazu sagte Luschi dann, dass er sich nicht sicher sei, dass durch eine Formel ein fehlendes Datum ermittelt werden kann und hat deshalb nach einer PQ Lösung gesucht.
Christian
Anzeige
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 10:20:25
Daniel
Hi
probiers mal mit dieser Formel, Ergebnis passt zumindest.
=AGGREGAT(15;6;ZEILE(INDEX(A:A;HEUTE()):INDEX(A:A;DATUM(JAHR(HEUTE())-30;MONAT(HEUTE());TAG(HEUTE())) ))/ISTFEHLER(VERGLEICH(ZEILE(INDEX(A:A;HEUTE()):INDEX(A:A;DATUM(JAHR(HEUTE())-30;MONAT(HEUTE()); TAG(HEUTE()))));D:D;0));1)
funktioniert ab Excel 2010, sollte die Funktion SEQUENZ vorhanden sein, kann man die Zahlenfolge auch etwas einfacher stellen als über Zeile()
Gruß Daniel
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 10:28:42
Christian
Hallo Daniel,
vielen Dank,
das passt leider auch nicht ganz. In der Bsp Datei funktioniert es,
lösche ich aber alle Daten "08.07.1992" aus der Beispieldatei, gibt die Formel den 08.07.1992 aus, obwohl der 8.7.92 nicht weniger als 30 Jahre, sondern exakt 30 Jahre zurückliegt.
Gruß
Christian
Anzeige
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 10:37:27
Daniel
dann ist der Endpunkt halt nicht

DATUM(JAHR(HEUTE())-30;MONAT(HEUTE());TAG(HEUTE()))
sondern

DATUM(JAHR(HEUTE())-30;MONAT(HEUTE());TAG(HEUTE()))-1
das exakte Enddatum musst sowieso du selber festlegen.
Gruß Daniel
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 10:49:24
Christian
https://www.herber.de/bbs/user/154061.xlsx
habe jetzt alle Varianten wo ich die -1 hätte einfügen können ausprobiert und in dieser Datei alle Daten 8.7.92 gelöscht. Keine der Varianten gibt den 25.11.92 aus.
Gruß
Christian
Anzeige
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 11:02:40
Daniel
bzw in dem Fall +1
ich würde mir auch den Bereich als Namen festlegen, also:
Name: DatumsBereich
Bezieht sich auf: =ZEILE(INDEX(!$A:$A;HEUTE()):INDEX(!$A:$A;DATUM(JAHR(HEUTE())-30;MONAT(HEUTE());TAG(HEUTE()))+1))
die Formel redzuiert sich dann auf
=AGGREGAT(15;6;DatumsBereich/ISTFEHLER(VERGLEICH(DatumsBereich;D:D;0));1)
um das zu beschleunigen kannst du vielleicht als Startwert nicht Heute nehmen, sondern vielleicht den 1.1.2004, wenn du weißt dass kein Datum jünger ist.
Gruß Daniel
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 11:30:34
Christian
Hallo Daniel,
hmm hab jetzt nicht festgestellt, das die Formel ungewöhnlich lange zum berechnen braucht.
Jedoch auch wenn ich -1 durch +1 ersetze, wird immer noch nicht der 25.11.92 ausgegeben.
https://www.herber.de/bbs/user/154062.xlsx
Anzeige
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 11:43:05
Daniel
Hi
die Formel in F2 ist richitg.
Makroformelabschluss ist nicht erforderlich, das macht Aggregat automatisch.
Das Ergebnis 29.7.1992 ist für die Datei 154062.xlsx korrekt.
Dieses fehlt in der Tabelle.
Gruß Daniel
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 12:04:28
Christian
Hallo Daniel.
das kann ich mir selbst nicht wirklich erklären, weil im Original ist der 29.7.92 3x vorhanden. Ich kann mich weder erinnern wann ich diese 3 Daten gelöscht haben sollte, noch aus welchem Grund ich das getan haben sollte. Daher meine Beauptung deine Formel wäre falsch, dafür muss ich mich bei dir entschuldigen.
Auf jeden Fall funktioniert die Formel, vielen Dank. Auch dir nochmal Luschi für die Mühe mit der PQ Lösung
Christian
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 16:36:32
Luschi
Hallo Daniel,
interessante Formellösung in Verbindung von Namensmanager und Aggregat-Funktion; es brauchte bei mir ein ¼-Stündchen bis der Groschen fiel.
Gruß von Luschi
aus klein-Paris
AW: ältestes Datum jünger als 30 Jahre
08.07.2022 16:55:51
Daniel
Danke.
Ist ne einfache Schleife über alle möglichen Datumswerte, und die die in der Liste vorhanden sind, nimmt man raus. Vom dem was übrigbleibt nimmt man das maximum.
Heute würde man die Datumfolge besser mit Sequenz erstellen und über Let den Namen überflüssig machen.
Gruß Daniel
AW: ältestes Datum jünger als 30 Jahre
09.07.2022 08:31:12
Luschi
Hallo Daniel,
habe mal die Lösung für E_2021/E_365 mit den neuen Möglichkeiten umgesetzt:

=LET(z;HEUTE();y;EDATUM(HEUTE();(-12*30))+1;x;SEQUENZ(z-y+1;1;y;1);AGGREGAT(15;6; x/ISTFEHLER(VERGLEICH(x;tab_GebDat;0));1))
oder

=LET(z;HEUTE();y;EDATUM(HEUTE();(-12*30))+1;x;SEQUENZ(z-y+1;1;y;1);w;AGGREGAT(15;6; x/ISTFEHLER(VERGLEICH(x;tab_GebDat;0));1);w)
Mit meinem kostenlosen Excel-Web-Konto ist das Erstellen solcher Formeln schon ganz ein Krampf, da bei jedem Formel-Eingabefehler die Formel einfach in 1 Text umgewandelt wird ohne jegliche Unterstützung.
Aber die grundlegende Idee, wie man fehlende Datumsangaben ermittelt muß man schon wissen oder sich Ergoogeln.
Schau doch mal, ob mein Formel-Konstrukt noch verbessern kann.
Gruß von Luschi
aus klein-Paris
PS: Da beim Einsatz von Let die F9-Taste unbrauchbar ist, sind die neuen Möglichkeiten gar nicht so einfach anzuwenden.
AW: ältestes Datum jünger als 30 Jahre
09.07.2022 12:16:08
Christian
Hallo Luschi,
habe dann auch mal getestet an meiner Originaldatei, in der nachdem ich einige Daten gelöscht habe, der 10.7.92 das älteste vorhandene Datum ist und nach wir vor der 25.11.92 das älteste fehlende.
Beide Formeln geben den 10.7.92 aus, obwohl dieses Datum vorhanden ist (und weniger als 30 Jahre zurückliegt).
Außerdem wo ich bei diesen Formeln jetzt gar nicht mehr durchblicke, wenn ich sie in ein anderes Tabellenblatt schreiben will, warum funktioniert es auch da? In den Formeln steht doch gar kein bezug zu dem Tabellenblatt mit den Daten. Aber diese beiden Formeln sind mir sowieso zu hoch um sie auch nur ansatzweise zu verstehen.
Gruß
Christian
AW: ältestes Datum jünger als 30 Jahre
09.07.2022 15:35:05
Luschi
Hallo Christian,
in diesem Teil der Formel: VERGLEICH(x;tab_GebDat;0) steckt 'tab_GebDat' und ist der Name der definierten 'Intelligenten Tabelle' (IT), diese ist im Namensmanager hinterlegt und in der gesamten Arbeitsmappe einmalig - keine 2. IT darf diesen Namen tragen, egal in welchem Tabellenblatt.
Auch Daniels Konstrukt für Excel bis 2019 funktioniert in anderen Tabellenblättern, denn im definierten Namen 'DatumsBereich' steht Folgendes: =ZEILE(INDEX(!$A:$A;HEUTE()):INDEX(!$A:$A; ... )
- wer genau hinschaut stellt fest, daß vor der Spaltenangabe nur das '!' steht, aber kein Tabellenbezug
- der Namensmanager vervollständigt dieses Namensbezug mit dem Tabellenblattnamen,
  aus der die Formel aufgerufen wird
- das klappt aber nur im Namensmanager und nicht in der Formel des Tabellenblattes
Wenn die Formel nicht den richtigen Wert zurück gibt, ist vielleicht die automatische Berechnung ausgeschalten.
- Register Formeln - Berechnungsoptionen - Automatisch
Ansonsten stelle 1 neue Beispieldatei bereit.
Gruß von Luschi
aus klein-Paris
AW: ältestes Datum jünger als 30 Jahre
09.07.2022 17:05:01
Christian
Hallo Luschi,
hier die Datei, deine Formeln in Spalte G.
Dies sind die aktuellen Daten aus der Originaldatei. Hab der Übersicht halber die Daten sortiert.
Gruß
Christian
https://www.herber.de/bbs/user/154076.xlsx
AW: ältestes Datum jünger als 30 Jahre
09.07.2022 21:39:48
Luschi
Hallo Christian,
Du mußt natürlich auch den vergebenen Namen für die intelligenten Tabelle in der Formel anpassen:
- in der 1. Demodatei hieß er 'tab_GebDat'
- in der neuen Demodatei so 'Leute_alle'

=LET(z;HEUTE();y;EDATUM(HEUTE();(-12*30))+1;x;SEQUENZ(y+1;1;y;1);AGGREGAT(15;6;  x/ISTFEHLER(VERGLEICH(x;Leute_alle;0));1)) 


=LET(z;HEUTE();y;EDATUM(HEUTE();(-12*30))+1;x;SEQUENZ(z-y+1;1;y;1);w;AGGREGAT(15;6;  x/ISTFEHLER(VERGLEICH(x;Leute_alle;0));1);w)
und schon kommt bei beiden Formel wieder '25.11.1992' heraus. Also weg vom schematichen Kopieren kopieren von Formeln, sondern auch mal kontrollieren, welche Namen im Namensmanager tatsächlich vergeben wurden.
Gruß von Luschi
aus klein-Paris
AW: ältestes Datum jünger als 30 Jahre
09.07.2022 14:02:22
Daniel
Hi
Wenns funktioniert..
Testen kann ich auch nicht viel, da ich kein Excel 365 habe sondern auch nur 2016.
Was man hierbei prinzipiell noch optimieren könnte, wäre dass man als Startwert nicht Heute() verwendet, sondern Max(tab_Gebdat)+1.
Dies wäre immer der anzuzeigende fehlende Tag, wenn ansonsten alte Tage in der Liste vorhanden sind.
Die Schleife wäre dann kleiner und die Rechenzeit der Formel kürzer.
Gruß Daniel

312 Forumthreads zu ähnlichen Themen

Datum aus Tag, Jahr, KW auslesen
Stephan

Gibt es eine Möglichkeit das Datum auszulesen aus:
Mo = Montag
Jahr=2008
KW=1
Besten Dank
Stephan
16.08.2008 22:23:00
Thread-ID: 1002369
7 Beiträge
Anzeige
Datum in Kalenderwoche plus Jahreszahl
Karsten

Hi zusammen,
brauch hierbei Hilfe. Und zwar moechte ich gerne wissen, wie ich ein Datum in Kalenderwoche plus Jahreszahl umwandeln kann. Datum in Kalenderwoche ist ja kein Thema. Ich mache das mit dem Analysis ToolPak. Formel: =WEEKNUM(A1,2) Leider reicht mir aber die Wochenzahl nicht aus....
26.09.2008 17:11:00
Thread-ID: 1012711
4 Beiträge
Summe wenn Datum ein Jahr zurück
Roland

Moin Spezialisten,
ich habe eine Spalte, in der div. Datumangaben enthalten sind, z.B.
11.06.2004
07.03.2005
04.05.2006
16.07.2006
05.02.2006
29.01.2006
25.09.2006
11.12.2007
06.10.2007
10.08.2008
15.12.2008
Nun möchte ausgehend von einem Datu...
27.10.2008 08:57:00
Thread-ID: 1019526
6 Beiträge
Anzeige
Datum in Zahl umwandeln
P.Mackes

Hallo,
was ich machen möchte ist folgendes:
ich habe eine Zahl, die ein Datum darstellt, z.B. 20080507. Dabei handelt es sich NICHT um ein Datumsformat, sondern um eine richtige Zahl. Jetzt muss ich davon allerdings 90 Tage abziehen. Also wandle ich mit Date(left...mid...right) das gan...
27.11.2008 00:37:57
Thread-ID: 1028186
3 Beiträge
Datumswerte in Spalte richtig umwandeln
chris

Hallo zusammen,
ich habe ein Problem und würde ecuh um Hilfe bitten.
ich habe eine Exceldatei.in dieser habe ich eine Spalte in dieser stehen Datumswerte.
Nur das Problem ist manche zellen sind nicht als Datum formatiert das merke ich z.b wenn ich den Autofilre verwende sind die Dat...
02.12.2008 11:57:00
Thread-ID: 1029457
6 Beiträge
Differenz von 2 Datums in Jahre rechnen
2

Hi,
ich möchte die Differenz zweier Datums in Jahre darstellen. Erstes Datum steht in B2 (zB 10.03.2001) und zweites in C2 (zB 15.07.2007) .
Die Differenz zwischen B2 und C2 soll in C3 stehn und zwar als Kommazahl. (zB 6,75 Jahre )
Weis jemand eine Formal dafür ?
Danke Joach...
06.01.2009 00:25:51
Thread-ID: 1037149
6 Beiträge
Datum als serielle Zahl ausgeben
reiner

Hallo Leute,
nachfolgende Prozedur wandelt das angegebene Datum in eine serielle Zahl um.
Ich will die Jahreszahl nun über eine Variable eingeben; wie müsste dann der Programmcode erweitert werden damit die Umwandlung funktioniert?
Sub Datum() Dim Datum As Date Datum = "01.01.20...
15.02.2009 16:36:00
Thread-ID: 1050918
4 Beiträge
Datum Minus 5Jahre in VBA
Modolo

Hallo Zusammen
Ich arbeite innerhalb eines Makros mit Filtern, ich bräuchte jetzt noch eine Lösung wie ich dort das aktuelle Datum minus 5 Jahre eingebe.
Hat da Jemand eine Lösung für mich?
Danke
Modolo
26.02.2009 13:14:16
Thread-ID: 1054345
10 Beiträge

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
6
3
3
5
1
2
5
5
3
9
14
6
7
11
4
2
16
2
3
5
2
17
6
4
1