Olga hält die Bücher für eine Privatschule. Für jeden eingeschriebenen Schüler hat sie ein Eintrittsdatum und ein Abgabedatum. Sie muss herausfinden, wie viele Monate jeder Schüler abrechnen muss. Wenn der Schüler mindestens fünf Tage im Unterricht war, sollte dieser Monat in die Abrechnung einbezogen werden. Wenn weniger als fünf Tage, werden sie für diesen Monat nicht in Rechnung gestellt. Sie muss auch Feiertage und Wochenenden ausschließen.

Ein wesentlicher Bestandteil jeder Lösung dieses Problems ist die Verwendung der NETWORKDAYS-Funktion. Diese Funktion berechnet, wie in anderen ExcelTips beschrieben, die Nettozahl der Arbeitstage zwischen zwei Daten. Es berücksichtigt Wochenenden und optional Feiertage.

Angenommen, Sie haben das Eintrittsdatum des Schülers in A1 und das Abgabedatum in A2. Sie müssen lediglich eine Liste der Feiertage erstellen. Sie können dies tun, indem Sie einfach beginnen, die Feiertage in einer Reihe von Zellen einzugeben. Geben Sie ein Datum pro Zelle ein und wählen Sie dann den Bereich aus. Definieren Sie einen Namen, der auf den Bereich verweist, z. B. MyHolidays.

Sie können dann eine Formel wie die folgende verwenden:

=(NETWORKDAYS(A1, DATE(YEAR(A1), MONTH(A1) + 1,0), MyHolidays) >= 5)

+ (NETWORKDAYS(DATE(YEAR(A2), MONTH(A2),1), A2, MyHolidays) >= 5)

+ DATEDIF(DATE(YEAR(A1), MONTH(A1) + 1, 1), DATE(YEAR(A2), MONTH(A2), 1), "m")

Die Formel ist ziemlich lang und muss untersucht werden. Beachten Sie, dass neben der Funktion NETWORKDAYS auch die Funktion DATEDIF verwendet wird, mit der die Differenz zwischen zwei Daten ermittelt und das Intervall auf unterschiedliche Weise zurückgegeben wird. In diesem Fall wird es mit dem „m“

verwendet Parameter, dh das Intervall wird als Anzahl von Monaten zurückgegeben – genau das, was Olga benötigt.

Der erste Teil der Formel (die erste Verwendung der NETWORKDAYS-Funktion) wird verwendet, um zu bestimmen, wie viele Tage zwischen dem Eingabedatum (in Zelle A1) und dem Ende des Monats liegen, in dem das Eingabedatum liegt. Wenn dieser Wert größer oder gleich 5 ist (Olgas Cutoff), wird der Wert 1 zurückgegeben, da dies als ein einzelner abrechnungsfähiger Monat gilt.

Der nächste Teil der Formel (die zweite Verwendung der NETWORKDAYS-Funktion) wird verwendet, um zu bestimmen, ob der Monat, in dem das Abgabedatum liegt, mindestens fünf Klassentage enthält. Wenn ja, wird der Wert 1 erneut zurückgegeben, da dies ein abrechnungsfähiger Monat ist.

Die DATEDIF-Funktion wird dann verwendet, um die Anzahl der vollen Monate zwischen dem Eingangsmonat und dem Abgabemonat zurückzugeben. Am Ende zählt die Anzahl der Monate, die dem Schüler in Rechnung gestellt werden sollten.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9514) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Berechnen von Monaten für Abrechnungszwecke.