Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

Editieren

1. Zu einem bestimmten Wochentag vor- bzw. zurückrechnen

Manchmal braucht man im Zusammenhang mit Datumsfunktionen eine Formel, die ausgehend von einem variablen Datum immer z.B. den nächsten (oder letzten) Donnerstag, Mittwoch oder sonst einen bestimmten Wochentag liefert.

1.1. Zum nächsten Montag (Dienstag, Mittwoch,....)

Da unser Ausgangsdatum variabel ist, müssten wir theoretisch 7 WENN-Fälle unterscheiden. Solche verschachtelten Funktionen sind aber schwierig zu dominieren und wenn die Formel auch noch dahingehend flexibel sein soll, dass sie auf verschiedene angestrebte Wochentage reagiert, wird das Ganze sehr unübersichtlich. Wir suchen daher nach einer mathematischen Lösung.

Wir haben:

Nun wissen wir, dass wir mit einer mathematischen Gleichung nur eine Unbekannte ermitteln können. Wir müssen also versuchen, in dieser Liste eine Unbekannte wegzubekommen. Das Ausgangsdatum ist per Definition variabel, also die Unbekannte, die uns in jedem Fall bleibt. Was aber ist mit dem Ausgangs-Wochentag? Kann man den zu einer bekannten Größe machen?
Man kann:

A

B

C

Indem wir von einem Datum den zugehörigen Wochentag abziehen, kommen wir immer zum letzten Samstag.1
Damit haben wir einen fixen Wochentag, von dem aus man nun durch Addition eines fixen Wertes zum gewünschten Wochentag kommen kann.

1

Startdatum

Der letzte Samstag

Formel in Spalte B

2

Mi 20.Dez 06

Sa 16.Dez 06

=A2-WOCHENTAG(A2)

3

Do 21.Dez 06

Sa 16.Dez 06

=A3-WOCHENTAG(A3)

4

Fr 22.Dez 06

Sa 16.Dez 06

=A4-WOCHENTAG(A4)

5

Sa 23.Dez 06

Sa 16.Dez 06

=A5-WOCHENTAG(A5)

6

So 24.Dez 06

Sa 23.Dez 06

=A6-WOCHENTAG(A6)

7

Mo 25.Dez 06

Sa 23.Dez 06

=A7-WOCHENTAG(A7)

8

Di 26.Dez 06

Sa 23.Dez 06

=A8-WOCHENTAG(A8)

Nachdem wir nun für unsere Vorwärtszählung nicht von einem beliebigen Wochentag ausgehen, sondern immer vom davorliegenden Samstag, wissen wir stets, was wir hinzuzählen müssen, um zum tatsächlich angestrebten Wochentag zu gelangen:

A

B

C

D

E

1

Startdatum

=$A2-WOCHENTAG($A2)
+7

=$A2-WOCHENTAG($A2)
+1

=$A2-WOCHENTAG($A2)
+2

=$A2-WOCHENTAG($A2)
+3

2

Do 21.Dez 06

Sa 23.Dez 06

So 17.Dez 06

Mo 18.Dez 06

Di 19.Dez 06

3

Fr 22.Dez 06

Sa 23.Dez 06

So 17.Dez 06

Mo 18.Dez 06

Di 19.Dez 06

4

Sa 23.Dez 06

Sa 23.Dez 06

So 17.Dez 06

Mo 18.Dez 06

Di 19.Dez 06

5

So 24.Dez 06

Sa 30.Dez 06

So 24.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

6

Mo 25.Dez 06

Sa 30.Dez 06

So 24.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

7

Di 26.Dez 06

Sa 30.Dez 06

So 24.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

8

Mi 27.Dez 06

Sa 30.Dez 06

So 24.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

Die Problemfälle sind in der oberen Tabelle hervorgehoben: Immer, wenn der angestrebte Wochentag mit dem Wochentag des Ausgangsdatums übereinstimmt, gelangen wir zum Ausgangsdatum anstatt zum entsprechenden Datum der kommenden Woche.

Nun könnte man versuchen, diesen Fall durch eine WENN-Abfrage in den Griff zu bekommen. Es geht aber auch eleganter.

Die Situation, die wir bisher haben ist die, dass wir maximal um 7 Tage zurückrechnen und anschließend maximal um 7 Tage vorwärts. Wir lösen das Problem, indem wir fix um 8 Tage vorwärts rechnen. Das bedeutet aber, dass wir die Formel anders aufbauen müssen.

Bisher haben wir uns einen fixen Startpunkt gesucht - immer der letzte Samstag – zu dem wir je nachdem 1, 2,...7 Tage hinzugezählt haben. Da wir jetzt fix 8 Tage hinzuzählen wollen, muss also der Startpunkt variabel werden und nicht auf den Samstag fixiert:

A

B

C

D

E

1

Startdatum

=$A2-WOCHENTAG($A2-1)
+8

=$A2-WOCHENTAG($A2-2)
+8

=$A2-WOCHENTAG($A2-3)
+8

=$A2-WOCHENTAG($A2-4)
+8

2

Do 21.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

Mi 27.Dez 06

Do 28.Dez 06

3

Fr 22.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

Mi 27.Dez 06

Do 28.Dez 06

4

Sa 23.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

Mi 27.Dez 06

Do 28.Dez 06

5

So 24.Dez 06

Mo 25.Dez 06

Di 26.Dez 06

Mi 27.Dez 06

Do 28.Dez 06

6

Mo 25.Dez 06

Mo 01.Jan 07

Di 26.Dez 06

Mi 27.Dez 06

Do 28.Dez 06

7

Di 26.Dez 06

Mo 01.Jan 07

Di 02.Jan 07

Mi 27.Dez 06

Do 28.Dez 06

8

Mi 27.Dez 06

Mo 01.Jan 07

Di 02.Jan 07

Mi 03.Jan 07

Do 28.Dez 06

1.1.1. Anwendungsbeispiele

Für die Erklärung des Formelaufbaus haben wir je nach Wochentag einen fixen Wert benutzt:
$A2-WOCHENTAG($A2-1)+8 → zum nächsten Montag
$A2-WOCHENTAG($A2-2)+8 → zum nächsten Dienstag
$A2-WOCHENTAG($A2-3)+8 → zum nächsten Mittwoch
usw.

Vielleicht ist der angestrebte Wochentag aber nicht immer der gleiche. Dann könnte man z.B. eine Zelle benutzen, in die man den jeweils zu benutzenden Wert hinterlegt, dann könnte die Formel so aussehen:
$A2-WOCHENTAG($A2-$A$1)+82

Oder man hinterlegt in dieser Zelle keine Werte, sondern die Wochentage: Mo, Di, Mi, usw. Dann müsste man eine WENN-Formel einbauen:

=WENN($A$1="Mo";1; WENN($A$1="Di";2; WENN($A$1="Mi";3; WENN($A$1="Do";4; WENN($A$1="Fr";5; WENN($A$1="Sa";6;7))))))

Die ganze Formel würde dann so aussehen:

$A2-WOCHENTAG($A2-WENN($A$1="Mo";1; WENN($A$1="Di";2; WENN($A$1="Mi";3; WENN($A$1="Do";4; WENN($A$1="Fr";5; WENN($A$1="Sa";6;7)))))))

Statt WENN-Formeln könnte man auch mit der Funktion SVERWEIS() arbeiten.

1.2. Zum letzten Montag (Dienstag, Mittwoch,....)

Im vorigen Beispiel mussten wir zuerst zurückrechnen und anschließend wieder vorwärtsrechnen, wodurch wir das Problem lösen mussten, dass die Anzahl zurückgerechneter Tage nicht gleich sein durfte wie die Anzahl vorwärtsgerechneter Tage.

Wenn wir einen zurückliegenden Wochentag suchen, haben wir es leichter:

(Datum-WOCHENTAG(Datum)) → zum letzten Samstag
(Datum-WOCHENTAG(Datum+1)) → zum letzten Freitag
(Datum-WOCHENTAG(Datum+2)) → zum letzten Donnerstag

Auch hier kann natürlich der Ausdruck +1, +2, usw. ersetzt werden durch einen Zellbezug oder eine WENN-Formel wie in den obigen Anwendungsbeispielen beschrieben.

1.3. Der erste Montag von jedem Monat des laufenden Jahres

Das folgende Beispiel geht davon aus, dass das aktuelle Jahr in einer benannten Zelle hinterlegt ist, und man sich mit der Bezeichnung AktuellesJahr darauf beziehen kann. Die Jahreszahl kann dabei direkt oder in der Form =JAHR(HEUTE()) gespeichert sein. Die hier verwendete Bezeichung AktuellesJahr kann natürlich auch durch eine Zelladresse oder eine Formel ersetzt werden.

A

B

1

Montag, 02.01.2006

DATUM(AktuellesJahr;ZEILE();8) -WOCHENTAG(DATUM(AktuellesJahr;ZEILE();6)

Erklärung:
Man geht vom Datum eine Woche nach dem Monatsersten aus und rechnet um einen Wert zurück, der sich aus dem Wochentag des Monatsersten herleiten lässt.
Für die Bestimmung des Monats wird die Zeilennummer verwendet. Entspricht diese nicht den gesuchten Monaten, muss der Wert für ZEILE() entsprechend angepasst werden.

2

Montag, 06.02.2006

3

Montag, 06.03.2006

4

Montag, 03.04.2006

5

Montag, 08.05.2006

6

Montag, 05.06.2006

7

Montag, 03.07.2006

8

Montag, 07.08.2006

9

Montag, 04.09.2006

10

Montag, 02.10.2006

11

Montag, 06.11.2006

12

Montag, 04.12.2006

Der jeweils erste Dienstag:
=DATUM(AktuellesJahr;ZEILE();8)-WOCHENTAG(DATUM(AktuellesJahr;ZEILE();5)

Der jeweils erste Mittwoch:
=DATUM(AktuellesJahr;ZEILE();8)-WOCHENTAG(DATUM(AktuellesJahr;ZEILE();4)

Der jeweils erste Donnerstag:
=DATUM(AktuellesJahr;ZEILE();8)-WOCHENTAG(DATUM(AktuellesJahr;ZEILE();3)

Der jeweils erste Freitag:
=DATUM(AktuellesJahr;ZEILE();8)-WOCHENTAG(DATUM(AktuellesJahr;ZEILE();2)

Der jeweils erste Samstag:
=DATUM(AktuellesJahr;ZEILE();8)-WOCHENTAG(DATUM(AktuellesJahr;ZEILE();8)

Der jeweils erste Sonntag:
=DATUM(AktuellesJahr;ZEILE();8)-WOCHENTAG(DATUM(AktuellesJahr;ZEILE();7)

1.4. Samstag/Sonntag auslassen

Das folgende Beispiel ergibt einen Kalender, der jeden Monat mit dem ersten Montag dieses Monats beginnt und nur die Wochentage berücksichtigt.

Um den jeweils ersten Montag pro Monat zu ermitteln gehen wir wie oben beschrieben vom jeweils 8. des Monats aus und rechnen zurück. Dieses Datum – der 8. des Monats – verwenden wir gleich als Spaltentitel und ordnen den Zellen in Zeile 1 das Zahlenformat: MMMM JJJJ zu.

Im Beispiel steht in Spalte 1 der Januar, in Spalte 2 der Februar, etc. Also können wir die Spaltennummer benutzen, um den jeweiligen Monat festzulegen. Wir gehen davon aus, dass es um das aktuelle Jahr gehen soll, daher definiert sich das Jahr mit der Formel =JAHR(HEUTE()). Die ganze Formel in allen Zellen der ersten Zeile lautet also:
=DATUM(JAHR(HEUTE());SPALTE();8)

A

B

C

D

E

1

Januar 2006

Februar 2006

März 2006

April 2006

Mai 2006

2

Mo, 02.01.06

Mo, 06.02.06

Mo, 06.03.06

Mo, 03.04.06

Mo, 01.05.06

3

Di, 03.01.06

Di, 07.02.06

Di, 07.03.06

Di, 04.04.06

Di, 02.05.06

4

Mi, 04.01.06

Mi, 08.02.06

Mi, 08.03.06

Mi, 05.04.06

Mi, 03.05.06

5

Do, 05.01.06

Do, 09.02.06

Do, 09.03.06

Do, 06.04.06

Do, 04.05.06

6

Fr, 06.01.06

Fr, 10.02.06

Fr, 10.03.06

Fr, 07.04.06

Fr, 05.05.06

7

Mo, 09.01.06

Mo, 13.02.06

Mo, 13.03.06

Mo, 10.04.06

Mo, 08.05.06

8

Di, 10.01.06

Di, 14.02.06

Di, 14.03.06

Di, 11.04.06

Di, 09.05.06

9

Mi, 11.01.06

Mi, 15.02.06

Mi, 15.03.06

Mi, 12.04.06

Mi, 10.05.06

10

Do, 12.01.06

Do, 16.02.06

Do, 16.03.06

Do, 13.04.06

Do, 11.05.06

11

Fr, 13.01.06

Fr, 17.02.06

Fr, 17.03.06

Fr, 14.04.06

Fr, 12.05.06

12

Mo, 16.01.06

Mo, 20.02.06

Mo, 20.03.06

Mo, 17.04.06

Mo, 15.05.06

Die Formel in A2 lautet:
=A1-WOCHENTAG(A1+5)
Diese Formel kann nun nach rechts kopiert werden.

In A3 muss eigentlich nur gerechnet werden: =A2+1. Dann können wir diese Formel aber nur zweimal kopieren und müssen sie am Freitag anpassen und 3 statt 1 hinzu zählen. Also erweitern wir die Formel in A3 auf:
=A2+1+WENN(WOCHENTAG(A2)=6;2) Diese Formel kann nun nach unten und rechts kopiert werden.

1.4.1. Jahreswechsel

Wir haben beim obigen Kalender die Spaltennummer benutzt, um den aktuellen Monat zu ermitteln. Wenn Sie nun denken, bei Spalte 12 sei Schluss, so irren Sie. Die Funktion DATUM() ist intelligenter als es auf den ersten Blick scheint:

Man kann den obigen Kalender getrost weiter nach rechts kopieren:

A

C

D

E

1

November 2006

Dezember 2006

Januar 2007

Februar 2007

2

Mo, 06.11.06

Mo, 04.12.06

Mo, 01.01.07

Mo, 05.02.07

Siehe auch: Datum

1.5. Der jeweils letzte Montag, Dienstag,... des Monats

Das folgende Beispiel geht davon aus, dass das laufende Jahr als Basis dienen soll und der Monat aus der Spaltennummer ermittelt wird (vgl. oben). Wenn das Basisdatum anders ermittelt wird, sind die Parameter Jahr, Monat, Tag der Funktion DATUM() entsprechend anzupassen.

Für die Ermittlung des letzten Montags (Dienstags, Mittwochs,...) des laufenden Monats gehen wir vom Folgemonat aus (daher SPALTE()+1). Wir machen uns die Tatsache zu nutze, dass ein Datum, von dem wir seine Wochentags-Nummer abziehen, uns zum davor liegenden Samstag führt (siehe oben). Wenn wir also vom ersten Tag des Folgemonats den Wochentag dieses Monatsersten abziehen, erhalten wir den letzten Samstag des laufenden Monats.

Für alle anderen gesuchten Wochentage ziehen wir vom Monatsersten des Folgemonats nicht den Wochentag des Monatsersten ab, sondern verschieben ihn gemäss folgender Tabelle:

A

B

1

Januar 2006

=DATUM(JAHR(HEUTE());SPALTE();1)

2

Sa, 28.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;1))

3

Fr, 27.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;2))

4

Do, 26.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;3))

5

Mi, 25.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;4))

6

Di, 31.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;5))

7

Mo, 30.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;6))

8

So, 29.01.06

=DATUM(JAHR(HEUTE());SPALTE()+1;1)  -WOCHENTAG(DATUM(JAHR(HEUTE());SPALTE()+1;7))


KategorieCalc KategorieHowto

  1. Die Funktion WOCHENTAG() kann mit der Zählung je nach Wunsch am Sonntag oder Montag beginnen. In den Beispielen dieser Seite benutzen wir die Standardeinstellung, die mit der Zählung am Sonntag beginnt. Die Verwendungsmöglichkeiten der Funktionsparameter sind im Online-Hilfetext (F1) aufgeführt. (1)

  2. Wenn die Formel kopiert werden soll, unbedingt auf die richtige Verwendung der absoluten und relativen Adressen achten. (2)


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28