Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

kw 48 in datum umrechnen

kw 48 in datum umrechnen
04.03.2021 14:29:13
Marc-David
Hallo zusammen,
ist es mögich, wenn in Zelle A1 beispielsweise "kw 48" steht, dies mithilfe von

=A1&" "&JAHR($A$2)
in ein Datum umzuschreiben, dass also 29.11.2021 entsteht.
Meine Idee ist, dass man kw 48 + Jahr in eine Zelle eingibt und daraus ein Datum entsteht, welches abgeglichen werden kann aber im Moment scheitere ich an Eingabe hin zu Ausgabe.
Viele Grüße
MD

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: kw 48 in datum umrechnen
04.03.2021 14:47:24
Marc-David
Hallo steve1da,
danke für den Link, geht das auch in einer Zelle?
Also ich schreibe z.B. "cw 48.21" dann meine ich die Kalenderwoche des Jahres 2021.
oder "cw 48.20" dann meine ich die Kalenderwoche des Jahres 2020.
Viele Grüße
MD

Montag der KW in A1 des Jahres in A2
04.03.2021 14:49:17
WF
Hi,
=7*KÜRZEN((2&-1&-A2)/7+TEIL(A1;4;99))-5
WF

AW: kw 48 in datum umrechnen
04.03.2021 14:53:38
Daniel
Hi
so für den Montag der jeweiligen KW
=Datum(Jahr($A$2);1;4)-Wochentag(Datum(Jahr($A$2);1;4);3)+7*(rechts(A1;2)-1)
Gruß Daniel

Anzeige
AW: kw 48 in datum umrechnen
04.03.2021 15:09:07
Marc-David
Hallo Daniel,
geht das auch anders? Wenn ich "kw 48.20" eingebe oder "48.20" dass Excel dann ein Datum erstellt also 23.11.2020 oder 28.11.2020, welcher Tag der Woche ist egal, hauptsache die richtige Kalenderwoche + Jahr.
Viele Grüße
MD

AW: kw 48 in datum umrechnen
04.03.2021 15:43:53
Daniel
das geht auch anders.
ich habe mich halt daran orientiert, was du in der Eingangsfrage beschrieben hast.
Ich gehe immer davon aus, dass der Fragesteller (also du) in der Eingangsfrage das Problem so beschreibt, wie es tatsächlich vorliegt.
wenn du das anders hast als du es beschrieben hast, dann musst du die Formeln entsprechend anpassen.
die Jahreszahl (2021, oder was auch immer, vermutlich funtioniert auch 20, 21 usw, aber bitte prüfen ob Excel das richtige Jahrhundert ergänzt) muss anstelle der Funktion Jahr($A$2) in die Formel
die Kalenderwoche (48) muss anstelle der Funktion rechts(A1;2) in die Formel
damit solltest du das entsprechend anpassen können.
wenn dir der Wochentag innerhalb der Kalenderwoche egal ist, dann kannst du auch den Formelteil
-Wochentag(Datum(Jahr($A$2);1;4);3) einfach weglassen, dieser verschiebt das Datum nur auf den Montag der Woche.
das Formelprinzp ist einfach:
zunächst gernierst du das Datum 4.1 des jeweiligen Jahres. der 4.1 liegt nach deutscher DIN immer in der KW1.
zu diesem Datum kannst du dann einfach die KW-Nr-1 multipliziert mit 7 (Anzahl Wochen * Anzahl Tage pro Woche) hinzuaddieren.
Gruß Daniel

Anzeige
AW: kw 48 in datum umrechnen
04.03.2021 15:22:53
Rudi
Hallo,
cw48.21 in A1:
=7*KÜRZEN((2&-1&-2000-RECHTS(A1;2))/7--TEIL(A1;4;2))-5
Gruß
Rudi

AW: kw 48 in datum umrechnen
05.03.2021 09:55:34
Marc-David
Hallo Rudi,
das funktioniert wunderbar. Ich bedanke mich.
Viele Grüße
MD

AW: kw 48 in datum umrechnen
05.03.2021 11:11:48
Marc-David
Hallo Rudi,
es hat sich doch noch eine Frage von mir ergeben.
Wenn ich "cw09.21" schreibe ohne Leertaste kommt #WERT!
auch wenn ich "cw 9.21" schreibe kommt #WERT! und Excel erkennt nicht, dass es sich um Kalenderwoche 9 handelt.
Geht es irgendwie die Formel gegen diese Fehler zu schützen?
Viele Grüße
MD

AW: kw 48 in datum umrechnen
05.03.2021 11:33:49
Herbert_Grom
Hallo Marc-David,
probiers mal damit:
=DATUM("20"&RECHTS(A1;2)*1;1;7*WENN(FINDEN(".";A1)-2=0;TEIL(A1;FINDEN(".";A1)-1;1)*1;TEIL(A1; FINDEN(".";A1)-2;2)*1)-3-WOCHENTAG(DATUM("20"&RECHTS(A1;2)*1;;);3)) 
Servus

Anzeige
AW: kw 48 in datum umrechnen
05.03.2021 11:50:42
Marc-David
Hallo Herbert_Grom,
das funktioniert teilweise, danke dir.
Bei "cw9.21" kommt #WERT! "cw 9.21" geht aber. Eine Idee?
Viele Grüße
MD

AW: kw 48 in datum umrechnen
05.03.2021 12:33:12
Daniel
einfach immer "cw 9.21" oder "cw09.21" schreiben.
Gruß Daniel

AW: kw 48 in datum umrechnen
05.03.2021 12:35:11
Herbert_Grom
Dann solltest du mal "Datenhygiene" betreiben und nicht einmal 9.21 und dann wieder 9.21 schreiben!
Servus

AW: kw 48 in datum umrechnen
05.03.2021 12:38:51
Marc-David
Da ich leider nicht alleine diese Einträge mache und trotz Kommentarfenster mir Erklärung, leider davon ausgehen muss, dass jemand "cw3.21" schreibt habe ich die Frage gestellt.
Viele Grüße
MD

AW: kw 48 in datum umrechnen
05.03.2021 13:15:22
Herbert_Grom
Dann frage eben noch die String-Länge ab: WENN(LÄNGE(A1)=5;DANN....
Servus

Anzeige
AW: kw 48 in datum umrechnen
05.03.2021 13:21:18
Marc-David
Hallo Herbert_Grom,
wie habe ich das zu verstehen? Also wenn ich es an den Anfang setze kommt einfach ein FALSCH
=WENN(LÄNGE(G13)=5;DATUM("20"&RECHTS(G13;2)*1;1;7*WENN(FINDEN(".";G13)-2=0;TEIL(G13;FINDEN(".";G13) -1;1)*1;TEIL(G13;FINDEN(".";G13)-2;2)*1)-3-WOCHENTAG(DATUM("20"&RECHTS(G13;2)*1;;);3))) 
und wenn ich

=WENN(LÄNGE(F13)=5;DATUM("20"&RECHTS(F13;2)*1;1;7*WENN(FINDEN(".";F13)-2=0;TEIL(F13;FINDEN(".";F13) -1;1)*1;TEIL(F13;FINDEN(".";F13)-2;2)*1)-3-WOCHENTAG(DATUM("20"&RECHTS(F13;2)*1;;);3)); DATUM("20"&RECHTS(F13;2)*1;1;7*WENN(FINDEN(".";F13)-2=0;TEIL(F13;FINDEN(".";F13)-1;1)*1;TEIL(F13; FINDEN(".";F13)-2;2)*1)-3-WOCHENTAG(DATUM("20"&RECHTS(F13;2)*1;;);3))) 
dann macht er ja nur zweimal das gleiche, sorry verstehe nicht wie ich das einbauen soll.
Viele Grüße
MD

Anzeige
AW: kw 48 in datum umrechnen
05.03.2021 13:26:38
Herbert_Grom
Hallo Marc-David,
ich schau mir das noch mal an, aber es wird Nachmittag werden.
Servus

AW: kw 48 in datum umrechnen
05.03.2021 13:44:41
Marc-David
Kein Problem, danke sehr.
Ohne dich würde ich da nicht weiterkommen.
Viele Grüße
MD

AW: kw 48 in datum umrechnen
05.03.2021 15:23:17
Yal
Hallo Marc-David,
ich sehe, dass es hier sehr kompliziert gedacht wird. Ich schlage mal etwas einfaches vor...
Ich würde die Eingabe Kalenderwoche und Jahr weiter in 2 Zellen eingeben lassen. Es lässt sicher besser steuern:
_ in A1 die Kalenderwoche als Zahl.
_ durch Datenüberprüfung ("Daten", "Datenüberprüfung") zwingen, dass die Eingabe ganzzählig zwischen 1 und 53 stattfindet
_ in A2 das Jahr. Auch ganzzählig zwischen 20.. und 20.. (deine Wahl!)
_ aus A2 den 4 Januar errechnen, weil bei ISO immer Kw 1 (bei "Excel-Woche", muss Du ermitteln)
_ und darauf (A1-1)*7 addieren:
=DATUM(A2;1;4)+(A1-1)*7
oder bei Excel-kalenderwoche
=DATUM(A2;1;4)+(A1-KALENDERWOCHE(DatumA2;1;4))*7
Dann hast Du eine Datum innerhalb der gegebene Woche.
Wenn es den Montag sein soll, Minus Wochentag(Datum;1;4);3)
(Bei 3 ist Montag = 0)
VG
Yal

Anzeige
Und "KW " 00 als Format...
05.03.2021 15:34:23
Yal
...Ich habe vergessen zu erwähnen, dass die Eingabe als Zahl in A1 mit einem Datenformat
"kw "00 oder "kw "#0
um 9 als kw 09 oder kw 9
anzeigen zu lassen.
VG
Yal

AW: Und "KW " 00 als Format...
05.03.2021 16:10:54
Marc-David
Hallo Yal,
leider muss es zusammen in eine Zelle aber verstehe ich dich richtig, wenn ich das Format ändere wird automatisch aus der Eingabe "cw3.21" "cw 3.21" oder "cw 03.21"?
Viele Grüße
MD

AW: Und "KW " 00 als Format...
05.03.2021 17:01:27
Yal
Hallo MD,
Je nach dem wie die Eingabe ist. Bei ein solchen Zahl kannst Du dem "Benutzerdefinierte Format" (über Strg+1) als
"cw "#0,00
oder
"cw "00,00
definieren.
Aber es wird stets ein Komma anstatt einen Punkt. Es sei denn dein Excel ist auf Uk/US eingestellt.
Eingabe ist dann 9,21 (neun komma ein und zwanzig), was eine Datenüberprüfung schwierig macht.
Datenüberprüfung musste "benutzerdefiniert". Die Formel dazu sieht wir folgt aus (Spanne 2015-2045)
=UND(GANZZAHL(A11)<54;REST(A11;1)>0,15;REST(A11;1)<0,45)
Dazu müsste eine Meldung geben, um den User den richtigen Format nah zu legen.
Alternativ wäre eine komplette Liste der erlaubte Werte zu erzeugen und durch Datenüberprüfung der User zwingen eine Wert aus der Liste auszuählen oder einzutippen. Bei 4 Jahren sind es 208 bis 210 Einträge (separates, ausgeblentes Blatt).
VG
Yal

Anzeige
AW: kw 48 in datum umrechnen
05.03.2021 16:18:41
Herbert_Grom
Hallo Marc-David,
schau dir mal meinen Vorschlag an, der deckt doch jetzt alle 4 Möglichkeiten ab:
https://www.herber.de/bbs/user/144477.xlsx
Servus

AW: kw 48 in datum umrechnen
05.03.2021 16:23:59
Marc-David
Hallo Herbert_Grom,
"cw9.21" ist leider noch eine Möglichkeit.
viele Grüße
MD

AW: kw 48 in datum umrechnen
05.03.2021 16:53:28
Herbert_Grom
Hallo Mark-David,
probiers noch mal damit:

=DATUM("20"&RECHTS(A1;2)*1;1;7*TEIL(A1;FINDEN(".";A1)-1;1)*1-3-WOCHENTAG(DATUM("20"&RECHTS(A1;2)*1;;) ;3)) 
Servus

AW: kw 48 in datum umrechnen
10.03.2021 10:18:14
Marc-David
Hallo Herbert_Grom,
sorry für die späte Rückmeldung. Funktioniert top. Vielen vielen Dank :)
Viele Grüße
MD
Anzeige

325 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige