Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 04:37:43
Mit Word erstellte PDF als Anhang an E-Mail anhängen
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1796to1800
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
1796to1800
1796to1800
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Altersunterschied berechnen

Altersunterschied berechnen
02.12.2020 10:29:28
Christian
Hallo,
gibt es eine Möglichkeit, den Altersunterschied in Jahren und Tagen zu berechnen?
Klar kenne ich die DATEDIF Formel, aber wie ihr seht, wird es in meinem Fall etwas komlizierter.
https://www.herber.de/bbs/user/142004.xlsx
Ich würde allerdings eine Lösung bevorzugen, dir mir das Ergebnis direkt anzeigt, ich also nicht erst eine PowerQuery Abfrage aktualisieren oder ein Makro ausführen muss, da häufig neue Daten hinzukommen.
Mir schweben also eher 2 Formeln (eine für die Jahre, eine für die Tage), von mir aus auch als UDF vor.
Die optimale Lösung würde für mich aussehen wie in Spalte E und F, wo einfach die Jahre und die Tage, berechnet werden.
Danke
Christian

30
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
es gibt auch DATEDIF mit YD
02.12.2020 10:50:32
WF
=DATEDIF(B2;D2;"Y")
und
=DATEDIF(B2;D2;"YD")
In Spalten B und D stehen "normale" Datumsformatierungen.
WF
AW: es gibt auch DATEDIF mit YD
02.12.2020 11:03:36
Christian
Hallo WF,
bei mir kommt Datedif mit dieser Art Formatierung leider nicht klar.
Ich will vermeiden, von Hand die Daten in TT.MM.JJJJ zu bringen, wegen der Gefahr der Tippfehler (und weil das bei 8000 Zeilen in der Originaldatei sehr lange dauern würde).
Gruß
Christian
AW: Altersunterschied berechnen
02.12.2020 10:54:22
Daniel
Hi
DateDif wäre hier schon das richtige, aber du musst erst mal aus deinen Texten echte Datumswerte machen.
schon mal danach gesucht?
Ist es tatsächlich so, dass die Start und End-Datum in zwei unterschiedlichen Schreibweisen vorliegen?
am einfachsten währe hier per Makro folgendes zu tun:
in Spalte B per Replace/Ersetzen die Ordinal-Kenner (th, st, nd, rd) zu eleminieren und danach das Text in Spalten mit der Einstellung im 3. Schritt Format: Datum MTY auf beide Spalten anzuwenden.
geht aber nur per Marko, da nur im Makro die amerikanischen Schreibweisen für die Monatsnamen angesetzt werden.
danach könntest du normal DateDif auswerten.
Gruß Daniel
Gruß Daniel
Anzeige
AW: Altersunterschied berechnen
02.12.2020 11:09:19
Christian
Hallo Daniel,
ich vermute du hast den Level, den ich angegeben habe überlesen. Was du hier zu Makros schreibst, sind für mich böhmische Dörfer.
Aber ja, gesucht habe ich wirklich. Aber die einzige Lösung, die in die richtung geht und ich auch verstanden habe, ist die Formel
=WENNFEHLER(DATUM(RECHTS(SVERWEIS(B2;ANC!B:D;3;0);4);FINDEN(LINKS(SVERWEIS(B2;ANC!B:D;3;0);3); "xxJanFebMarAprMayJunJulAugSepOctNovDec")/3;LINKS(RECHTS(SVERWEIS(B2;ANC!B:D;3;0);8);2));"") , die ähnliche Daten wie Spalte B umwandeln könnte, würde es in Spalte B nicht die Ordinal Kenner wie du sie nennst stehen.
Gruß
Christian
Anzeige
AW: Altersunterschied berechnen
02.12.2020 11:20:04
Daniel
Hi
ja das geht schon in die richtige Richtung.
zum Umwandeln in echte Datumswerte kannst du diese Teilformel verwenden:
für Spalte B so:
=DATUM(RECHTS(B2;4);FINDEN(LINKS(B2;3);"xxJanFebMarAprMayJunJulAugSepOctNovDec")/3;TEIL(B2;FINDEN(",";B2)-4;2))
und für Spalte D musst du lediglich die "-4" durch "-2" ersetzen.
dh man sucht um die Positon des Tags zu finden nach dem Komma und geht von dort zwei Zeichen nach vorne.
wenn noch die Ordinal-Kenner dabei sind, eben 4 Zeichen (weil man davon ausgehen kann, dass da immer auch zwei Zeichen sind)
diese beiden Teilformeln kannst du dann in dein DateDif mit "y" und "yd" einsetzen.
Gruß Daniel
Anzeige
AW: Altersunterschied berechnen
02.12.2020 11:30:02
Christian
Hallo Daniel,
hab dann grad noch eine 3. Formel gemacht
DATUM(RECHTS(D1;4);FINDEN(LINKS(D1;3);"xxJanFebMarAprMayJunJulAugSepOctNovDec")/3;TEIL(D1;FINDEN(",";D1)-2;2))-DATUM(RECHTS(B1;4);FINDEN(LINKS(B1;3);"xxJanFebMarAprMayJunJulAugSepOctNovDec")/3;TEIL(B1;FINDEN(",";B1)-4;2))
um das ganze dann auch in Tagen zu haben. Scheint zu funktionieren, danke.
Aber wenn ich dich noch um eins bitten dürfte, du hast ja sicher auch meine andere Antwort vorhin mit der PQ Problematik und dem automatischen Umwandeln bei einer PQ Lösung gelesen. Gibt es diese Möglichkeit und wenn ja wie?
Danke Christian
Anzeige
wobei für den Tag aus D ....
02.12.2020 11:30:19
Rudi
...TEIL(D2;5;2) ausreicht.
Gruß
Rudi
AW: wobei für den Tag aus D ....
02.12.2020 11:33:52
Christian
Hallo Rudi,
danke für den Hinweis, habe die Formel sofort abgeändert. Funktioniert.
Christian
AW: wobei für den Tag aus D ....
02.12.2020 12:00:30
Herbert_Grom
Hallo Rudi,
und was ist an den einstelligen Tagen? ;o)=)
Servus
gib mal in eine Zelle 7, ein
02.12.2020 12:09:34
WF
was siehst Du ?
AW: gib mal in eine Zelle 7, ein
02.12.2020 12:14:24
Christian
ich habe keinen Unterschied feststellen können
JEDE Eingabe wir mit ENTER abgeschlossen
02.12.2020 12:17:53
WF
.
AW: wobei für den Tag aus D ....
02.12.2020 12:55:15
Daniel
solange vor dem Zahlenwert ein Leerzeichen steht, macht das nichts.
das Leerzeichen wird für die Umwandlung in die Zahl die die Datumsfunktion benötigt, ignoriert.
dh " 1" wird genauso in 1 gewandelt wie "1".
Problematisch würde es erst, wenn die Monate 100 oder mehr Tage hätten oder jemand das Leerzeichen zwischen Monatsnamen und Tag weglässt.
Gruß Daniel
Anzeige
kann man weiter eindampfen
02.12.2020 11:31:03
WF
=DATUM(RECHTS(B2;4);SUCHEN(TEIL(B2;2;2);"#anebarprayunulugepctovec")/2;TEIL(B2;FINDEN(",";B2)-4;2))
WF
AW: kann man weiter eindampfen
02.12.2020 11:38:08
Christian
Hallo WF,
ok, wahrscheinlich habe ich einen Fehler gemacht, den ich nicht finde, aber bei
B2= December 8th, 1974
D2= Mar 22, 2006
gibt mein Formelversuch =DATEDIF(DATUM(RECHTS(B2;4);SUCHEN(TEIL(B2;2;2);"#anebarprayunulugepctovec")/2;TEIL(B2;FINDEN(",";B2)-4;2));DATUM(RECHTS(D2;4);SUCHEN(TEIL(D2;2;2);"#anebarprayunulugepctovec")/2;TEIL(D2;FINDEN(",";D2)-4;2));"Y")
#WERT! aus.
Gruß
Christian
Fehler gefunden
02.12.2020 11:39:13
Christian
Muss am Ende natürlich -2 heißen
Die Frage ist, ob diese Verkürzung
02.12.2020 12:51:45
Daniel
wirklich sinnvoll ist.
Denn einen messbare Vorteile bringt das nicht.
Dafür ist es nicht mehr so einfach selbsterklärend (in der langen Version sieht man sofort, dass die Anfänge der Monatsnamen gemeint sind)
und solltest du das selber schreiben, gehe ich jede Wette ein, dass du für das kürzere länger brauchst.
einen Tippfehler zu finden ist in dieser Variante auch nicht so einfach.
Die Verkürzung bringt als nichts, außer mehr Aufwand.
Gruß Daniel
Anzeige
am verständlichsten also ausgeschriebene Monate
02.12.2020 12:57:45
WF
.
im Prinzip ja
02.12.2020 13:38:57
Daniel
dann halt mit Array und Vergleich, einfach über Text-Position funktionert dann natürlich nicht mehr.
Wobei die Abkürzung der Monatsnamen auf die ersten 3 Zeichen wahrscheinlich genauso bekannt sein dürften und erkannt werden die voll ausgeschriebenen Monatsnamen, so dass da in Bezug auf die Verständlichkeit kein Unterschied besteht.
AW: im Prinzip ja
02.12.2020 18:02:55
Christian
Hallo Daniel, hallo WF
interessante Diskussion zwischen euch beiden, wieder etwas gelernt.
Wäre nur bitte einer von euch noch so nett und beantwortet mir noch die offene Frage, die eine theoretische Umwandlung der Daten mit Power Query betrifft?
Danke
Christian
Anzeige
AW: im Prinzip ja
02.12.2020 18:44:57
Daniel
Mit PQ kennen ich mich nicht so aus
Außerdem hättest du in der Eingangsfrage geschrieben, dass du wegen dem notwendigen aktualisieren kein PQ willst.
Ansonsten gibst in PQ die Funktion "Typ umwandeln" da weiß ich aber nicht, ob die auch Amerikanische Formate verarbeitet oder die der Ländereinstellung.
Gruß Daniel
AW: im Prinzip ja
02.12.2020 19:07:47
Christian
Hallo Daniel,
ich gebe dir vollkommen recht. Dass PQ die Daten in Spalte D automatisch umwandelt, war mir erst aufgefallen nachdem der Beitrag hier eröffnet war. Aber, so lange sich das nicht automatisch aktualisiert werde ich trotzdem die Formellösung bevorzugen.
Die Frage war daher eher informativ gemeint, weil wenn es doch möglich ist, würde ich vielleicht bei künftigen Problemen auch eine PQ Lösung in Betracht ziehen.
Gruß Christian
Anzeige
AW: im Prinzip ja
03.12.2020 11:56:15
ChrisL
Hi Christian
In den Verbindungseigenschaften gibt es schon Optionen zur automatischen Aktualisierung:
Userbild
Du schreibst die Daten werden häufig aktualisiert. Was bedeutet häufig?
Wenn die Dateneingabe innerhalb der gleichen Datei erfolgt, könnte theoretisch auch ein VBA-Ereignis (z.B. Wechsel auf das Blatt mit dem Abfrageresultat) zur automatischen Aktualisierung genutzt werden. Aber grundsätzlich würde ich mich schon eher auf die im Screen-Shot gezeigten Standardoptionen konzentrieren und wenn dies nicht reicht, dann würde ich vermutlich im vorliegenden Fall auch die Formellösung vorziehen.
cu
Chris
Anzeige
AW: im Prinzip ja
03.12.2020 11:56:52
ChrisL
aus Versehen offen markiert
AW: Altersunterschied berechnen
02.12.2020 11:20:51
Christian
ich weiß ja auch dass ich problemlos die Spalte D mit Powerquery in TT.MM.JJJJ umwandeln könnte und nebendran in Spalte E das Ergebnis schreiben, habe aber keine Ahnung wie ich es dann hinbekomme, dass das umgewandelte Datum automatisch erscheint, sobald ich ein englisches Datum in Spalte D einfüge.
AW: Altersunterschied berechnen
02.12.2020 11:22:54
worti
Hallo Christian,
hier mal die beiden Formel, mit denen du aus B2 bzw D2 ein Datum machen kannst.
Teils entliehen bei WF(excelformeln) und ein wenig erweitert:
=DATUM(RECHTS(B2;4);VERGLEICH(TEIL(B2;1;SUCHEN(" ";B2;1)-1);{"Januar";"February";"March";"April"; "May";"June";"July";"August";"September";"October";"November";"December"};0);TEIL(B2;SUCHEN(" ";B2;1) +1;1+ISTZAHL(TEIL(B2;SUCHEN(" ";B2;1)+2;1)*1)*1))
und
=DATUM(RECHTS(D2;4);VERGLEICH(LINKS(D2;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep"; "Oct";"Nov";"Dec"};0);TEIL(D2;SUCHEN(" ";D2;1)+1;1+ISTZAHL(TEIL(D2;SUCHEN(" ";D2;1)+2;1)*1)*1))
Noch ein Datedif als Schleifchen drum herum, fertig.
Gruß Worti
AW: Altersunterschied berechnen
02.12.2020 11:31:37
Christian
Hallo Worti,
ist jetzt anscheinend ein anderer Ansatz als Daniels Vorschlag, aber scheint auch zu funktionieren. Vielen Dank
Christian
AW: Altersunterschied berechnen
02.12.2020 11:47:50
Herbert_Grom
Hallo Christian,
da du erst einmal deine Datumsangaben in echtes Datum umwandeln musst, habe ich es mit ein paar Hilfsspalten gelöst:
https://www.herber.de/bbs/user/142008.xlsx
Servus
AW: Altersunterschied berechnen
02.12.2020 12:16:47
Christian
Hallo Herbert,
sorry aber da kann etwas nicht stimmen. Zeile 4, 21 Jahre, 415 Tage kann rechnerisch nicht sein.
Gruß
Christian
AW: Altersunterschied berechnen
03.12.2020 10:02:40
Herbert_Grom
Hallo Christian,
hast du jetzt eine Lösung für deine Frage? Wenn ja, dann poste sie doch mal!
Servus

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige