Clever-Excel-Forum

Hallo zusammen,

zunächst einmal möchte ich mich entschuldigen, für ein sogenanntes Crossposting in Spotlight.de https://www.spotlight.de/de/blog/article...ele-Jahre/ .
 
Dort kann mir jedoch niemand helfen.

Ich habe mir vor Jahren einen Schichtplaner erstellt bei dem sich die Ferienzeiten bei Änderung der Jahreszahl in A2 entsprechend anpasst. Es ist aber sehr mühselig jedes Jahr die Zellen mit dem nächsten Ferienjahr anzupassen.
Hat jemand eine Idee wie man die Formeln in den Zellen in meiner Beispieltabelle H8:O8 ändern muss, sodass diese einen Zellbereich z.B. für 20 Kalenderjahre (gerne auch separates Tabellenblatt) absucht.

Ich hoffe, ich habe mich einigermaßen verständlich ausgedrückt um mein Problem zu schildern.

[attachment=5940]

Gruß,
Ralf

Hola,

Zitat:Hat jemand eine Idee wie man die Formeln in den Zellen in meiner Beispieltabelle H8:O8 ändern muss

ohne die Datei zu sehen?

Gruß,
steve1da

sorry,
Datei habe ich eingefügt.

Hallöchen,

Du könntest die Zeilen mit INDIREKT berechnen, im Prinzip

=INDIREKT("H" & 2016-1816), um auf H200 zu kommen. Statt 2016 nimmst Du dann die Zelladresse, in der das Jahr steht. Dadurch kommst Du 2016 auf 200, 2017 auf 201 usw.

Übrigens kann man die Wechsel zwischen Sommer- und Winterzeit auch per Formel berechnen:
=DATUM(2016;4;)-REST(DATUM(2016;4;)-1;7)
=DATUM(2016;11;)-REST(DATUM(2016;11;)-1;7)
und für die ganzen Feiertage schaust Du mal in unseren Beispielbereich, ohne VBA.

Hallo Ralf,

Andre`s Antwort ist natürlich vollkommen richtig, kann aber in deiner Datei  in einigen Jahren Anpassungen benötigen.
Spätestens wenn die Leerzeilen zwischen den Ferienkalendern nicht mehr ausreichen wirst du Zeile hinzufügen.
Da die 1816 in der Funktion INDIREKT eine Konstante ist muss dieser Wert dann angepasst werden.

Ausserdem nehme ich an, dass du dieses Beispiel für die Winterferien auch für die anderen vier Ferien umsetzen willst.

Hier einige Bemerkungen:

1) Im Prinzip willst du die Werte einer Zeile eines Bereiches anhand eines Schlüssels in der ersten Spalte diese Bereiches  ermittel. Hierfür gibt es in Excel die Funktion SVERWEIS. Nach Ansicht einiger Experten in diesem Forum ist diese Funktion eine der wichtigsten Funktionen von Excel. Vielleicht kannst du ja eimal die Beschreibung hierfür durchlesen.

2) Ausserdem schreibst du in deiner Ferienliste jeden Ferientag in eine Zelle. Das ist nicht nötig. Da die Kalendertage in Excel fortlaufende Zahlen sind reicht der erste und der letzte Tag der Ferien.

3) In deiner Version prüfst du ob ein Tag zu den Winterferien gehört nur in einem kleinen Bereich des Kalenders. Ich nehme an, dass du dies auch für die anderen Ferien machst. Du must also immer wissen an welchen Tagen du welche Ferien überprüfen must.

4) Du nutzt in den Ferienlisten verbundene Zellen. Zellen Verbindung sollte meines Erachtens weitgehend vermieden werden. Über die Ausrichtung: "Zelle über Auswahl Zentrieren" bekommt man optisch das gleiche Ergebnis.

Mein Vorschlag:

1) In den Ferienlisten werden nur die ersten und letzten Tage erfasst
2) In jeweils einer (für die Winterferien zwei) seperaten Zeile werden über die Funktion SVERWEIS der erste und letzte Ferientag des aktuellen Jahres aus dem Bereich der Ferien ermittelt. (Für neue Jahre müssen die Werte innerhalb des Bereiches  erweitert werden. In Excel ab 20?? könnte man die Bereiche als Tabelle formatieren.)

3) Die Formel für die Ermittlung, ob ein Tag ein Ferientag ist, ist für alle Kalendertage gleich.

In der nächsten Antwort mein Umsetzungsvorschlag.


ps Ich weis nicht wie du Feiertage in der Zeile 4 einträgst, aber mit SVERWEIS könnte man sie, ohne sie jedes Jahr manuel anzupassen, eintragen.

Formeln in H8:CT8

=WENN(ODER(UND(H10>=$I$50;H10<=$J$50);UND(H10>=$I$61;H10<=$J$61);UND(H10>=$I$72;H10<=$J$72);UND(H10>=$I$83;H10<=$J$83);UND(H10>=$I$94;H10<=$J$94);UND(H10>=$I$95;H10<=$J$95));"F";"")

Den Aufbau der Urlaubslisten liefere ich nach, hab zur Zeit Probleme die Zellen auszulesen und hier richtig darzustellen.

Hallo,

da ich Ausscnitte nicht zusammenstellen Kann hier die Überarbeitete Datei.
Geändert habe ich nur die Ferienlisten jetzt H50:J104

und die Formel zur Urlaubsbestimmung H8:CT8

Hallo,

die Formel in H7:




Was ich nie verstehen werde ist, warum man mit Hilfszellen arbeiten muß, wenn man die Werte direkt in die Zellen schreiben kann:

Arbeitsblatt mit dem Namen 'Urlaubsliste'

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

5

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

6

Do

Fr

Sa

So

Mo

Di

Mi

Do

Fr

Sa

So

##

Di

Mi

Do

Fr

Sa

So

##

Di

Mi

Do

Fr

Sa

So

##

Di

Mi

Do

Fr

Sa

7

F

S

S

--

N

N

--

--

F

F

12

S

S

N

N

--

--

--

F

F

S

S

N

N

12

--

--

F

F

S

S

Zelle	Format	Wert
H5	T	01.01.2015
I5	T	02.01.2015
H6	TTT	01.01.2015
I6	TTT	02.01.2015

Zelle	Formel
H5	=DATUM($A$2;1;1)
I5	=H5+1
H6	=H5
I6	=I5
H7	=WAHL($C$1;WAHL(REST(H5+$F$6;28)+1;"F";"12";"S";"S";"N";"N";"--";"--";"--";"F";"F";"S";"S";"N";"N";"12";"--";"--";"F";"F";"S";"S";"--";"N";"N";"--";"--";"F");WAHL(REST(DATUM($A$2;1;H5)+$F$6;28)+1;"F";"F";"S";"S";"N";"N";"--";"--";"--";"F";"F";"S";"S";"N";"N";"N";"--";"--";"F";"F";"S";"S";"S";"N";"N";"--";"--";"F");WAHL(REST(DATUM($A$2;1;H5)+$F$6;7)+1;"--";"--";"T";"T";"T";"T";"T"))
I7	=WAHL($C$1;WAHL(REST(I5+$F$6;28)+1;"F";"12";"S";"S";"N";"N";"--";"--";"--";"F";"F";"S";"S";"N";"N";"12";"--";"--";"F";"F";"S";"S";"--";"N";"N";"--";"--";"F");WAHL(REST(DATUM($A$2;1;I5)+$F$6;28)+1;"F";"F";"S";"S";"N";"N";"--";"--";"--";"F";"F";"S";"S";"N";"N";"N";"--";"--";"F";"F";"S";"S";"S";"N";"N";"--";"--";"F");WAHL(REST(DATUM($A$2;1;I5)+$F$6;7)+1;"--";"--";"T";"T";"T";"T";"T"))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Schaltjahrberechnung: