[EXCEL] - Aus Kalenderwoche den Wochen ANFANG & Wochen ENDE (Datum) ermitteln.

[Terrenco]

Liebe Forumsgemeinde,

ich habe eine Excel Datei, bei welcher ich aus der Kalenderwoche (Zahl) versuche, den Wocheneanfang (Datum) wie den Beginn des Wochenendes (Datum) zu ermitteln.

Das sieht wie folgt aus:



Das gelingt mir für das Jahr 2016 gut, sobald ich die Werte [Wochenanfang] & [(Beginn)Wochenende] für das Jahr 2017 versuche zu ermitteln, fängt die KW1 des Jahr 2017 am 9. Januar an anstatt am 2. Januar an.

Basierend auf folgender Website : https://www.administrator.de/frage/kalenderwoche-anfangs-enddatum-herausfinden-164701.html
berechne ich das Datum für den Wochenanfang mit folgender Formel:

=DATUM(AA1;1;7*AA3+3-WOCHENTAG(DATUM(AA1;1;1)))

Das Datum für den Beginn des Wochenendes( Freitag) wird mit folgender Formel ermittelt:

=DATUM(AA1;1;7*AA3+7-WOCHENTAG(DATUM(AA1;1;1)))

AA1 = steht für das Jahr (2017)
AA3 = steht für die Kalenderwoche (1)

AA3+3 = ermittelt jeweils den Montag
AA3+7 = ermittelt jeweils den Freitag, da Freitag 4 Tage Später ist als Montag (3 + 4)


Kann das falsche Datum welches für den Wochenanfang und das Wochenende ab 2017 ermittelt wird, irgendwie mit folgendem Thema "ISO 8601" zusammenhängen ? (Quelle : https://www.administrator.de/frage/berechnung-kw-excel-282286.html) .

Seht ihr eine Lösung wie ich für die Kalenderwoche 1 (und fortlaufende) des Jahres 2017 die richtigen Daten für Wochenanfang und Wochenende ermitteln kann?
Anbei ein Bild für die richtigen Werte, die erscheinen sollen (quelle:: Kalenderwochen.net ... darf ich hier aufgrund der whitelist regel nicht verlinken)
)

 

[SOL Solutions]

Hallo,

Die Formel die Du dafuer hast ist soweit schon mal super. Sie hat nur einen kleinen Denkfehler. Die Funktion Wochentag gibt Dir in ihrer jetzigen Form fuer Sonntag eine 1 und keine 7 wider. Deswegen ist da der "Wochenumschwung" auch schon am Sonntag und das ist zufaellig auch der 1.1.2017.

Was du machen musst, ist die Formel so zu veraendern, dass fuer Montag eine 1 und Sonntag eine 7 rauskommt. Also: Wochentag(datum, 2)
Dann nur noch den Tagesoffset der Hauptformel einstellen und voila.

Beste Gruesse
SOL

 

[Terrenco]

Guten Morgen SOL,
irgendwie klappt das leider noch nicht so, wie ich mir das vorgestellt habe.
Meine Excel Datei ist eine simpler "Projekt-Terminplan"

Die erste Zeile gibt Auskunft über das Jahr
die zweite Zeile gibt Auskunft über den Monat
Die dritte Zeile gibt Auskunft über die Kalenderwoche
Die vierte Zeile gibt Auskunft über das Datum des Wochenanfangs & das Datum des (Beginn) Wochenende


Ab Zeile 6 folgen dann die Namen der Personen, welche von Datum X bis Datum Y, z.B. Urlaub haben.

Fürs Ermitteln des Wochenanfangs(Montag) konnte ich im 2016 folgende Formel verwenden:

=DATUM(AA1;1;7*AA3+3-WOCHENTAG(DATUM(AA1;1;1)))

Die Formel kopierte ich von Kalenderwoche zu Kalenderwoche und änderte einfach den Wert für "Kalenderwoche", sprich ich sorgte dafür dass der Wert AA3 entsprechend der richtigen Kalenderwoche entspricht.

Im Jahr 2017, klappt es trotz Deiner vorgeschlagenen Anpassungen (ich weiss derzeit nicht ob ich diese so umgesetzt habe wie Du dir das vorgestellt hast), nicht....

Mit folgender Formel :

=DATUM(AA1;1;2*AA3+1-WOCHENTAG(DATUM(AA1;1;1)))

komme ich zwar auf den richtigen Wert für den Montag der Kalenderwoche 1 im Jahr 2017.
Kopiere ich diese Formel jedoch in die vorgesehene Zelle fürs Montag der Kalenderwoche 2 im Jahr 2017 (unter Anpassung des Wertes AA3) erscheint nicht das erwartete Datum , Montag 9. Januar 2017.


Die richtigen Werte fürs den Anfang des Jahres 2017 wären wie folgt

Jahr 2017 
*********
KW1 Montag 2.1  Freitag 6.1
KW2 Montag 9.1  Freitag 13.1
KW3 Montag 16.1 Freitag 20.1
KW4 Montag 23.1 Freitag 28.1

Viellicht bin ich ja mit meiner Formel auch auf dem Holzweg und es gäbe etwas einfachers für meinen Anwendungsfall?

Ich verwende Excel 2016 im Verbund mit einem Office 365 Enterprise E3 plan.
Es gäbe zwar auch die Möglichkeit, mit Visio oder anderen Applikationen meinen Projektplan irgendwie linear abzubilden.
Das gute an Excel ist, dass es im weit verbreitet ist und als Benutzer Anpassungen relativ schnell eingepflegt hat, auch über den Webview des O365 Portals.

 

[SOL Solutions]

Hallo,

das ist also die Aenderung, welche Du mit Hilfe meinen Anmerkung gebastelt hast? Ohne Mist, wie passt das zusammen?

OK, hier ist die fertige Formel, schau es Dir nochmal genau an.

=DATUM(AA1;1;7*AA3+2-WOCHENTAG(DATUM(AA1;1;1);2))

Vielleicht liegt's ja daran das ich ein Komma verwendet habe anstelle eines Semikolon.

Beste Gruesse

 

[Terrenco]

Der DAU schlechthin, oder ;-) ?

Du hast mit Deiner Antwort vom Samstagmorgen die Lösung geliefert, ich wusste einfach nicht wohin mit der 2.
Es ist so, dass ich meine Formel die ich aus den tiefen des Internets kopiert habe, halt immer noch nicht bis zur letzten Zeichenfolge verstehe - aber Dank Deiner Antwort funktioniert es nun einwandfrei.

Nochmals herzlichsten Dank für die Geduld


Aus dem Office Support Blog (quelle: https://support.office.com/de-de/ar...e404c190949a?ui=de-DE&rs=de-DE&ad=DE&fromAR=1 )

WOCHENTAG(Zahl;[Typ])

Die Syntax der Funktion WOCHENTAG weist die folgenden Argumente auf:

Zahl Erforderlich. Eine fortlaufende Zahl, die den Tag des gesuchten Datums darstellt. Datumsangaben sollten mithilfe der Funktion DATUM oder als Ergebnisse anderer Formeln oder Funktionen eingegeben werden. Verwenden Sie z. B. für den 23. Mai 2008 "DATUM(2008;5;23)". Es können Probleme auftreten, wenn Datumsangaben als Text eingegeben werden.

Typ Optional. Eine Zahl, mit der der Typ des Rückgabewerts bestimmt wird