Vermeiden Sie die Änderung des Jahres in Datumsangaben von Formeln

Question 1

Ansatz 1

Anstatt Start-/Enddaten in Ihren SUMIFSFunktionen fest zu codieren, generieren Sie sie mithilfe von Excel-Formeln und verweisen Sie dann auf die Zellen, die diese berechneten Datumswerte enthalten. DieDATEUndEOMONTHFunktionen können wie im Screenshot unten gezeigt verwendet werden.

Ihre SUMIFSFormel für Januar kann dann wie folgt geändert werden:

=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)

Für den Monat Januar Sheet1!B3enthält die Zelle das Startdatum des Monats und Sheet1!C3das Enddatum. Ändern Sie diese Zellbezüge dorthin, wo Sie Ihre berechneten Datumswerte ablegen. Für Februar lauten die Start-/Enddatumzellen Sheet1!B4und Sheet1!C4, für März Sheet1!B5und Sheet1!C5usw. Wenn Ihre 12 Monatsformeln SUMIFSals Bereich von 12 Zeilen mal 1 Spalte angeordnet sind, können Sie die Januar-Formel in die nächsten 11 Monate kopieren.

Die Und-Zeichen (&-Zeichen) in den Formeln verketten jeweils einen Vergleichsoperator mit dem Wert in der referenzierten Zelle, ">="&Sheet1!B3ist also in der geänderten Formel effektiv gleichbedeutend mit ">=01/01/2024"weil Sheet1!B3liefert den Datumswert 01.01.2024.

Um das Jahr zu ändern, ändern Sie einfach den Jahreswert in der entsprechenden Zelle - Zelle Sheet1!B1im Screenshot. Ihre 12 Monatsformeln SUMIFSbeziehen sich alle automatisch auf die Start-/Enddaten des neuen Jahres, sodass die Formeln nicht mehr bearbeitet werden müssen.

Mit diesem Ansatz lässt sich auch das Monatsende-Datum für Februar in Schaltjahren verwalten (wie im Screenshot in Zelle gezeigt C4).

Ansatz 2

DPIFEin alternativer Ansatz besteht darin, die Datumswerte in Monatszahlenwerte umzuwandeln, indem Sie Ihren vorhandenen Amount/DPIFBereichen einen zusätzlichen Bereich hinzufügen. Dazu verwenden Sie denMONTHfunktionieren als

=MONTH(date)

wobei dateeine Datumszelle im DPIFBereich darstellt.

Wenn Sie diese monatlichen Werte aufrufen MPIF, können Sie Formeln verwenden wie

=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
...
=SUMIFS(Amount;MPIF;12)

ohne sich überhaupt Gedanken über das Jahr machen zu müssen.

Dieser monatliche Ansatz setzt voraus, dass Ihre Datenbereiche nicht mehrere Jahre umfassen. Andernfalls werden die Daten für alle Januar-, Februar- und anderen Jahre summiert, was möglicherweise nicht das ist, was Sie möchten.

In diesem Fall besteht eine Verfeinerung der Lösung darin, den Daten in einem Bereich Jahreswerte hinzuzufügen, YPIFindem manYEARFunktion, um diese Werte zu erhalten. Sie würden dann ein zweites Kriterium zu den SUMIFSFormeln hinzufügen, um diese Jahreswerte einzuschließen, zum Beispiel

=SUMIFS(Amount;MPIF;1;YPIF;2024)

Das bedeutet natürlich, dass Sie jedes Jahr immer noch 12 Formeln ändern müssen, auch wenn die Änderungen etwas einfacher sind als die, die Sie derzeit haben. Um dies zu vermeiden, platzieren Sie den Jahreswert in einer eigenen Zelle und verweisen Sie darauf, indem Sie eine Formel wie diese verwenden: =SUMIFS(Amount;MPIF;1;YPIF;Sheet1!$B$1)

Ansatz 3

Nicht unbedingt zu empfehlen, aber der Vollständigkeit halber aufgeführt. Ändern Sie Ihre Formeln in

=SUMIFS(Amount;DPIF;">=01/01/"&Sheet1!$B$1;DPIF;"<=31/01/"&Sheet1!$B$1)
=SUMIFS(Amount;DPIF;">=01/02/"&Sheet1!$B$1;DPIF;"<=28/02/"&Sheet1!$B$1)
usw

Hierbei handelt es sich um eine einmalige Bearbeitungsaufgabe, die für die Jahre 2021, 2022 und 2023 funktioniert, jedoch dazu führt, dass der 29.02.2024 aus der Monatssumme für Februar im Jahr 2024 wegfällt, sofern niemand daran denkt, eine Änderung vorzunehmen, und die möglicherweise im Jahr 2025 Probleme verursacht, wenn sie nicht wieder zurückgeändert wird.

Answer