Clever-Excel-Forum › Office-Anwendungen › Excel v
nur Werktage in Listen eintragen

Thema geschlossen 
Baumstrukturmodus
nur Werktage in Listen eintragen
Rabe Offline
:-)
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#1
17.12.2014, 16:33
Hallo,
ich habe eine Werktages-Liste und bei Änderung des Jahres in A1 sollen dann wieder nur die Werktage in der Tabelle stehen.
Da ich das für alle Monate des Jahres machen muß, wäre eine Formel für die Spalten C und K und im gleichen Abstand weiter zum Runterziehen sehr praktisch.
Wie mache ich das?

Tabelle1
ABCIJK
12015
2KWJan 15KWFeb 15
31Do01.01.20156Mo02.02.2015
41Fr02.01.20156Di03.02.2015
52Mo05.01.20156Mi04.02.2015
62Di06.01.20156Do05.02.2015
72Mi07.01.20156Fr06.02.2015
82Do08.01.20157Mo09.02.2015
92Fr09.01.20157Di10.02.2015
103Mo12.01.20157Mi11.02.2015
113Di13.01.20157Do12.02.2015
123Mi14.01.20157Fr13.02.2015

 verbundene Zellen
A1:C1
B2:C2
J2:K2

verwendete Formeln
Zelle Formel Bereich N/A
J2=MONATSENDE(B2;0)+1
A3:A12,I3:I12=KALENDERWOCHE(C3;21)
B3:B12,J3:J12=C3
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.19 einschl. 64 Bit

Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#2
17.12.2014, 16:42 (Dieser Beitrag wurde zuletzt bearbeitet: 17.12.2014, 16:46 von Jockel.)
Hi Ralf, eine Möglichkeit wäre..:

{edited} weil fehlerbehaftet gewesen...

Arbeitsblatt mit dem Namen 'Tabelle4'
 ABC
12015  
2KW Jan 15
3 Fr02.01.2015
4 Mo05.01.2015
5 Di06.01.2015
6 Mi07.01.2015
7 Do08.01.2015
8 Fr09.01.2015
9 Mo12.01.2015
10 Di13.01.2015
11 Mi14.01.2015

ZelleFormel
C2=(1&-A1)+0
C3=C2+1+((REST(C2;7)=6)+(REST(C2+1;7)=0))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#3
17.12.2014, 16:56
Hallo Ralf, ansonsten gibts natürlich auch die Funktion(en) ARBEITSTAG() oder ARBEITSTAG.INTL ()

...
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
Wilfried Höttl Offline
pausiert
Registriert seit: 12.04.2014
Version(en): 2003
#4
17.12.2014, 17:50
Hallo Jockel!


Möchte mich hier anhängen.
Du bist ja ein richtiger Formelkönig.

Und wie müssen die Formeln geändert werden, daß die Feiertage auch nicht angezeigt werden?

Danke für eine Antwort

Gruß
Wilfried
Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#5
17.12.2014, 18:16
Hallo Willfried, da würde ich dann sicher ARBEITSTAG() oder ARBEITSTAG.INTL() anwenden, weil es dafür (optional) einen Parameter gibt. Aber das weißt Du ja... Ansonsten werden es Array-Formeln... ... und die werden auch ziemlich heftig..:

Arbeitsblatt mit dem Namen 'arbeitstage... '
 AB
1Beginn:01.04.2012
2Ende:30.04.2012
3ArbTage:19
4  
502.04.2012Mo
603.04.2012Di
704.04.2012Mi
805.04.2012Do
907.04.2012Sa
1010.04.2012Di
1111.04.2012Mi
1212.04.2012Do
1313.04.2012Fr
1414.04.2012Sa
1516.04.2012Mo
1617.04.2012Di
1718.04.2012Mi
1819.04.2012Do
1920.04.2012Fr
2021.04.2012Sa
2123.04.2012Mo
2224.04.2012Di
2325.04.2012Mi
24  
25  

NameBezug
feier=feiertage!$B$1:$B$10

ZelleFormel
A5{=$B$1-1+KKLEINSTE(WENN((WOCHENTAG($B$1-1+(ZEILE(INDIREKT("1:"&ZEILE(B1)*9))-1))<=6)*ISTNV(VERGLEICH($B$1-1+(ZEILE(INDIREKT("1:"&ZEILE(B1)*9)));feier;0));ZEILE(INDIREKT("1:"&ZEILE(B1)*9)));ZEILE(B1))}
A6{=WENN(ANZAHL($A$5:A5)>$B$3-1;"";$A$5+KKLEINSTE(WENN((WOCHENTAG($A$5+(ZEILE(INDIREKT("1:"&ZEILE(A1)*9)))-1)<=6)*ISTNV(VERGLEICH($A$5+(ZEILE(INDIREKT("1:"&ZEILE(A1)*9)));feier;0));ZEILE(INDIREKT("1:"&ZEILE(A1)*9)));ZEILE(A1)))}
A7{=WENN(ANZAHL($A$5:A6)>$B$3-1;"";$A$5+KKLEINSTE(WENN((WOCHENTAG($A$5+(ZEILE(INDIREKT("1:"&ZEILE(A2)*9)))-1)<=6)*ISTNV(VERGLEICH($A$5+(ZEILE(INDIREKT("1:"&ZEILE(A2)*9)));feier;0));ZEILE(INDIREKT("1:"&ZEILE(A2)*9)));ZEILE(A2)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
Wilfried Höttl Offline
pausiert
Registriert seit: 12.04.2014
Version(en): 2003
#6
17.12.2014, 19:43 (Dieser Beitrag wurde zuletzt bearbeitet: 17.12.2014, 19:46 von Wilfried Höttl.)
Hallo!

Habe mir das gebastelt,so wollte ich es.

Tabelle1

AB
1Mi 01.01.2014
2oh. Sa. So. Feiertage
3Do 02.01.2014
4Fr 03.01.2014
5Mo 06.01.2014
6Mi 08.01.2014Feiertage
7Do 09.01.2014
8Fr 10.01.2014Mi 01.01.2014
9Mo 13.01.2014Di 07.01.2014
10Di 14.01.2014Mi 15.01.2014
11Do 16.01.2014Fr 17.01.2014
12Mo 20.01.2014
13Di 21.01.2014
Formeln der Tabelle
ZelleFormel
A3=ARBEITSTAG(A1-1;1;B$8:B$20)
A4=ARBEITSTAG(A3;1;B$8:B$20)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß
Wilfried
Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#7
17.12.2014, 20:03
Hallo Wilfried, ich verstehe. Aber das hatte ich doch geschrieben...!?!? Wieso benutzt Du denn zwei Formeln...? So kenne ich Dich ja gar nicht... ... hier reicht eine..:

=ARBEITSTAG($A$1-1;ZEILE(A1);B$8:B$20)

und runner ziehen...
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
Rabe Offline
:-)
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#8
18.12.2014, 15:00
Hi,

(17.12.2014, 20:03)Jockel schrieb: Wieso benutzt Du denn zwei Formeln...? So kenne ich Dich ja gar nicht... ... hier reicht eine..:
=ARBEITSTAG($A$1-1;ZEILE(A1);B$8:B$20)

danke an alle! Ich habe es jetzt so gemacht:

Daten
ABC
12015Wareneingänge
2KWTagDatum
3KW 1/2015Fr02.01.2015
4KW 2/2015Mo05.01.2015
5KW 2/2015Mi07.01.2015

verwendete Formeln
Zelle Formel Bereich N/A
A3:A5="KW " & KALENDERWOCHE(C3;21) & "/" & JAHR(C3)
B3:B5=C3
C3:C5=ARBEITSTAG(DATUM($A$1;1;1)-1;ZEILE(A1);Feiertage!$B$59:$B$87)
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.19 einschl. 64 Bit

Suchen
Top
BoskoBiati Offline
नौसिखिया [vidyaarthi]

Registriert seit: 13.04.2014
Version(en): 365
#9
18.12.2014, 16:24
Hallo Ralf,

ich würde es so gestalten:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJK
12015          
2KWJan 15 KWFeb 15 KWMrz 15   
3KW 1/15Fr02.01.2015KW 6/15Mo02.02.2015KW 10/15Mo02.03.2015 01.01.2015
4KW 2/15Mo05.01.2015KW 6/15Di03.02.2015KW 10/15Di03.03.2015 06.01.2015
5KW 2/15Mi07.01.2015KW 6/15Mi04.02.2015KW 10/15Mi04.03.2015  
6KW 2/15Do08.01.2015KW 6/15Do05.02.2015KW 10/15Do05.03.2015  
7KW 2/15Fr09.01.2015KW 6/15Fr06.02.2015KW 10/15Fr06.03.2015  
8KW 3/15Mo12.01.2015KW 7/15Mo09.02.2015KW 11/15Mo09.03.2015  
9KW 3/15Di13.01.2015KW 7/15Di10.02.2015KW 11/15Di10.03.2015  
10KW 3/15Mi14.01.2015KW 7/15Mi11.02.2015KW 11/15Mi11.03.2015  
11KW 3/15Do15.01.2015KW 7/15Do12.02.2015KW 11/15Do12.03.2015  
12KW 3/15Fr16.01.2015KW 7/15Fr13.02.2015KW 11/15Fr13.03.2015  

ZelleFormatWert
B2MMM JJ01.01.2015
C2MMM JJ 
B3TTT02.01.2015
C3TT.MM.JJJJ02.01.2015

NameBezug
FT=Tabelle1!$K$3:$K$4

ZelleFormel
B2=DATUM($A$1;KÜRZEN(SPALTE()/3;)+1;1)
A3="KW "&KALENDERWOCHE(B3;21)&"/"&RECHTS($A$1;2)
B3=C3
C3=ARBEITSTAG.INTL(B2;1;1;FT)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste