KW --> Datum Formel

[StephPsc]

Hi,

Frage 1: wie kann ich aus eine Zelle wo die Kalenderwoche/ Jahr (z.B. 40/ 2016) steht den ersten Montag dieser Woche rausziehen (z.B. 03.10.2016) ?
Habe bis jetzt nur Lösungen gefunden wo die Woche und das Jahr in unterschiedliche Zellen stehen... Die Formatierung soll exakt so sein wie in die Beispiele.

Frage 2: gibt es eine Formel die folgendes Beispiel in 1 Schritt lösen kann: KW 40/ 2016 - 31 (Wochen) = 09.03.2016 (Montag von KW9/ 2016)
Er soll also die Datum Zelle nehmen wo meine KWs drinnen stehen, eine normale Zahl abziehen und das exakte datum als Lösung ausgeben.

Vielen Dank und LG,
Steph

[Kl@us-M.]

Hi Steph,

wie liegen die einstelligen KW's vor: Mit vorangestellter "0", also "05/ 2016" oder ohne "0" (5/ 2016) ???

[StephPsc]

mit 0... und einen Abstand nachdem "/"
Also z.B. 05/ 2016 oder 10/ 2018

Dankeschön!

[Kl@us-M.]

Hi Steph,

probier mal so (wobei die 9. KW 2016 am Montag, dem 29.02.2016 begonnen hat und nicht wie vor Dir geschrieben am 09.03.2016):

	A
1	Zu Deiner Frage 1:
2	40/ 2016
3	03.10.2016
4
5	Zu Deiner Frage 2:
6	40/ 2016
7	Anzahl der abzuziehenden Kalenderwochen: 31
8	29.02.2016

Zelle	Formel
A3	=("4.1."&RECHTS(A2;4))+LINKS(A2;2)*7-7-REST("2.1."&RECHTS(A2;4);7)
A8	=("4.1."&RECHTS(A6;4))+(LINKS(A6;2)-A7)*7-7-REST("2.1."&RECHTS(A6;4);7)

[StephPsc]

Passt, danke!! :)

Kannst du mir bitte noch verraten was ich in Formel 1 ändern soll wenn ich z.B. den Freitag haben will anstatt den Montag?

Danke schon mal! Du hast mich echt weiter geholfen :)

[Kl@us-M.]

Hi Steph,

für den Freitag schreibst Du hinter Formel 1 ganz einfach "+4" (ohne ""):

=("4.1."&RECHTS(A2;4))+LINKS(A2;2)*7-7-REST("2.1."&RECHTS(A2;4);7)+4

Warum "+4": Weil es von Montag bis Freitag eben vier Tage mehr sind :19:

[StephPsc]

Dankeschön!!

[StephPsc]

=("4.1."&RECHTS(A2;4))+LINKS(A2;2)*7-7-REST("2.1."&RECHTS(A2;4);7)

Könnte jemand mir die Formel auch mal kurz erklären?

Und wenn ich dieses Ergebnis jetzt vergleichen will mit ein anderes datum, wie kann ich excel dann klär machen das er das Ergebnis als Datum sieht?
z.B. wenn das datum in Zelle A8 = das datum was aus meine formel rauskommt -->färbe die zelle grün

Danke!

[IchBinIch]

Hi,

ich bin kein Formelexperte und wundere mich auch immer was man so alles "konstruieren" kann.

Im Prinzip wird im ersten Teil der Formel ein Datum erzeugt:

("4.1."&RECHTS(A2;4))
"Schreibe" "4.1." und füge die letzten 4 Stellen (=2016) aus dem Wert in A2 an = "4.1.2016"

+LINKS(A2;2)*7-7
Addiere zu diesem Datum (4.1.2016) das Produkt aus der Multiplikation der ersten beiden Stellen des Wertes in A2 (=40) und 7 und ziehe davon 7 ab.
An dieser Stelle ist bereits der Montag zu der in A2 befindlichen KW ermittelt.

Wofür dann noch der hintere Teil der Formel -REST("2.1."&RECHTS(A2;4);7) benötigt wird, kann ich leider nicht sagen. Das würde ich gerne an einen der Experten weitergeben.

Im übrigen gibt es im Register "Formeln" unter "Formelüberwachung" die Formelauswertung. Dort kannst Du Dir Schritt für Schritt anschauen wie die Formel abgearbeitet wird.

Gruß
Ich

Vielleicht wird es so deutlicher:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
2	40/ 2016	4.1.2016	273	03.10.2016	Mo

Zelle	Formel
B2	=("4.1."&RECHTS(A2;4))
C2	=LINKS(A2;2)*7-7
D2	=B2+C2
E2	=TEXT(WOCHENTAG(D2;1);"TTT")

[BoskoBiati]

Hallo,

("4.1."&RECHTS(A2;4)) ergibt den 4.1. des Jahres aus dem String in A2

LINKS(A2;2)*7-7 ergibt die Tage aus der Wochenanzahl in A2 (7*40) abzüglich 7, da der 4.1 definitiv in der Woche 1 (ist per DIN so festgelegt) liegt, man also nur eine Woche weniger addieren darf.

REST("2.1."&RECHTS(A2;4);7) ergibt den Wochentag den der 2.1. hat, da man den noch abziehen muß, damit man den Montag erhält (zufällig ergibt das in 2016 eine  0!!!!!)


[html]

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
2	41/2017	Mo 09.10.17
3	41/2017	Mi 11.10.17

Zelle	Formel
B2	=("4.1."&RECHTS(A2;4))+LINKS(A2;2)*7-7-REST("2.1."&RECHTS(A2;4);7)
B3	=("4.1."&RECHTS(A3;4))+LINKS(A3;2)*7-7

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[/html]


@IchBinIch, es gibt eine Formelauswertung, da kann man das nachvollziehen.  :19: 
Konstruieren ist hier wohl falsch, Die Logik der Kalenderwochen in eine Formel gepackt.