☰

Mit HEUTE in Excel das nächste Vorkommen eines Datums innerhalb einer bestimmten Anzahl von Monaten berechnen?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Mit HEUTE in Excel das nächste Vorkommen eines Datums innerhalb einer bestimmten Anzahl von Monaten berechnen?

Zur Klarstellung: Das Rückgabedatum MUSS weniger als 24 Monate nach dem aktuellen Datum liegen, da es sich um ein Ablaufdatum handelt und die Artikel zum richtigen Zeitpunkt ablaufen müssen, sofern sie nicht erneuert werden. Anders ausgedrückt: Das Datum muss so nah wie möglich an einem 2-Jahres-Erneuerungszyklus liegen, ohne 2 Jahre zu überschreiten.

Originalbeitrag: Guten Morgen! Ich habe Google durchforstet und bin nicht weitergekommen, also hoffe ich, dass mir jemand beim Erstellen einer Formel hilft. Ich muss eine einzelne Formel erstellen, die nicht auf andere Zellen verweist, weil ich die Anzahl der Fehler anderer Benutzer begrenzen möchte :)

Was ich brauche: Finden Sie die nächste Instanz von 31.03. ODER 30.09., die mehr als 18 Monate, aber weniger als 24 Monate ab HEUTE liegt.

Basierend auf meiner obigen Formulierung habe ich das Gefühl, dass eine Kombination aus HEUTE, ODER, <, > und einem Monats-/Tagesbezeichner mir das geben würde, wonach ich suche, aber ich habe Schwierigkeiten mit der Reihenfolge der Operationen. Derzeit verwenden meine Kollegen eine Tabelle (unten), um diese Daten manuell zu berechnen, und das ist mühsam.

  • April - Sept Ungerade = März Nächste Ungerade
  • April - Sept. gerade = März nächste gerade
  • Okt. gerade - März ungerade = nächster Sept. gerade
  • Okt. ungerade - März gerade = Sept. nächste ungerade

Vielen Dank im Voraus für alle Beiträge. Ich werde dies bearbeiten, wenn mir nach der Arbeit eine grobe Formel einfällt!

Antwort1

Hier ist eine leicht verständliche, formelbasierte, nicht auf Arrays basierende Lösung, die mit Excel 2010 (und älteren Versionen) funktioniert. Sie verwendet Hilfsspalten (die ausgeblendet werden können).

Da die Anforderung von <24 Monaten und >18 Monaten nicht immer erfüllt werden kann und die harte Anforderung <24 Monate beträgt, habe ich die Anforderung am anderen Ende auf >=18 Monate gelockert.

Für jedes Datum gibt es nur drei mögliche Zieldaten: 31.03. oder 30.09. im Jahr 18 Monate nach diesem Datum oder 31.03. im darauffolgenden Jahr. Sie müssen nur das erste dieser Daten auswählen, das die Kriterien erfüllt.

Bildbeschreibung hier eingeben

Die Frage gibt die Ergebnisse basierend auf HEUTE an. Ich wollte auch zeigen, wie sich dies an anderen „Heuten“ verhält. Zelle A2 enthält =TODAY(). Die anderen Zellen in Spalte A sind nur einige andere Daten zur Veranschaulichung; insbesondere solche an den „Randdaten“ in Bezug auf den 31.3. und 30.9. Die Formeln verweisen auf die Datumszelle, aber HEUTE() könnte stattdessen fest codiert sein.

Die Spalten I:J dienen nur zur Veranschaulichung. Sie zeigen die Daten 18 und 24 Monate nach dem Datum in Spalte A, um zu verstehen, warum die Ergebniswerte ausgewählt wurden.

Die Hilfsspalten sind C:E. Diese enthalten die drei möglichen Zieldaten für das Datum in Spalte A. Ziel 1 in C2 enthält:

=DATE(YEAR(EDATE(A2,18)),3,31)

Dadurch wird das Datum 31.3. im Jahr 18 Monate nach dem Datum in Spalte A erstellt. Ziel 2 in D2 enthält:

=DATE(YEAR(EDATE(A2,18)),9,30)

Dadurch wird das Datum 30.9. im Jahr 18 Monate nach dem Datum in Spalte A erstellt. Ziel 3 in E2 enthält:

=DATE(YEAR(EDATE(A2,18))+1,3,31)

Dadurch wird das Datum 31.03. im Jahr erstellt, das 18 Monate nach dem Datum in Spalte A liegt.

Das Ergebnis steht in Spalte G. Die Formel in G2:

=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)

Aufgrund der Anforderungen ist nur ein Zieldatum zulässig. SUMPRODUCT behandelt die Array-Vergleiche mit einer normalen (nicht Array-)Formel.

C2:E2<EDATE(A2,24)Gibt für jedes Zieldatum WAHR/FALSCH (1/0) zurück, abhängig davon, ob das Datum weniger als 24 Monate nach dem Datum in Spalte A liegt.

C2:E2>=EDATE(A2,18)Gibt in ähnlicher Weise 1/0 für jedes Zieldatum zurück, basierend darauf, ob das Datum >= 18 Monate nach dem Datum in Spalte A liegt.

Nur ein Zieldatum erfüllt beide Bedingungen, daher gilt das Produkt dieser 1/0-Werte 1für dieses Datum und 0für die beiden anderen Daten. Dieses Produkt wird mit dem Wert in jeder Zieldatumszelle multipliziert. Da Daten als Zahlen gespeichert werden, ist das Ergebnis die Zahl, die das qualifizierende Zieldatum darstellt. Diese muss nur als Datum formatiert werden.

Antwort2

Die folgende benutzerdefinierte Funktion erstellt zunächst einen Kalenderzeitraum von 18 bis 24 Monaten ab heute. Anschließend durchläuft sie diesen Zeitraum, bis sie ein Datum findet, das Ihren Kriterien entspricht:

Public Function ProjDate() As Date
    Dim d1 As Date, d2 As Date, y As Long
    Dim dd As Date, d As Long, m As Long

    d = Day(Date)
    m = Month(Date)
    y = Year(Date)
    d1 = DateSerial(y, m + 18, d + 1)
    d2 = DateSerial(y, m + 24, d - 1)

    For dd = d1 To d2
        d = Day(dd)
        m = Month(dd)
        If (m = 3 And d = 31) Or (m = 9 And d = 30) Then
            ProjDate = dd
            Exit Function
        End If
    Next dd
End Function

Bildbeschreibung hier eingeben

Benutzerdefinierte Funktionen (UDFs) sind sehr einfach zuInstallierenund verwenden Sie:

  1. ALT-F11 öffnet das VBE-Fenster
  2. ALT-I ALT-M öffnet ein neues Modul
  3. Füge das Material ein und schließe das VBE-Fenster

Wenn Sie die Arbeitsmappe speichern, wird die UDF mit gespeichert. Wenn Sie eine Version von Excel verwenden, die älter als 2003 ist, müssen Sie die Datei als .xlsm und nicht als .xlsx speichern.

Zuentfernendie UDF:

  1. Öffnen Sie das VBE-Fenster wie oben
  2. Löschen Sie den Code
  3. Schließen Sie das VBE-Fenster

Zuverwendendie UDF aus Excel:

=meineFunktion(A1)

Weitere Informationen zu Makros im Allgemeinen finden Sie unter:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Und

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Einzelheiten zu UDFs finden Sie unter:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Damit dies funktioniert, müssen Makros aktiviert sein!

Antwort3

Hier ist eine Formel. In der geschriebenen Form verweist sie zu Testzwecken auf A1. Sie können sie jedoch A1durch ersetzen TODAY(), wenn Sie feststellen, dass sie die erwarteten Ergebnisse liefert:

=MAX((MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))-1

Das Obige ist einAnordnungFormel.

Da es sich um eine Matrixformel handelt, müssen Sie sie „bestätigen“, indem Sie ctrl+ gedrückt halten shiftund gleichzeitig drücken enter. Wenn Sie dies richtig machen, setzt Excel Klammern {...}um die Formel, wie in der Formelleiste zu sehen.

CSEWenn Sie die Eingabeprozedur umgehen möchten , können Sie es auch mit der etwas längeren Variante versuchen:

=AGGREGATE( 14,4,(MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}),1)-1

verwandte Informationen