Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1236to1240
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1236to1240
1236to1240
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kalenderwochen (dynamisch) ermitteln

Kalenderwochen (dynamisch) ermitteln
Schmecks
Hallo,
ich habe folgendes Problem:
Ich möchte in Excel mir die Kalenderwoche automatisch anhand der dahinter eingetragenen Wochentage ermitteln. Da alle horizontal aufgelisteten Wochentage immer nur aus einer Kalenderwoche sein können, muss in bei der Berechnung nur überprüft werden ob in der Zelle eine Zahl (= ein Datum) steht. Da die Kalenderdaten dynamisch je nach Jahr (2012-2020) ermittelt werden, können die ersten oder letzten Zellen leer sein, da der Monat (siehe Beispiele) erst am Dienstag mit dem 01.01.2013 beginnt. Wie bringe ich Excel dazu, über eine Formel, immer alle Zellen einer Woche (horizontale Reihe) abzuprüfen, ob diese ein Datum enthalten. Die Formel zur Ermittlung der Kalenderwoche ist mir bekannt (KÜRZEN((D51-DATUM(JAHR(D51+3-REST(D51-2;7));1;REST(D51-2;7)-9))/7)).
Ich hatte schon mit der Formeln ISTZAHL() rumprobiert aber leider ohne Erfolg, da ich zwar jede Zelle auswerten kann ob sie eine Zahl ist oder nicht, aber mir dann die Möglichkeit fehlt, wenn eine Zahl gefunden wird, dann diese auf die Kalenderwochenformel anzuwenden.
Ich hoffe ich konnte mein Anliegen rüberbringen und freue mich auf eure Antworten.
Beispiele für Januar und September 2013

       
Jan.       
KWMODIMIDOFRSASO
xxx 123456
xxx78910111213
xxx14151617181920
xxx21222324252627
xxx28293031   
xxx       
 
Sept.       
KWMODIMIDOFRSASO
xxx      1
xxx2345678
xxx9101112131415
xxx16171819202122
xxx23242526272829
xxx30     

mfg
Schmecks
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 10:49:32
dave
Hallo [Name?]
Wenn ich dich richtig verstehe, ist deine Zelle D51 als Basis für die KW-Formel dynamisch zu gestalten. Vielleicht hilft es dir, diese mit Bereich.Verschieben an die richtige Stelle zu rücken?!
Da in deiner Beispieltabelle die Zellenbeschriftungen nicht ersichtlich sind, kann ich da momentan nicht konkreter werden, aber vielleicht reicht ja der Denkanstoß. :-)
Gruß
David
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 11:07:47
Mag
Hi,
würde es denn nicht reichen in der Monatsmitte die KW zu ermitteln und die Wochen runter/rauf zu rechnen?
Gruß
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 11:22:17
Schmecks
nicht ganz siehe weiter unten ...
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 12:56:37
Erwin
Hallo Schmecks
ist das in etwa für dich so O.K.
https://www.herber.de/bbs/user/77597.xls
Gruß
Erwin
Anzeige
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 14:11:39
Schmecks
Hallo,
eigentlich hast du mit deiner Tabelle in soweit recht, das man davon ausgehen kann das in jeder Zeile (Woche) zumindestens am Sonntag ein Wert drin stehen wird, wenn auch nur er 01.xx.xx. Insofern könnte man die Formel vereinfachen und dann wie bei dir die Kalenderwochen hochzählen.
Danke für deine Mühe.
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 10:57:55
Reinhard
Hallo Schnecks,
lade mal eine mappe hoch damit man sieht wie du da was formatiert hast.
Gruß
Reinhard
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 11:05:37
hary
Hallo
ein Versuch.
Tabelle1
 ABCDEFG
439          1
540234567
6             

verwendete Formeln
Zelle Formel Bereich
A4 =WENN(ANZAHL(B4:G4)=0;"";KÜRZEN((MAX(B4:G4)-DATUM(JAHR(MAX(B4:G4)+3-REST(MAX(B4:G4)-2;7));1;REST(MAX(B4:G4)-2;7)-9))/7)) 
A5 =WENN(ANZAHL(B5:G5)=0;"";KÜRZEN((MAX(B5:G5)-DATUM(JAHR(MAX(B5:G5)+3-REST(MAX(B5:G5)-2;7));1;REST(MAX(B5:G5)-2;7)-9))/7)) 
A6 =WENN(ANZAHL(B6:G6)=0;"";KÜRZEN((MAX(B6:G6)-DATUM(JAHR(MAX(B6:G6)+3-REST(MAX(B6:G6)-2;7));1;REST(MAX(B6:G6)-2;7)-9))/7)) 

Tabellendarstellung in Foren Version 5.30


gruss hary
Anzeige
AW: Kalenderwochen (dynamisch) ermitteln
22.11.2011 11:19:01
Schmecks
Hallo,
also hier mal mein Excel-Tabellenblatt:
ich hoffe es ist nicht zu unübersichtlich. Die einzelnen Kalender-Monatsansichten werden wie bereits gesagt dynamisch erzeugt, je nach dem was für eine Jahreszahl ich wähle. Es geht lediglich darum die kalenderwoche dann korrekt anzuzeigen.
Calendar (2)
 ABCDEFGHIJKLMN
1                           
2    JANUAR  Jahr2013 
3  KWMODIMIDOFRSASO         
4  xx.  123456         
5  2. KW78910111213         
6  xx.14151617181920         
7  xx.21222324252627         
8  xx.28293031               
9  xx.                       
10                           
11                           
12    SEPTEMBER         
13  KWMODIMIDOFRSASO         
14  xx.            1  #WERT!  35. KW 
15  xx.2345678         
16  xx.9101112131415         
17  xx.16171819202122         
18  xx.23242526272829         
19  xx.30                     
20                           

verbundene Zellen
C2:I2
L2:M2
C12:I12

verwendete Formeln
Zelle Formel Bereich
C2 =WENN($Q$4="";"JANUARY";WVERWEIS($Q$4;Translations!$C$3:$I$31;3;WAHR)) 
L2 =Q5 
C3 =WENN($Q$4="";"MON";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;15;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
D3 =WENN($Q$4="";"TUE";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;16;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
E3 =WENN($Q$4="";"WED";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;17;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
F3 =WENN($Q$4="";"THU";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;18;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
G3 =WENN($Q$4="";"FRI";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;19;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
H3 =WENN($Q$4="";"SAT";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;20;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
I3 =WENN($Q$4="";"SUN";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;21;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
C4 =WENN(WOCHENTAG(DATUM($L$2;1;1))=2;DATUM($L$2;1;1);"") 
D4 =WENN(C4<>"";C4+1;WENN(UND(C4="";WENN(WOCHENTAG(DATUM($L$2;1;1))=3;DATUM($L$2;1;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;1;1))=3;DATUM($L$2;1;1);""))) 
E4 =WENN(D4<>"";D4+1;WENN(UND(D4="";WENN(WOCHENTAG(DATUM($L$2;1;1))=4;DATUM($L$2;1;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;1;1))=4;DATUM($L$2;1;1);""))) 
F4 =WENN(E4<>"";E4+1;WENN(UND(E4="";WENN(WOCHENTAG(DATUM($L$2;1;1))=5;DATUM($L$2;1;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;1;1))=5;DATUM($L$2;1;1);""))) 
G4 =WENN(F4<>"";F4+1;WENN(UND(F4="";WENN(WOCHENTAG(DATUM($L$2;1;1))=6;DATUM($L$2;1;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;1;1))=6;DATUM($L$2;1;1);""))) 
H4 =WENN(G4<>"";G4+1;WENN(UND(G4="";WENN(WOCHENTAG(DATUM($L$2;1;1))=7;DATUM($L$2;1;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;1;1))=7;DATUM($L$2;1;1);""))) 
I4 =WENN(H4<>"";H4+1;WENN(UND(H4="";WENN(WOCHENTAG(DATUM($L$2;1;1))=1;DATUM($L$2;1;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;1;1))=1;DATUM($L$2;1;1);""))) 
B5 =KÜRZEN((I5-DATUM(JAHR(I5+3-REST(I5-2;7));1;REST(I5-2;7)-9))/7) 
C5 =I4+1 
D5 =C5+1 
E5 =D5+1 
F5 =E5+1 
G5 =F5+1 
H5 =G5+1 
I5 =H5+1 
C6 =I5+1 
D6 =C6+1 
E6 =D6+1 
F6 =E6+1 
G6 =F6+1 
H6 =G6+1 
I6 =H6+1 
C7 =I6+1 
D7 =C7+1 
E7 =D7+1 
F7 =E7+1 
G7 =F7+1 
H7 =G7+1 
I7 =H7+1 
C8 =WENN(ODER(I7="";I7=MONATSENDE(DATUM($L$2;1;1);0));"";I7+1) 
D8 =WENN(ODER(C8="";C8=MONATSENDE(DATUM($L$2;1;1);0));"";C8+1) 
E8 =WENN(ODER(D8="";D8=MONATSENDE(DATUM($L$2;1;1);0));"";D8+1) 
F8 =WENN(ODER(E8="";E8=MONATSENDE(DATUM($L$2;1;1);0));"";E8+1) 
G8 =WENN(ODER(F8="";F8=MONATSENDE(DATUM($L$2;1;1);0));"";F8+1) 
H8 =WENN(ODER(G8="";G8=MONATSENDE(DATUM($L$2;1;1);0));"";G8+1) 
I8 =WENN(ODER(H8="";H8=MONATSENDE(DATUM($L$2;1;1);0));"";H8+1) 
C9 =WENN(ODER(I8="";I8=MONATSENDE(DATUM($L$2;1;1);0));"";I8+1) 
D9 =WENN(ODER(C9="";C9=MONATSENDE(DATUM($L$2;1;1);0));"";C9+1) 
E9 =WENN(ODER(D9="";D9=MONATSENDE(DATUM($L$2;1;1);0));"";D9+1) 
F9 =WENN(ODER(E9="";E9=MONATSENDE(DATUM($L$2;1;1);0));"";E9+1) 
G9 =WENN(ODER(F9="";F9=MONATSENDE(DATUM($L$2;1;1);0));"";F9+1) 
H9 =WENN(ODER(G9="";G9=MONATSENDE(DATUM($L$2;1;1);0));"";G9+1) 
I9 =WENN(ODER(H9="";H9=MONATSENDE(DATUM($L$2;1;1);0));"";H9+1) 
C12 =WENN($Q$4="";"SEPTEMBER";WVERWEIS($Q$4;Translations!$C$3:$I$30;11;WAHR)) 
C13 =WENN($Q$4="";"MON";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;15;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
D13 =WENN($Q$4="";"TUE";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;16;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
E13 =WENN($Q$4="";"WED";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;17;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
F13 =WENN($Q$4="";"THU";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;18;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
G13 =WENN($Q$4="";"FRI";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;19;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
H13 =WENN($Q$4="";"SAT";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;20;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
I13 =WENN($Q$4="";"SUN";LINKS(WVERWEIS($Q$4;Translations!$C$3:$I$30;21;WAHR);WVERWEIS($Q$4;Translations!$C$3:$I$30;27;WAHR))) 
C14 =WENN(WOCHENTAG(DATUM($L$2;9;1))=2;DATUM($L$2;9;1);"") 
D14 =WENN(C14<>"";C14+1;WENN(UND(C14="";WENN(WOCHENTAG(DATUM($L$2;9;1))=3;DATUM($L$2;9;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;9;1))=3;DATUM($L$2;9;1);""))) 
E14 =WENN(D14<>"";D14+1;WENN(UND(D14="";WENN(WOCHENTAG(DATUM($L$2;9;1))=4;DATUM($L$2;9;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;9;1))=4;DATUM($L$2;9;1);""))) 
F14 =WENN(E14<>"";E14+1;WENN(UND(E14="";WENN(WOCHENTAG(DATUM($L$2;9;1))=5;DATUM($L$2;9;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;9;1))=5;DATUM($L$2;9;1);""))) 
G14 =WENN(F14<>"";F14+1;WENN(UND(F14="";WENN(WOCHENTAG(DATUM($L$2;9;1))=6;DATUM($L$2;9;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;9;1))=6;DATUM($L$2;9;1);""))) 
H14 =WENN(G14<>"";G14+1;WENN(UND(G14="";WENN(WOCHENTAG(DATUM($L$2;9;1))=7;DATUM($L$2;9;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;9;1))=7;DATUM($L$2;9;1);""))) 
I14 =WENN(H14<>"";H14+1;WENN(UND(H14="";WENN(WOCHENTAG(DATUM($L$2;9;1))=1;DATUM($L$2;9;1);"")="");"";WENN(WOCHENTAG(DATUM($L$2;9;1))=1;DATUM($L$2;9;1);""))) 
K14 =KÜRZEN((C14-DATUM(JAHR(C14+3-REST(C14-2;7));1;REST(C14-2;7)-9))/7) 
M14 =KÜRZEN((I14-DATUM(JAHR(I14+3-REST(I14-2;7));1;REST(I14-2;7)-9))/7) 
C15 =I14+1 
D15 =C15+1 
E15 =D15+1 
F15 =E15+1 
G15 =F15+1 
H15 =G15+1 
I15 =H15+1 
C16 =I15+1 
D16 =C16+1 
E16 =D16+1 
F16 =E16+1 
G16 =F16+1 
H16 =G16+1 
I16 =H16+1 
C17 =I16+1 
D17 =C17+1 
E17 =D17+1 
F17 =E17+1 
G17 =F17+1 
H17 =G17+1 
I17 =H17+1 
C18 =WENN(ODER(I17="";I17=MONATSENDE(DATUM($L$2;9;1);0));"";I17+1) 
D18 =WENN(ODER(C18="";C18=MONATSENDE(DATUM($L$2;9;1);0));"";C18+1) 
E18 =WENN(ODER(D18="";D18=MONATSENDE(DATUM($L$2;9;1);0));"";D18+1) 
F18 =WENN(ODER(E18="";E18=MONATSENDE(DATUM($L$2;9;1);0));"";E18+1) 
G18 =WENN(ODER(F18="";F18=MONATSENDE(DATUM($L$2;9;1);0));"";F18+1) 
H18 =WENN(ODER(G18="";G18=MONATSENDE(DATUM($L$2;9;1);0));"";G18+1) 
I18 =WENN(ODER(H18="";H18=MONATSENDE(DATUM($L$2;9;1);0));"";H18+1) 
C19 =WENN(ODER(I18="";I18=MONATSENDE(DATUM($L$2;9;1);0));"";I18+1) 
D19 =WENN(ODER(C19="";C19=MONATSENDE(DATUM($L$2;9;1);0));"";C19+1) 
E19 =WENN(ODER(D19="";D19=MONATSENDE(DATUM($L$2;9;1);0));"";D19+1) 
F19 =WENN(ODER(E19="";E19=MONATSENDE(DATUM($L$2;9;1);0));"";E19+1) 
G19 =WENN(ODER(F19="";F19=MONATSENDE(DATUM($L$2;9;1);0));"";F19+1) 
H19 =WENN(ODER(G19="";G19=MONATSENDE(DATUM($L$2;9;1);0));"";G19+1) 
I19 =WENN(ODER(H19="";H19=MONATSENDE(DATUM($L$2;9;1);0));"";H19+1) 

Tabellendarstellung in Foren Version 5.47


Anzeige
AW: hast du probiert?
22.11.2011 11:25:03
hary
Hallo
hast Du meine Formel mit Max probiert.
gruss hary
AW: hast du probiert?
22.11.2011 12:43:14
Schmecks
Danke funktioniert super.
Kannst du mir vielleicht beim Verständnis helfen?
Die Wenn-Funktion ansich ist klar, erst prüfen ob Anzahl = 0, sonst leer lassen, ansonsten Formel.
Doch das mit dem Kürzen sind für mich böhmische Dörfer.
Kannst du das mal beschreiben?
mfg
Schmecks

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige