Registriert seit: 10.04.2014
Version(en): 2016 + 365
20.11.2015, 15:07
(Dieser Beitrag wurde zuletzt bearbeitet: 20.11.2015, 15:47 von Rabe .
Bearbeitungsgrund: Grund für Aktion eingefügt.
)
Hi,
ich habe hier eine Liste mit Datums-Bereichen für Ferien und da möchte ich rechts davon eine Liste aller Ferientage untereinanderweg geschrieben haben, wie in der Spalte I manuell gezeigt. In I3 wird mit Drehfeld das Jahr ausgewählt. Ich möchte mit dieser Liste in einem Jahreskalender mit bedingter Formatierung die Ferientage bunt einfärben.
Wie kann ich die Tagesliste erstellen? Mit Formel? Mit VBA?
Ferien A B C D E F G H I 3 Winterferien Osterferien Pfingstferien Sommerferien Herbstferien Weihnachtsferien 2016 4 2012 02.04. - 13.04. 29.05. - 09.06. 26.07. - 08.09. 29.10. - 02.11. 24.12. - 05.01. 29.03.2016 5 2013 25.03. - 05.04. 21.05. - 01.06. 25.07. - 07.09. 28.10. - 30.10. 23.12. - 04.01. 30.03.2016 6 2014 14.04. - 25.04. 10.06. - 21.06. 31.07. - 13.09. 27.10. - 30.10. 22.12. - 05.01. 31.03.2016 7 2015 30.03. - 10.04. 26.05. - 06.06. 30.07. - 12.09. 02.11. - 06.11. 23.12. - 09.01. 01.04.2016 8 2016 29.03. - 02.04. 17.05. - 28.05. 28.07. - 10.09. 02.11. - 04.11. 23.12. - 07.01. 02.04.2016 9 2017 '- 10.04. - 21.04. 06.06. - 16.06. 27.07. - 09.09. 30.10. - 03.11. 22.12. - 05.01. 17.05.2016 10 2018 '- 26.03. - 06.04. 22.05. - 02.06. 26.07. - 08.09. 29.10. - 02.11. 24.12. - 05.01. 18.05.2016 11 2019 '- 15.04. - 27.04. 11.06. - 21.06. 29.07. - 10.09. 28.10. - 30.10. 23.12. - 04.01. 19.05.2016 12 2020 '- 06.04. - 18.04. 02.06. - 13.06. 30.07. - 12.09. '- '- 20.05.2016
Excel-Inn.de Hajo-Excel.de XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007 Add-In-Version 19.08 einschl. 64 Bit
Registriert seit: 13.04.2014
Version(en): 365, 2019
Hallo,
Zitat: da möchte ich rechts davon eine Liste aller Ferientage untereinanderweg geschrieben haben
warum? Man könnte das auch abfragen, ohne es aufzudröseln.
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 10.04.2014
Version(en): 2016 + 365
20.11.2015, 15:47
(Dieser Beitrag wurde zuletzt bearbeitet: 20.11.2015, 15:47 von Rabe .)
Hi Edgar,
(20.11.2015, 15:36) BoskoBiati schrieb: warum? Man könnte das auch abfragen, ohne es aufzudröseln.
Ich möchte mit dieser Liste in einem Jahreskalender mit bedingter Formatierung die Ferientage bunt einfärben.
Aber wenn das auch anders geht, ...
Registriert seit: 13.04.2014
Version(en): 365, 2019
Hallo,
Tabelle1 B C D E F G H I J 3 Winterferien Osterferien Pfingstferien Sommerferien Herbstferien Weihnachtsferien 2016 4 2012 02.04. - 13.04. 29.05. - 09.06. 26.07. - 08.09. 29.10. - 02.11. 24.12. - 05.01. 29.03.2016 5 2013 25.03. - 05.04. 21.05. - 01.06. 25.07. - 07.09. 28.10. - 30.10. 23.12. - 04.01. 30.03.2016 6 2014 14.04. - 25.04. 10.06. - 21.06. 31.07. - 13.09. 27.10. - 30.10. 22.12. - 05.01. 31.03.2016 7 2015 30.03. - 10.04. 26.05. - 06.06. 30.07. - 12.09. 02.11. - 06.11. 23.12. - 09.01. 01.04.2016 8 2016 29.03. - 02.04. 17.05. - 28.05. 28.07. - 10.09. 02.11. - 04.11. 23.12. - 07.01. 02.04.2016 9 2017 '- 10.04. - 21.04. 06.06. - 16.06. 27.07. - 09.09. 30.10. - 03.11. 22.12. - 05.01. 16.05.2015 10 2018 '- 26.03. - 06.04. 22.05. - 02.06. 26.07. - 08.09. 29.10. - 02.11. 24.12. - 05.01. 18.05.2016 11 2019 '- 15.04. - 27.04. 11.06. - 21.06. 29.07. - 10.09. 28.10. - 30.10. 23.12. - 04.01. 19.05.2016 12 2020 '- 06.04. - 18.04. 02.06. - 13.06. 30.07. - 12.09. '- '- 20.05.2016
Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format J4 1. / Formel ist =Ferientag Abc
Namen in Formeln Zelle Name Bezieht sich auf J4 Ferientag =SUMME((Tabelle1!$J4>=(LINKS(INDEX(Tabelle1!$D$4:$D$12;Tabelle1!$J$3-2011):INDEX(Tabelle1!$H$4:$H$12;Tabelle1!$J$3-2011);6)&Tabelle1!$J$3)*1)*(Tabelle1!$J4<=(RECHTS(INDEX(Tabelle1!$D$4:$D$12;Tabelle1!$J$3-2011):INDEX(Tabelle1!$H$4:$H$12;Tabelle1!$J$3-2011);6)&Tabelle1!$J$3)*1))
Namen verstehen
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 10.04.2014
Version(en): 2016 + 365
Hallo Edgar,
ich habe es nun überprüft anhand einer Liste der gesamten Tage des Jahres.
Dabei fällt auf, daß die Weihnachtsferien nicht eingefärbt werden, vermutlich, weil sie in einem Jahr anfangen und im anderen enden. Da die Daten so aus dem Internet runtergeladen werden, möchte ich das auch nicht aufsplitten.
Ich wollte ja eigentlich die Tagesdatümer als Liste aus der Tabelle rausziehen und dann in dem tatsächlichen Kalenderblatt die Tage einfärben, wenn das Datum in der Zelle mit einem Datum in der Liste übereinstimmt, deswegen ist das tatsächliche Kalenderblatt anders aufgeteilt, hier mal das erste Quartal:
Jahreskalender A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB 1 2015 kein Schaltjahr 2 Januar Februar März 3 KW Mo Di Mi Do Fr Sa So KW Mo Di Mi Do Fr Sa So KW Mo Di Mi Do Fr Sa So 4 1 1 2 3 4 5 1 9 1 5 2 5 6 7 8 9 10 11 6 2 3 4 5 6 7 8 10 2 3 4 5 6 7 8 6 3 12 13 14 15 16 17 18 7 9 10 11 12 13 14 15 11 9 10 11 12 13 14 15 7 4 19 20 21 22 23 24 25 8 16 17 18 19 20 21 22 12 16 17 18 19 20 21 22 8 5 26 27 28 29 30 31 9 23 24 25 26 27 28 13 23 24 25 26 27 28 29 9 14 30 31 10 11 April Mai Juni
verwendete Formeln Zelle Formel Bereich N/A AA1 =WENN(TAG(--("1.2."&B1)+28)<>29;"kein ";"")&"Schaltjahr" C4,L4,U4 =WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=C3;DATUM($B$1;MONAT(B2&1);1);"") D4,M4,V4 =WENN(C4<>"";C4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=D3;DATUM($B$1;MONAT(B2&1);1);"")) E4,N4,W4 =WENN(D4<>"";D4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=E3;DATUM($B$1;MONAT(B2&1);1);"")) F4,O4,X4 =WENN(E4<>"";E4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=F3;DATUM($B$1;MONAT(B2&1);1);"")) G4,P4,Y4 =WENN(F4<>"";F4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=G3;DATUM($B$1;MONAT(B2&1);1);"")) H4,Q4,Z4 =WENN(G4<>"";G4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=H3;DATUM($B$1;MONAT(B2&1);1);"")) I4,R4,AA4 =WENN(H4<>"";H4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=I3;DATUM($B$1;MONAT(B2&1);1);"")) C5:C7,L5:L7,U5:U7 =MAX(C4:I4)+SPALTE($B$1)-1 D5:I7,M5:R7,V5:AA7 =C5+1 B4:B9,K4:K9,T4:T9 =WENN(ANZAHL(C4:I4)=0;"";KÜRZEN((MIN(C4:I4)-DATUM(JAHR(MIN(C4:I4)+3-REST(MIN(C4:I4)-2;7));1;REST(MIN(C4:I4)-2;7)-9))/7)) C8:C9,L8:L9,U8:U9 =WENN(I7="";"";WENN(MONAT(I7+1)=MONAT(I7);I7+1;"")) D8:I9,M8:R9,V8:AA9 =WENN(C8="";"";WENN(MONAT(C8+1)=MONAT(C8);C8+1;"")) B2,K2,T2,B11,K11,T11 =TEXT((KÜRZEN((ZEILE()-1)/9;)*3+1+KÜRZEN((SPALTE()-1)/9;))*30;"MMMM")
definierte Namen Name Bezieht sich auf Tabelle Z1S1-Formel Ferientag =SUMME((Ferien!$I1>=(LINKS(INDEX(Ferien!$C$4:$C$12;Ferien!$I$3-2011):INDEX(Ferien!$G$4:$G$12;Ferien!$I$3-2011);6)&Ferien!$I$3)*1)*(Ferien!$I1<=(RECHTS(INDEX(Ferien!$C$4:$C$12;Ferien!$I$3-2011):INDEX(Ferien!$G$4:$G$12;Ferien!$I$3-2011);6)&Ferien!$I$3)*1)) =SUM((Ferien!RC9>=(LEFT(INDEX(Ferien!R4C3:R12C3,Ferien!R3C9-2011):INDEX(Ferien!R4C7:R12C7,Ferien!R3C9-2011),6)&Ferien!R3C9)*1)*(Ferien!RC9<=(RIGHT(INDEX(Ferien!R4C3:R12C3,Ferien!R3C9-2011):INDEX(Ferien!R4C7:R12C7,Ferien!R3C9-2011),6)&Ferien!R3C9)*1)) ft =Jahreskalender!$AG$2:$AJ$26 Jahreskalender =Jahreskalender!R2C33:R26C36
Bedingte Formatierung Haupttabelle 1 Wird angewendet auf Nr. Bed. Regeltyp Operator Formel1 Formel2 Format Schrift Füllfarbe Unterstrichen Schrift- farbe Muster Musterfarbe Typ Bereich $C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30 01.Bed.: Formel ist =SVERWEIS($C1;ft;4;0)="X" 16764108 1 C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30 $C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30 02.Bed.: Zellwert ist gleich =HEUTE() 16751001 1 C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30 $C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36 01.Bed.: Formel ist =SVERWEIS(C4;ft;4;0)="X" 13434828 1 C22:G27,C4:G9,U22:Y27,U9:Y9,L4: P9,C13:G18,L31: P36,L13: P18,U13:Y18,L22: P27,C31:G36,U31:Y36 $C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36 02.Bed.: Zellwert ist gleich =HEUTE() 16751001 1 C22:G27,C4:G9,U22:Y27,U9:Y9,L4: P9,C13:G18,L31: P36,L13: P18,U13:Y18,L22: P27,C31:G36,U31:Y36 $H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36 01.Bed.: Formel ist =SVERWEIS(H4;ft;4;0)="X" 13434828 1 H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36 $H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36 02.Bed.: Zellwert ist gleich =HEUTE() 16751001 1 H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36 $H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36 03.Bed.: Zellwert ist ungleich ="" 12632256 1 H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36 $U$4:$Y$8 01.Bed.: Formel ist =SVERWEIS(U4;ft;4;0)="X" 13434828 1 U4:Y8 $U$4:$Y$8 02.Bed.: Zellwert ist gleich =HEUTE() 16751001 1 U4:Y8 $Z$4:$AA$8 01.Bed.: Formel ist =SVERWEIS(Z4;ft;4;0)="X" 13434828 1 Z4:AA8 $Z$4:$AA$8 02.Bed.: Zellwert ist gleich =HEUTE() 16751001 1 Z4:AA8 $Z$4:$AA$8 03.Bed.: Zellwert ist ungleich ="" 12632256 1 Z4:AA8 $C$10:$I$10,$L$10:$R$10 01.Bed.: Formel ist =SVERWEIS(C10;ft;4;0)="X" 255 1 C10:I10,L10:R10 $C$10:$I$10,$L$10:$R$10 02.Bed.: Zellwert ist gleich =HEUTE() 16751001 1 C10:I10,L10:R10
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich Wird angewendet auf Nr. Bed. Format Zelle Anhalten Auswahl Anzeige Anzahl Durchschnitt Typ Bereich $C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30 01.Bed. Wahr 1 C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30 $C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30 02.Bed. Wahr 1 C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30 $C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36 01.Bed. Wahr 1 C22:G27,C4:G9,U22:Y27,U9:Y9,L4:P9,C13:G18,L31:P36,L13:P18,U13:Y18,L22:P27,C31:G36,U31:Y36 $C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36 02.Bed. Wahr 1 C22:G27,C4:G9,U22:Y27,U9:Y9,L4:P9,C13:G18,L31:P36,L13:P18,U13:Y18,L22:P27,C31:G36,U31:Y36 $H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36 01.Bed. Wahr 1 H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36 $H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36 02.Bed. Wahr 1 H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36 $H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36 03.Bed. Wahr 1 H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36 $U$4:$Y$8 01.Bed. Wahr 1 U4:Y8 $U$4:$Y$8 02.Bed. Wahr 1 U4:Y8 $Z$4:$AA$8 01.Bed. Wahr 1 Z4:AA8 $Z$4:$AA$8 02.Bed. Wahr 1 Z4:AA8 $Z$4:$AA$8 03.Bed. Wahr 1 Z4:AA8 $C$10:$I$10,$L$10:$R$10 01.Bed. Wahr 1 C10:I10,L10:R10 $C$10:$I$10,$L$10:$R$10 02.Bed. Wahr 1 C10:I10,L10:R10
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen. Excel-Inn.de Hajo-Excel.de XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007 Add-In-Version 19.08 einschl. 64 Bit
Die Namensformel ist mir zu komplex und somit bekomme ich die Formatierung im Original-Blatt nicht hin. Kannst Du Dir mal den Kalender anschauen?
Jahreskalender mit Ferien.xlsb (Größe: 29,24 KB / Downloads: 8)
Registriert seit: 13.04.2014
Version(en): 365, 2019
23.11.2015, 15:52
(Dieser Beitrag wurde zuletzt bearbeitet: 23.11.2015, 15:52 von BoskoBiati .)
Hallo Ralf,
der Kalender war doch auch mal meine Baustelle, oder?
Kopie von Jahreskalender mit Ferien.xlsb (Größe: 29,01 KB / Downloads: 8)
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Folgende(r) 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag: 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag 28
• Rabe
Registriert seit: 10.04.2014
Version(en): 2016 + 365
Hi Edgar,
(23.11.2015, 15:52) BoskoBiati schrieb: der Kalender war doch auch mal meine Baustelle, oder?das kann sein, aber das weiß ich nicht mehr.
Jetzt funktioniert es klasse mit den Ferien.
Registriert seit: 29.09.2015
Version(en): 2030,5
Ich hatte eine andere 'Lösung'
Angehängte Dateien
0_Jahreskalender mit Ferien.xlsb (Größe: 22,55 KB / Downloads: 10)
Registriert seit: 13.04.2014
Version(en): 365, 2019
Hallo,
Zitat Ralf:
Zitat: Da die Daten so aus dem Internet runtergeladen werden, möchte ich das auch nicht aufsplitten.
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 10.04.2014
Version(en): 2016 + 365
Hi,
(23.11.2015, 18:45) snb schrieb: Ich hatte eine andere 'Lösung'
danke auch für Deine Lösung, aber wie schon Edgar schrieb, stehen die Ferientermine auf einer Internet-Seite und dort ist das genau so formatiert. Damit können die Feriendaten durch einfaches Kopieren ohne weitere Tätigkeiten in die Tabelle übertragen und dann die Tage eingefärbt werden.