Beispiele für Verschachtelte WENN Formeln: http://www.kurtzkurse.de/excel-wenn-funktion-verschachtelt.htm
In diversen antiquarischen Shopware Shops die ich pflege war es notwendig, die relevanten Geburtstage zu jeder Tages-, Wochen-, 2Wochen-, Monats-Zeitungen, Zeitschriften und Magazinen auf der Produktseite anzugeben, ausgehend von Erscheinungsdatum.
Das Erscheinungdatum (editierbare, daher grün) habe ich per Formeln in Tag Monat und Jahr aufgeteilt um sie einzeln abfragen zu können.
Tag: =LINKS(C2;2)
Monat: =RECHTS(LINKS(C2;5);2)
Jahr: =RECHTS(C2;4)
Zusätzlich gibt es eine Spalte um zu definieren um was für eine Zeitschrift es sich handelt.
Herausgekommen ist die unten aufgeführte Formel die zusätzlich je nach Monat auch die jeweiligen Tage berücksichtigt.
=WENN(J2="Tag";"Tag";WENN(J2="Woche";TEXT(DATUM(I2;F2;D2);"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2);"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"T.M.JJJJ");WENN(J2="2Wochen";TEXT(DATUM(I2;F2;D2);"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2);"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"T.M.JJJJ");WENN(UND(J2="Monat";(ODER(G2=1;G2=3;G2=5;G2=7;G2=8;G2=10;G2=12)));TEXT(DATUM(I2;F2;D2);"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2);"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+15;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+15;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+16;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+16;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+17;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+17;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+18;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+18;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+19;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+19;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+20;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+20;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+21;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+21;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+22;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+22;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+23;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+23;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+24;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+24;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+25;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+25;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+26;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+26;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+27;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+27;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+28;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+28;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+29;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+29;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+30;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+30;"T.M.JJJJ");WENN(UND(J2="Monat";ODER(G2=4;G2=6;G2=8;G2=9;G2=8;G2=11));TEXT(DATUM(I2;F2;D2);"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2);"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+15;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+15;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+16;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+16;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+17;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+17;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+18;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+18;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+19;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+19;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+20;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+20;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+21;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+21;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+22;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+22;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+23;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+23;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+24;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+24;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+25;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+25;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+26;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+26;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+27;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+27;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+28;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+28;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+29;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+29;"T.M.JJJJ");WENN(UND(J2="Monat";G2=2);TEXT(DATUM(I2;F2;D2);"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2);"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+1;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+2;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+3;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+4;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+5;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+6;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+7;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+8;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+9;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+10;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+11;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+12;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+13;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+14;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+15;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+15;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+16;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+16;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+17;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+17;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+18;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+18;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+19;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+19;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+20;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+20;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+21;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+21;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+22;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+22;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+23;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+23;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+24;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+24;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+25;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+25;"T.M.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+26;"TT.MM.JJJJ")&", "&TEXT(DATUM(I2;F2;D2)+26;"T.M.JJJJ");"Zu viele als dass man sie hier aufzählen könnte.")))))
Sicher etwas unleserlich, aber sie funktioniert.
Hier der logische Vorläufer:
=WENN(J2="Tag";"Tag";WENN(J2="Woche";"Woche";WENN(J2="2Wochen";"2Wochen";WENN(UND(J2="Monat";(ODER(G2=1;G2=3;G2=5;G2=7;G2=8;G2=10;G2=12)));"31 Tge";WENN(UND(J2="Monat";ODER(G2=4;G2=6;G2=8;G2=9;G2=8;G2=11));"31 Tage";WENN(UND(J2="Monat";G2=2);"27 Tage";"undefiniert")))))