296 Aufrufe
Gefragt in Tabellenkalkulation von jelena Mitglied (750 Punkte)

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

12 Antworten

0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)

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

0 Punkte
Beantwortet von jelena Mitglied (750 Punkte)
Hallo Karin, gibt es auch eine Formel für diese Lösung. Danke
0 Punkte
Beantwortet von
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"
0 Punkte
Beantwortet von jelena Mitglied (750 Punkte)
Bearbeitet von jelena
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
0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)
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
0 Punkte
Beantwortet von jelena Mitglied (750 Punkte)
Hallo Karin, die Spalten B,C,D und E stimmen. U und V sind leer. Danke
0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)

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

0 Punkte
Beantwortet von jelena Mitglied (750 Punkte)
Hallo Karin, bei Musterfrau 7 müsste es heißen hätte und nicht hat. Danke
0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)

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

0 Punkte
Beantwortet von jelena Mitglied (750 Punkte)
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
...