Wer hat (hätte) als nächstes Geburtstag

Question

Hallo guten Tag, bräuchte eine Formel für „Wer hat (hätte) als nächstes Geburtstag“ es sollten (laut angefügter Datei) in Spalte G2 bis G6 die ersten 5 Personen berechnet werden. Sollte eine angezeigte Person verstorben sein so sollte anstatt hat     hätte ausgegeben werden. Danke

https://supportnet.de/forum/?qa=blob&qa_blobid=8755643807399763212

Answer 1

Hi Jelena,

ich kann dir höchstens eine VBA-Lösung anbieten:

Sub Geburtstage()
    Dim arrDaten()
    Dim lngZaehler As Long
    Dim lngZeile As Long
    Dim lngLetzte As Long
    lngLetzte = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim arrDaten(0 To lngLetzte - 2, 0 To 1)
    For lngZaehler = 0 To lngLetzte - 2
        arrDaten(lngZaehler, 0) = Cells(lngZaehler + 2, 1)
        If DateSerial(Year(Date), Month(Cells(lngZaehler + 2, 3)), Day(Cells(lngZaehler + 2, 3))) >= Date Then
            arrDaten(lngZaehler, 1) = DateSerial(Year(Date), Month(Cells(lngZaehler + 2, 3)), Day(Cells(lngZaehler + 2, 3)))
        Else
            arrDaten(lngZaehler, 1) = DateSerial(Year(Date) + 1, Month(Cells(lngZaehler + 2, 3)), Day(Cells(lngZaehler + 2, 3)))
        End If
    Next lngZaehler
    Range("H2").Resize(lngLetzte - 2, 2) = arrDaten()
    Range(Cells(2, 8), Cells(lngLetzte, 9)).Sort key1:=Range("I2"), order1:=xlAscending, Header:=xlNo
    For lngZaehler = 2 To 6
        lngZeile = Cells(lngZaehler, 8) + 1
        If Cells(lngZaehler, 4) = "" Then
            Cells(lngZaehler, 7) = Cells(lngZeile, 2) & " hat am " & Format(Cells(lngZaehler, 9), "ddd. dd.mmm.yyyy") & " den " & Cells(lngZeile, 5) & ". Geburtstag"
        Else
            Cells(lngZaehler, 7) = Cells(lngZeile, 2) & " hätte am " & Format(Cells(lngZaehler, 9), "ddd. dd.mmm.yyyy") & " den " & Cells(lngZeile, 5) & ". Geburtstag"
        End If
    Next lngZaehler
    Range(Cells(2, 8), Cells(lngLetzte, 9)).ClearContents
End Sub

Beachte: die Spalten H und I werden vom Code benötigit um alle Daten zu sortieren. Sie werden nach Ermittlung der relevanten 5 Geburtstage wieder geleert.

Bis später, Karin

Answer 2

Hallo Karin, gibt es auch eine Formel für diese Lösung. Danke

Answer 3

Guten Morgen,

da es ein .xls ist - und daher nur "alte" Formeln zur Verfügung stehen, ist das als Formel ziemlich lang

in z.B. H1

=MAX(A2:A6*(DATUM(JAHR(HEUTE())+WENN(MAX(DATUM(JAHR(HEUTE());MONAT(C2:C6);TAG(C2:C6)))<HEUTE();1;0);MONAT(C2:C6);TAG(C2:C6))=WENN(MAX(DATUM(JAHR(HEUTE());MONAT(C2:C6);TAG(C2:C6)))<HEUTE();MIN(DATUM(JAHR(HEUTE())+1;MONAT(C2:C6);TAG(C2:C6)));MIN(WENN(DATUM(JAHR(HEUTE());MONAT(C2:C6);TAG(C2:C6))>=HEUTE();DATUM(JAHR(HEUTE());MONAT(C2:C6);TAG(C2:C6));1000000)))))

und dann als 2 tes

=INDEX(B2:B6;H1)&WENN(INDEX(D2:D6;H1)="";" hat ";" hätte ")&"am "&TEXT(INDEX(C2:C6;H1);"TTT TT.MM.")&JAHR(HEUTE())+WENN(MAX(DATUM(JAHR(HEUTE());MONAT(C2:C6);TAG(C2:C6)))<HEUTE();1;0)&" den "& JAHR(HEUTE())+WENN(MAX(DATUM(JAHR(HEUTE());MONAT(C2:C6);TAG(C2:C6)))<HEUTE();1;0)-JAHR(INDEX(C2:C6;H1)) & ". Geburtstag"

Answer 4

Guten Morgen, die VBA-Lösung ginge gut, es sollte jedoch ein Jahr dazugerechnet werden da die Person ja zu diesem Datum um ein Jahr älter wird und ich bräuchte die Lösung in T2 bis T6 habe mich bei meiner Fragestellung mit den Spalten vertan. Im Notfall könnte diese Datei auch als .xlsm gespeichert werden. Vielen Dank

Answer 5

Das Jahr dazurechnen ist kein Problem.

Da du nun die Auflistung in einer anderen Spalte benötigst stellt sich die Frage: die anderen Spalten stimmen aber so wie in der hochgeladenen Mappe? Also Name in Spalte B, Geburtsdatum in Spalte C, Verstorben in Spalte D und Alter in Spalte E? Sind die Spalten U und V leer sodass man dort Daten eintragen und sortieren kann?

Bis später, Karin

Answer 6

Hallo Karin, die Spalten B,C,D und E stimmen. U und V sind leer. Danke

Answer 7

Hi Jelena,

der Code müsste dann so aussehen:

Sub Geburtstage()
    Dim arrDaten()
    Dim lngZaehler As Long
    Dim lngZeile As Long
    Dim lngLetzte As Long
    lngLetzte = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim arrDaten(0 To lngLetzte - 2, 0 To 1)
    For lngZaehler = 0 To lngLetzte - 2
        arrDaten(lngZaehler, 0) = Cells(lngZaehler + 2, 1)
        If DateSerial(Year(Date), Month(Cells(lngZaehler + 2, 3)), Day(Cells(lngZaehler + 2, 3))) >= Date Then
            arrDaten(lngZaehler, 1) = DateSerial(Year(Date), Month(Cells(lngZaehler + 2, 3)), Day(Cells(lngZaehler + 2, 3)))
        Else
            arrDaten(lngZaehler, 1) = DateSerial(Year(Date) + 1, Month(Cells(lngZaehler + 2, 3)), Day(Cells(lngZaehler + 2, 3)))
        End If
    Next lngZaehler
    Range("U2").Resize(lngLetzte - 2, 2) = arrDaten()
    Range(Cells(2, 21), Cells(lngLetzte, 22)).Sort key1:=Range("V2"), order1:=xlAscending, Header:=xlNo
    For lngZaehler = 2 To 6
        lngZeile = Cells(lngZaehler, 21) + 1
        If Cells(lngZaehler, 4) = "" Then
            Cells(lngZaehler, 20) = Cells(lngZeile, 2) & " hat am " & Format(Cells(lngZaehler, 22), "ddd. dd.mmm.yyyy") & " den " & Cells(lngZeile, 5) + 1 & ". Geburtstag"
        Else
            Cells(lngZaehler, 20) = Cells(lngZeile, 2) & " hätte am " & Format(Cells(lngZaehler, 22), "ddd. dd.mmm.yyyy") & " den " & Cells(lngZeile, 5) + 1 & ". Geburtstag"
        End If
    Next lngZaehler
    Range(Cells(2, 21), Cells(lngLetzte, 22)).ClearContents
End Sub

Bis später, Kairn

Answer 8

Hallo Karin, bei Musterfrau 7 müsste es heißen hätte und nicht hat. Danke

Answer 9

Hi Jelena,

stimmt, in der Codezeile für den Vergleich mit Spalte D steht die falsche Variable - ändere die Zeile wie folgt:

        If Cells(lngZeile, 4) = "" Then

Bis später, Karin

Answer 10

Hallo Karin, habe noch einen Fehler gefunden und zwar wenn ein Datum auf heute fällt wird ein Jahr zu viel angezeigt. Wäre es möglich, wenn ein Datum auf heute fällt, sollte der ganze Text in der Farbe hellblau erscheinen. Danke