Office: (Office 2013) Kalenderwoche angeben ohne Bezug auf ein eingegebenes Datum

Hallo,

vor einiger Zeit hatte ich mit einer Datei zu tun, in der das funktionierte, mir die Formel aber leider nicht raus kopiert
Es war kein VBA, nur eine Formel. Nun versuche ich das nachzubauen.

Angegeben war nur die Jahreszahl in einer Zelle, die man mit einem Steuerelement hoch und runterschrauben konnte - klappt, ok.

Dann kamen 2 Spalten für Datumswerte, deren Zeitraum rechts unter einer Zeile mit den Kalenderwochen des Jahres mit 0 oder 1 markiert war, je nachdem, welche KW von dem Zeitraum betroffen war (=1) oder nicht (=0).

In der Zeile über den Kalenderwochen stand jeweils der betreffende Monat (Jan, Feb, ..., Dez, Jan.) - bzgl den Monaten habe ich es glaub hinbekommen (selbst noch überrascht bin ;) passt sich auch gemäß der Jahreszahl an.
Aber darunter die KW (1,2,3,...) bekomme ich nicht hin.
Jedenfalls nicht, so, dass ich für den 1.1.2006 und 1.1.2011 korrekt die KW 52 angezeigt bekomme.

Habe es auch mit dem Bezugsdatum 4. Januar probiert, klappt nicht.
Ich meine mich zu erinnern, dass die (lange) Formel für die KW u. a. TEILEN enthielt, was mich überraschte - und weshalb ich mir die Formel auch noch erklären lassen wollte

Es geht eigentlich also nur um eine Formel, die die KW 1,2,3,...,52,1 zählt.
Und einziger Bezugspunkt ist das Jahr in einer Zelle und bei den Monaten die Formel =DATUM(C1;1;1).

Im Anhang habe ich ein screenshot mit Infos und Formeln zum besseren Verständnis.

In den anderen Threads wurde bei KALENDERWOCHE immer Bezug auf ein Datum genommen.

PS: mit =KALENDERWOCHE(F2;21) bzw =KALENDERWOCHE(F2;11) erhalte ich zwar KW, aber es werden die erste und letzte und 1. des nächsten Jahres falsch angegeben

Weiß jemand, wie die Formel richtig wäre ?

Danke und viele Grüße
skys


Edit: habe gerade dieses hier in einem anderen Forum gefunden, was teilweise passt.
Dass die 3 Formeln der laufenden KW sich quasi auf die vorherige beziehen (Zeile 2), hatte ich auch, aber komme nicht mehr darauf. Allerdings hätte ich in B1, C1 die Monate Jan, Feb und nicht 1. KW, 2. KW stehen.
Bild davon hänge ich auch noch an.

:)

 

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.
aussagekräftigen Dateinamen hoch.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten

 

Hi,

Da liegt wohl das Problem wieder mal 50cm vor dem Bildschirm!

Arbeitsblatt mit dem Namen 'Tabelle1'
*	A	B
1	01.01.2011	52
2	01.01.2006	52

Zelle	Formel
B1	=KALENDERWOCHE(A1;21)
B2	=KALENDERWOCHE(A2;21)

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

 

Wenn exakt die zum 1. eines Monats zugehörige KW gemeint sein soll:

A2: Jahr, z.B. 2017
A3 =A2+1 (alternativ siehe unten)
B1: =REST(SPALTE(B1)-2;12)+1

B2: =KALENDERWOCHE(B$1&-($A2+KÜRZEN((SPALTE(B2)-2)/12));21)

• Du kannst nun entweder die Formel B1 über M1 rechts hinaus kopieren ("Ewigkeitszeile"; dann brauchst Du nur A2),
• oder Du verwendest A3/kopierst A3 herunter, benötigst dann aber nur B1:M1 (eine Zeile pro Jahr mit 12 Monaten).

Die Formel B2 passt für beide Zusammenhänge, passend darunter kopiert.
________________________________________________________________________________ __________________
Exkurs, nicht direkt zur Frage:
In jedem Jahr werden 53 KW berührt. Ausnahme: Schaltjahre mit So. 1.1. (alle 12, 28 oder 40 Jahre, siehe unten) wie 2040. Da sind es 54. Somit ist ein Jahr entweder 52,14 (normales) oder 52,29 (Schaltjahr) Wochen lang.

Hier die 54 Wochen anreißenden Jahre, im 12-, 28- oder 40-Jahres-Abstand:

C2: 1.1.2012
C3: =C2+WENN(REST(1&-JAHR(C2)-12;7)=1;4382;WENN(REST(1&-JAHR(C2)-28;7)=1;10227;14609)) oder
C3: {=C2+7*MIN(WENN(TEXT(1&-JAHR(C2)-{12.28.40};"TTT.T")="So.1";{626.1461.2087}))}
D3: =JAHR(C3)-JAHR(C2)

(Zeile 3 runterkopieren)

 

Hallo euch Dreien,

und Danke für eure Antworten !

@ Hajo
Sorry, ich hatte das schon mal gelesen, dachte aber, es wäre nicht nötig.
Im Anhang ist nun die Datei.
(Ich verstehe, dass oft auf Bilder ausgewichen wird, da ein Herunterladen für jeden immer auch ein Risiko birgt, sich etwas einzufangen.)

@ BoskoBiati
Deine Lösung trifft nicht ganz meine Frage. Ich gebe ja kein Datum an.

@ Lupo
Deinen Beitrag habe ich eben erst gesehen - muss ich noch durchdenken.
Die Formel, an die ich mich erinnere, hatte glaube ich, außer TEILEN auch irgendwo ein KÜRZEN und REST drin. *leider nicht mehr weiß

Muss es erst ausprobieren, melde mich aber, wenn es passt oder ich stecken bleibe (letzteres evtl eher möglich ;)

Jedenfalls schon mal vielen Dank !

Eiswürfel und Grüße
skys

 

beziehe dich mit Kalenderwoche auf Spalte C, da D nicht geändert wird.

 

Hallo Hajo,

Du meinst auf das Datum in Spalte D ?

Das wäre ein Missverständnis.
Ich beziehe mich jetzt nur auf Tabellenblatt "Soll".
Die KW in Spalte B und E gibt's eigentlich gar nicht, habe ich nur für mich zur Kontrolle eingefügt.
Es geht mir um Zeile 4, wo die KWs mit Formeln hinterlegt sind und als einziger Bezug das Jahr in C1 oder der Inhalt von F3 in Frage kommen kann.
Ab der 3 Zelle (H4) wird dann nur wieder nach rechts kopiert.
So dass die ganze Zeile 4 bis zum Jan des Folgejahres mit Formeln hinterlegt ist und sich genauso automatisch anpasst, wenn ich das Jahr ändere, wie Zeile 3.

Ich versuche gerade noch die Formeln von lupo anzugleichen, aber glaube, das passt nicht, weil hierbei immer noch weitere "Helferzellen" mit Datum benötigt werden.

Gruß
skys

 

Gut mein Tipp willst Du nicht umsetzen. Den Bezug auf die andere Tabelle konnte ich nicht sehen und bin darum raus.

 

Nanu, welch unfreundlicher Ton.
Von "nicht umsetzen wollen" (nachgesehen) und "andere Tabelle" (gibt nur eine) kann keine Rede sein, keine Ahnung, wie du darauf kommst.

Es geht nicht um die Datumsangaben in Spalte C oder D, allenfalls um das Jahr in C1, ansonsten nur um Zeile 3 und Zeile 4.

 

Hi,

und warum nicht Kalenderwoche(f3;21) und das nach rechts ziehen????

Arbeitsblatt mit dem Namen 'Soll'
*	A	B	C	D	E
1	*	2011	*	*	*
2	*	*	*	*	*
3	*	*	*	Jan	Jan
4	Tag	Datum	Datum	52	1

Zelle	Formel
D4	=KALENDERWOCHE(D3;21)

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

 

Hallo BoskoBiati,

vielen Dank - und super, das funktioniert !
Hatte ich sogar auch schon auf Tabellenblatt "Suche" in Zeile 4,nur mit dem Fehler 11 statt 21 (in der Datei sehen kannst) - deshalb klappte es nicht.

Aber ich weiß nicht, ob die Formel reicht, um das weitere zu machen:
In den Spalten C und D habe ich pro Zeile einen Zeitraum (von C bis D).
Und unter den Kalenderwochen hatte ich eine WENN-Formel, so dass eine 1 eingetragen wurde, wenn der Zeitraum in der darüberstehenden KW lag, und eine 0 eingetragen wurde, wenn nicht.

Das bekomme ich so nicht hin. Ich vermute, es liegt an der anderen Formel.
Ich hatte mich damals in der WENN-Formel auf Zeile 4 bezogen - dort stand aber eine viel längere Formel drin.
Oder weißt du, wie das nun gehen müsste ?

(Bild im Anhang, mit =WENN($C7 < E$4 < $D7;"1";"0") klappt nicht)
Leerzeichen nur, weil sonst die Formel im Beitrag nicht ausgeschrieben wurde.

Viele Grüße
skys

 

Hallo,

grundlegende Syntaxregeln sollte man schon kennen:

Code:

geht auch einfacher:

Arbeitsblatt mit dem Namen 'Soll'
*	C	D	E	F	G	H	I
1	2018	*	*	*	*	*	*
2	*	*	*	*	*	*	*
3	*	*	*	Jan	Jan	Jan	Jan
4	Datum	Datum	KW	1	2	3	4
5	26.12.2017	02.01.2018	1	1	0	0	0
6	01.01.2018	08.01.2018	2	1	1	0	0

Zelle	Formel
F3	=DATUM(C1;1;1)
G3	=F3+7
F4	=KALENDERWOCHE(F3;21)
F5	=($C5<=F)*($D5>=F)

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

 

Hallo BoskoBiati,

also, ich bin noch dabei...

UND hatte ich auch schon, ging nicht.
Und jetzt rätsel ich, weil es bei dir anscheinend geht, bei mir aber nicht (s. Anhang).

Probiere mittlerweile ja schon alles mögliche aus, nur um sicher zu gehen...
Funktioniert nicht:
=WENN(UND(C7 < F4;D7 > F4)1;0)
=WENN(UND(C7 < F4;D7 > F4);1;0)
=WENN(UND(C7 < F4;D7 > F4)"1";"0")
=WENN(UND(C7 < F4;D7 > F4);"1";"0")

HEUREKA ! Deine andere Formel klappt !!
... aber WARUM ???

Also mit WENN bekomme ich es immer noch nicht hin *rätsel*

Und dein =($C5=F$3) funktioniert, aber ich verstehe es nicht (
Was bedeutet die Multiplikation ? Eine Verknüpfung ?
Und warum funktioniert das, obwohl keine WENN-Bedingung mit dabei ist ?

*begeistert, aber unwissend davor sitz... !

Schon mal super vielen Dank !
Vor allem dachte ich, es müsste diese viel längere Formel sein, aber du hast das in eine elegante kurze gepackt.

Würde es nur gerne noch verstehen !

skys

 

Moin!
Zunächst am Rande:
Die Formeln der Threaderöffnung stammen ja von mir.
Die sind deshalb etwas "komplzierter", weil ja andere Voraussetzungen vorlagen.
Da Du jedoch echte Daten in einem benutzerdefinierten Zahlenformat vorliegen hast, kannst Du (siehe Bosko) auch gleich KALENDERWOCHE() nutzen.

Zu Deinen Fragen:

Deine UND()-Formeln kannst Du nicht ziehen, weil Du die jew. Spalte C:D nicht mittels $ fixiert hast.

Ein Vergleich ($C5 0 entspricht dann WAHR

Gruß Ralf

 

Hallo Ralf,

und danke für deine Antwort !
Finde es schön, dass du dich meldest und ich mich bei dir so auch noch mal für den Input mit deinen Formeln bedanken kann *Smilie

An WAHR und FALSCH hatte ich überhaupt nicht gedacht - aber stimmt, das ist hier natürlich auch eine gute Möglichkeit. Wäre ich aber auch nicht von allein darauf gekommen.
Vielen Dank für deine Erklärung !

Nichtsdestotrotz beißt es mich noch, dass ich bei WENN hängen geblieben bin. Habe es (auch mit $) immer noch nicht hinbekommen und mich an 2 Stellen (WENN und bedingte Formatierung) im Moment ins Excel-Nirvana befördert. (Ärgerlich vor allem, weil ich schon mal drauf kam und nun Null). Mal hoffen, dass es mit klarem Kopf noch funkt !!

Bis später und soweit schon VIELEN DANK !!!
skys