Microsoft Excel

Herbers Excel/VBA-Archiv

Datumsanzeige mit KW in Tabelle

Betrifft: Datumsanzeige mit KW in Tabelle von: Benjamin
Geschrieben am: 22.09.2020 07:50:34

Hallo, ich habe eine Exceldatei. In der soll automatisch die Kalenderwoche eingetragen werden. Ich habe aber kein konkretes Datum vom Tag, da diese Tage ja auch Dynamisch sein sollen und sich mit der Jahreszahl ändern sollen.
Ich habe die Excel Datei mal hochgeladen https://www.herber.de/bbs/user/140359.xlsx
Das hochgeladene Excel Datei zeigt jetzt noch SA und SO an, was ich nicht möchte.

Die Woche soll nur von Montag bis Freitag gehen. Samstag und Sonntag sollen ausgelassen werden.
In W3 soll die ausgerechnete Kalenderwoche stehen z.B. für 2021 ist die erste KW die KW53.
In D5 steht dann der erste Tag der letzten KW oder der erste Tag der ersten KW wenn der erste Tag von KW1 der 01.01 ist.
In W2 steht das richtige Datum für die ausgerechnete KW.

Ist es irgendwie möglich das mit Formeln hin zu bekommen. Ich habe zwar ein paar Formeln aber die hören dann irgendwann auf und funktionieren nicht mehr. Für ein paar Tipps oder sogar Formeln wäre ich sehr dankbar.
lg

Betrifft: AW: Datumsanzeige mit KW in Tabelle
von: UweD
Geschrieben am: 22.09.2020 09:21:45

Hallo

W1: =Übersicht!B3
W2: =DATUM(Übersicht!B3;1;1)
W3: =KALENDERWOCHE(W2;11)

D5: =DATUM(W1;1;4)-WOCHENTAG(DATUM(W1;1;4);11)+1

LG UweD

Betrifft: AW: Datumsanzeige mit KW in Tabelle
von: UweD
Geschrieben am: 22.09.2020 09:40:00

Sorry Fehler

2. Parameter 21 anstelle 11

W3: =KALENDERWOCHE(W2;21)

LG UweD

Betrifft: AW: Datumsanzeige mit KW in Tabelle
von: Benjamin
Geschrieben am: 22.09.2020 10:35:07

Also das sieht schon mal echt super aus, vielen dank.
Wenn ich W2:21 eingebe dann ist das Datum aber falsch. Dann habe ich den 27.12.2020 stehen anstatt den 28.12.2020

Betrifft: AW: Datumsanzeige mit KW in Tabelle
von: Benjamin
Geschrieben am: 22.09.2020 10:42:29

Wenn ich in D5 D5: =DATUM(W1;1;4)-WOCHENTAG(DATUM(W1;1;4);11)+1 eintrage, ist der erste Tag mit dem gerechnet wird der 04.01.2021 und nicht der 28.12.2020.
Wenn ich die Formel
=DATUM(W1;1;1)+(W3-WENN(WOCHENTAG(DATUM(W3;1;1);2)>4;0;1))*7-WOCHENTAG(DATUM(W1;1;1) +(W3-WENN(WOCHENTAG(DATUM(W3;1;1);2)>4;0;1))*7;2) +1
benutze, wird mir der 28.12.2020 angezeigt, was ja auch stimmt.
Wenn ich mit meiner Formel dann die Formel =KALENDERWOCHE(W2;11) in =KALENDERWOCHE(W2;21) ändere, ist der 27.12.2020 der erste Tag in D5.

Betrifft: AW: Datumsanzeige mit KW in Tabelle
von: UweD
Geschrieben am: 22.09.2020 10:56:36

Der 28.12.2020 ist der Montag von KW 53 vom Jahr 2020 und gehört NICHT ins Jahr 2021

Betrifft: AW: Datumsanzeige mit KW in Tabelle
von: Benjamin
Geschrieben am: 22.09.2020 11:37:05

Richtig, aber der 01.01.2021 ist ja noch in der 53. KW, deswegen wird der 28.12.2021 auch angezeigt am Montag. Wenn ich deine Formel alle eingebe so wie du sie aufgeschrieben hast, ist der 1. Tag der 53. KW der 27.12.2020 und Freitag dementsprechend der 31.12.2020

lg

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: neopa C
Geschrieben am: 22.09.2020 11:12:12

Hallo Benjamin,

... dazu gruppiere zunächst alle Deine KW-Tabellenblätter.

Dann:

in W3: =TEXT(TEIL(ZELLE("Dateiname";$A$1);FINDEN("]";ZELLE("Dateiname";$A$1))+1;31)+0;"00")
in D5: =7*KÜRZEN((2&-1&-W1)/7+W3)-5
in W1: =Übersicht!B3
in W2: =D5

danach hebe die Gruppierung wieder auf.

Damit hast Du alle Werktage jeder KW des jeweiligen Kalenderjahres einheitlich abgebildet.

Es fehlen lediglich bei einigen Jahren (so auch 2021) ein bis (ich meine) max 3 Werktage am Anfang des Jahres bzw. sind aus dem vergangenen Jahr zusätzlich noch gelistet. Letztere kannst Du mit bedingter Formatierung ausblenden. Für die fehlenden Tage des Jahres (2021 ist es nur der 1.1) kannst Du bei Bedarf ein Sonder-Tabellenblatt einfügen.

Gruß Werner
.. , - ...

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: Benjamin
Geschrieben am: 22.09.2020 11:38:34

Hallo, was meinst du mit KW Tabellenblätter. Soll ich alle Tabellenblätter markieren oder nur die Zelle der KW?

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: Benjamin
Geschrieben am: 22.09.2020 11:43:18

Ah okay, einfach nur alle Tabellenblätter markieren. Ich sehe was du meinst, Es fehlt dann die vorherige KW. Ja das ist nicht so einfach. Dann müsste man ein Extra Tabellenblatt einfügen.

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: Benjamin
Geschrieben am: 22.09.2020 12:08:23

Ist es denn möglich, wenn ich vor der 1.KW das Blatt 53.KW erzeuge, dass man dann Rückwärts rechnet mit einer Formel? Dann wäre alles perfekt.

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: UweD
Geschrieben am: 22.09.2020 12:50:40

Hallo

Kopiere das Blatt 01 und benenne es 53
In W1 schreibe =Übersicht!B3-1

LG UweD

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: Benjamin
Geschrieben am: 22.09.2020 13:00:35

Hi, das hilft nicht ganz weil dann kommt daraus Januar 2022 raus. Ich glaube die Formel =7*KÜRZEN((2&-1&-T1)/7+T3)-5 muss dann in T5 rein und dann in P5 =H5-1, in L5 =P5-1, ....
Aber dann habe ich das Problem, dass auch wieder Sonntage und Samstage angezeigt werden.

Betrifft: AW: Erfassung der Mo-Fr der KWen eines Jahres ...
von: UweD
Geschrieben am: 22.09.2020 13:14:07

Hallo

NICHT PLUS 1, sondern MINUS 1

Also wenn du es so gemacht hast, wie neopa C es dir vergestellt hast...



Bei mir sieht das Blatt 53 dann so aus. Was richtig wäre.



LG UweD

Betrifft: AW: das "Sonder"-KW-Tabellenblatt ...
von: neopa C
Geschrieben am: 22.09.2020 14:21:54

Hallo Benjamin,

... würde ich z.B. "00" (bin mir nämlich nicht sicher, ob es da momentan nicht auch eine KW54 geben könnte) benennen und folgende Formeln zuweisen (Formel P5 nach links kopieren:

Arbeitsblatt mit dem Namen '00'
 PQRSTUVW
1     Jahr2021
2     MonatJanuar
3     Woche53
4        
5    01Fr  

ZelleFormel
W1=Übersicht!B3
W2=T5
W3=WENNFEHLER(KALENDERWOCHE(W2;21);"")
P5=WENNFEHLER(WENN(JAHR(T5-1)=$W1;T5-1;"");"")
Q5=P5
T5=WENN(JAHR('01'!D5-3)=W1;'01'!D5-3;"")
U5=T5
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...

Betrifft: AW: das "Sonder"-KW-Tabellenblatt ...
von: Benjamin
Geschrieben am: 23.09.2020 05:35:42

Hallo Werner, ich habe es nun so eingegeben

W1 =Übersicht!B3 // Dann steht in KW53 Aber das Jahr 2021. Wenn ich =Übersicht!B3-1 eintrage steht da 2020

W2 =T5 // funktioniert nicht denn wenn T5 ein Januar ist aber KW53 noch im Dezember beginnt sollte man lieber =D5 eintragen

W3 =

P5 =WENNFEHLER(WENN(JAHR(T5-1)=$W1;T5-1;"");"") //Klappt zwar für 2023 aber wenn ich das Jahr 2021 auswähle sind T5 und P5 leer

lg

Betrifft: AW: das ist doch auch richtig so ...
von: neopa C
Geschrieben am: 23.09.2020 11:56:54

Hallo Benjamin,

... denn im Jahr 2020 gab es keinen Werktag, der zu einer KW aus dem Jahr 2019 gibt.

Im Tabellenblatt 01 stehen dagegen (korrekt) der 30. und der 31.12.(19) und somit in W2 auch Dezember, weil da die KW 01 beginnt.

Gruß Werner
.. , - ...

Betrifft: AW: das ist doch auch richtig so ...
von: Benjamin
Geschrieben am: 23.09.2020 12:19:26

Ich habe mich wohl unglücklich ausgedrückt. Das eingestellte Jahr im Januar 2021. Deswegen ist der 01.01.2021 noch in er 53KW. Wenn ich die Formel W1 =Übersicht!B3 eintrage in KW53 wird mir Januar angezeigt. Aber die 53KW gehört noch zum Dezember.

Betrifft: AW: der 1.1.20 ist zwar KW53 (2019) aber Januar...
von: neopa C
Geschrieben am: 23.09.2020 12:29:43

Hallo Benjamin,

... und sollte mE auch so stehen bleiben. Unglücklicher ist da für z.B. 2020 das im Tabellenblatt 01 da als Monat Dezember steht (weil der 30.12. der erste Werktag der 1. KW von 2020 ist.

Du musst Dich entscheiden, was Du willst.

Gruß Werner
.. , - ...

Betrifft: AW: das "Sonder"-KW-Tabellenblatt ...
von: Benjamin
Geschrieben am: 23.09.2020 06:03:37

  Gruppe  Jahr2020  
                 Monat 
                  Woche   
                       
 ######  ######  #######            
VornameStun
den
Anwes.
X / E / K
Hausaufg.
J/N
Früher abgeholt
um:
StundenAnwes.
X / E / K
Hausaufg.
J/N
Früher abgeholt
um:
StundenAnwes.
X / E / K
Hausaufg.
J/N
Früher abgeholt
um:
StundenAnwes.
X / E / K
Hausaufg.
J/N
Früher abgeholt
um:
StundenAnwes.
X / E / K
Hausaufg.
J/N
Früher abgeholt
um:
  
                       
                       

ZelleFormel
W1=Übersicht!B3-1
W2=T5
W3=IFERROR(WEEKNUM(W2,21),"")
D5=H5-1
E5=D5
H5=L5-1
I5=H5
L5=P5-1
M5=L5
P5=IFERROR(IF(YEAR(T5-1)=$W1,T5-1,""),"")
Q5=P5
T5=IF(YEAR('01'!D5-3)=W1,'01'!D5-3,"")
U5=T5
C7=Übersicht!H4
C8=Übersicht!H5


Betrifft: AW: siehe dazu mein Beitrag zuvor owT
von: neopa C
Geschrieben am: 23.09.2020 11:58:29

Gruß Werner
.. , - ...

Betrifft: AW: siehe dazu mein Beitrag zuvor owT
von: Benjamin
Geschrieben am: 23.09.2020 12:15:20

Müsste da nicht stehen KW53 weil der 01.01.2021 ist noch in der KW53 am Freitag und nicht in KW1.
Und dann müsste die Liste ja ausgefüllt sein...

Betrifft: AW: dem ist doch auch so ...
von: neopa C
Geschrieben am: 23.09.2020 12:24:24

Hallo Benjamin,

... siehe mein Beitrag von gestern 14:21.

Gruß Werner
.. , - ...

Betrifft: AW: dem ist doch auch so ...
von: Benjamin
Geschrieben am: 23.09.2020 13:00:52

In meinem Beitrag von heute 06.03 habe ich alles im Blatt 53 so eingetragen wie du es gesagt hast. Trotzdem hat die KW53 keine Anzeige wenn das Jahr in Blatt 01 auf 2021 gestellt ist.

Betrifft: AW: das hast Du eben nicht ...
von: neopa C
Geschrieben am: 23.09.2020 13:21:44

Hallo Benjamin,

... Du hast dort in W1 von Übersicht!B3 eine 1 subtrahiert. Richtig ist nur: =Übersicht!B3

Gruß Werner
.. , - ...

Beiträge aus dem Excel-Forum zum Thema "Datumsanzeige mit KW in Tabelle"