Clever-Excel-Forum

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

	A	B	C	I	J	K
1	2015
2	KW	Jan 15		KW	Feb 15
3	1	Do	01.01.2015	6	Mo	02.02.2015
4	1	Fr	02.01.2015	6	Di	03.02.2015
5	2	Mo	05.01.2015	6	Mi	04.02.2015
6	2	Di	06.01.2015	6	Do	05.02.2015
7	2	Mi	07.01.2015	6	Fr	06.02.2015
8	2	Do	08.01.2015	7	Mo	09.02.2015
9	2	Fr	09.01.2015	7	Di	10.02.2015
10	3	Mo	12.01.2015	7	Mi	11.02.2015
11	3	Di	13.01.2015	7	Do	12.02.2015
12	3	Mi	14.01.2015	7	Fr	13.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

Hi Ralf, eine Möglichkeit wäre..:

{edited} weil fehlerbehaftet gewesen...

Arbeitsblatt mit dem Namen 'Tabelle4'
	A	B	C
1	2015
2	KW		Jan 15
3		Fr	02.01.2015
4		Mo	05.01.2015
5		Di	06.01.2015
6		Mi	07.01.2015
7		Do	08.01.2015
8		Fr	09.01.2015
9		Mo	12.01.2015
10		Di	13.01.2015
11		Mi	14.01.2015

Zelle	Formel
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

Hallo Ralf, ansonsten gibts natürlich auch die Funktion(en) ARBEITSTAG() oder ARBEITSTAG.INTL ()

...

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

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... '
	A	B
1	Beginn:	01.04.2012
2	Ende:	30.04.2012
3	ArbTage:	19
4
5	02.04.2012	Mo
6	03.04.2012	Di
7	04.04.2012	Mi
8	05.04.2012	Do
9	07.04.2012	Sa
10	10.04.2012	Di
11	11.04.2012	Mi
12	12.04.2012	Do
13	13.04.2012	Fr
14	14.04.2012	Sa
15	16.04.2012	Mo
16	17.04.2012	Di
17	18.04.2012	Mi
18	19.04.2012	Do
19	20.04.2012	Fr
20	21.04.2012	Sa
21	23.04.2012	Mo
22	24.04.2012	Di
23	25.04.2012	Mi
24
25

Name	Bezug
feier	=feiertage!$B$1:$B$10

Zelle	Formel
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

Hallo!

Habe mir das gebastelt,so wollte ich es.

Tabelle1

	A	B
1	Mi 01.01.2014
2	oh. Sa. So. Feiertage
3	Do 02.01.2014
4	Fr 03.01.2014
5	Mo 06.01.2014
6	Mi 08.01.2014	Feiertage
7	Do 09.01.2014
8	Fr 10.01.2014	Mi 01.01.2014
9	Mo 13.01.2014	Di 07.01.2014
10	Di 14.01.2014	Mi 15.01.2014
11	Do 16.01.2014	Fr 17.01.2014
12	Mo 20.01.2014
13	Di 21.01.2014

Formeln der Tabelle

Zelle Formel 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

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...

Hi,

(17.12.2014, 19: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

	A	B	C
1	2015	Wareneingänge
2	KW	Tag	Datum
3	KW 1/2015	Fr	02.01.2015
4	KW 2/2015	Mo	05.01.2015
5	KW 2/2015	Mi	07.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

Hallo Ralf,

ich würde es so gestalten:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K
1	2015
2	KW	Jan 15		KW	Feb 15		KW	Mrz 15
3	KW 1/15	Fr	02.01.2015	KW 6/15	Mo	02.02.2015	KW 10/15	Mo	02.03.2015		01.01.2015
4	KW 2/15	Mo	05.01.2015	KW 6/15	Di	03.02.2015	KW 10/15	Di	03.03.2015		06.01.2015
5	KW 2/15	Mi	07.01.2015	KW 6/15	Mi	04.02.2015	KW 10/15	Mi	04.03.2015
6	KW 2/15	Do	08.01.2015	KW 6/15	Do	05.02.2015	KW 10/15	Do	05.03.2015
7	KW 2/15	Fr	09.01.2015	KW 6/15	Fr	06.02.2015	KW 10/15	Fr	06.03.2015
8	KW 3/15	Mo	12.01.2015	KW 7/15	Mo	09.02.2015	KW 11/15	Mo	09.03.2015
9	KW 3/15	Di	13.01.2015	KW 7/15	Di	10.02.2015	KW 11/15	Di	10.03.2015
10	KW 3/15	Mi	14.01.2015	KW 7/15	Mi	11.02.2015	KW 11/15	Mi	11.03.2015
11	KW 3/15	Do	15.01.2015	KW 7/15	Do	12.02.2015	KW 11/15	Do	12.03.2015
12	KW 3/15	Fr	16.01.2015	KW 7/15	Fr	13.02.2015	KW 11/15	Fr	13.03.2015

Zelle	Format	Wert
B2	MMM JJ	01.01.2015
C2	MMM JJ
B3	TTT	02.01.2015
C3	TT.MM.JJJJ	02.01.2015

Name	Bezug
FT	=Tabelle1!$K$3:$K$4

Zelle	Formel
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