Office: fortlaufendes Datum jeden Montags ohne Ferien

Hallo, ich will eine wöchentliche Anwesenheitsliste erstellen, Spalte A die Namen, die weiteren Zeilenköpfe automatisch gefüllt mit dem mit dem Datum des jeweiligen Wochentags (also eine Liste für Montag z. Bsp.). Die Formel von "Excelformeln.de" "=A1+(WOCHENTAG(A1;2)>5)*7+(5-WOCHENTAG(A1;2))" ist prima dafür. Nun hat mein A4-Blatt quer nicht soviele Spalten, dass ich die Ferien(Mon)tage auslassen möchte. Die Arbeitstagsformel von Excel "=ARBEITSTAG(B4;5;$A$1:$AV$10)"erlaubt die Eingabe von Ausnahmen, berechnet mir aber den nächsten Arbeitstag und nicht den nächsten Montag nach den Ferien.
Hat einer von euch eine Idee, wie man beide Formeln entsprechend mixen kann, bzw. eine VBA-freie Lösung?
Ach so, die gesamten Ferientage habe ich in einem anderen Blatt hintereinander in einer Zeile erfasst.
Ein kleines Danke schon mal!
Jens

:)

 

Hallo
Diese Formel stammt von Wilfried Höttl.

Tabelle1

	A	B	C	D	E	F	G	H	I	J
1	01.01.2010	<--erste d.J.
2		04.01.2010	11.01.2010	01.02.2010	08.02.2010	15.02.2010	22.02.2010	01.03.2010	08.03.2010	15.03.2010

verwendete Formeln
Zelle	Formel	Bereich
B2	{=A1+MIN(WENN((WOCHENTAG(A1+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(A1+ZEILE(:); B64:B73;0)); ZEILE(:)))}	$B
C2	{=B2+MIN(WENN((WOCHENTAG(B2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(B2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$C
D2	{=C2+MIN(WENN((WOCHENTAG(C2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(C2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$D
E2	{=D2+MIN(WENN((WOCHENTAG(D2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(D2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$E
F2	{=E2+MIN(WENN((WOCHENTAG(E2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(E2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$F
G2	{=F2+MIN(WENN((WOCHENTAG(F2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(F2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$G
H2	{=G2+MIN(WENN((WOCHENTAG(G2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(G2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$H
I2	{=H2+MIN(WENN((WOCHENTAG(H2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(H2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$I
J2	{=I2+MIN(WENN((WOCHENTAG(I2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(I2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$J

{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 5.30

gruss hary

 

Moin
gerade kleinen Fehler entdeckt.
in B2 muss die Formel lauten:

Tabelle1

	B
2	04.01.2010

verwendete Formeln
Zelle	Formel	Bereich
B2	{=A1+MIN(WENN((WOCHENTAG(A1+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(A1+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$B

{} Matrixformel mit Strg+Umschalt+Enter abschließen

 

Hallo Hary,

Nein! Das geht nur, wenn das Startdatum in der gleichen Zeile steht, also hier in A2, da ansonsten immer Bezug auf die vorhergehende Zeile genommen wird, in der nichts steht.

Arbeitsblatt mit dem Namen 'Tabelle2'
*	A	B	C
2	01.01.2010	15.02.2010	22.02.2010

Zelle	Formel
B2	{=A2+MIN(WENN((WOCHENTAG(A2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(A2+ZEILE(:); Tabelle3!$B:$B;0)); ZEILE(:)))}
C2	{=B2+MIN(WENN((WOCHENTAG(B2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(B2+ZEILE(:); Tabelle3!$B:$B;0)); ZEILE(:)))}

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

 

Moin Edgar
Hast Recht.
In Prinzip wollte ich eigentlich auch nur schreiben, dass der Bezug auf Tabelle2 in der Formel B2 fehlte/fehlerhaft war.
Aber morgens um 6°° ....... ;-))
schoenen Sonntag
gruss hary

 

Hallo
war dennoch ein Fehler drin. Wenn der erste im Monat ein Montag ist.
jetzt richtig.
Frage: gehts evtl noch einfacher????

Tabelle1

	A	B	C	D	E	F	G	H	I	J
1	01.11.2010	<--erste d.J.
2		01.11.10	08.11.2010	15.11.2010	22.11.2010	29.11.2010	06.12.2010	13.12.2010	20.12.2010	27.12.2010
3
4
5	november	2010	Monatsname als Text, Anzeige nur im betr. Monat
6
7		01.11.2010	08.11.2010	15.11.2010	22.11.2010	29.11.2010
8

verbundene Zellen

C5:F5

verwendete Formeln
Zelle	Formel	Bereich
B2	{=WENN(WOCHENTAG(A1;2)=1;A1;(A1+MIN(WENN((WOCHENTAG(A1+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(A1+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))))}	$B
C2	{=B2+MIN(WENN((WOCHENTAG(B2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(B2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$C
D2	{=C2+MIN(WENN((WOCHENTAG(C2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(C2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$D
E2	{=D2+MIN(WENN((WOCHENTAG(D2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(D2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$E
F2	{=E2+MIN(WENN((WOCHENTAG(E2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(E2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$F
G2	{=F2+MIN(WENN((WOCHENTAG(F2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(F2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$G
H2	{=G2+MIN(WENN((WOCHENTAG(G2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(G2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$H
I2	{=H2+MIN(WENN((WOCHENTAG(H2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(H2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$I
J2	{=I2+MIN(WENN((WOCHENTAG(I2+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(I2+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:)))}	$J
B7	{=WENN(WOCHENTAG(DATUM(B5;MONAT(A5&1);1);2)=1;DATUM(B5;MONAT(A5&1);1);DATUM(B5;M ONAT(A5&1);1)+MIN(WENN((WOCHENTAG(DATUM(B5;MONAT(A5&1);1)+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(DATUM(B5;MONAT(A5&1);1)+ZEILE(:); Tabelle2!$B:$B;0)); ZEILE(:))))}	$B
C7	{=WENN(B7="";"";WENN(MONAT(B7+MIN(WENN((WOCHENTAG(B7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(B7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))> MONAT(B7);"";B7+MIN(WENN((WOCHENTAG(B7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(B7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))) }	$C
D7	{=WENN(C7="";"";WENN(MONAT(C7+MIN(WENN((WOCHENTAG(C7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(C7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))> MONAT(C7);"";C7+MIN(WENN((WOCHENTAG(C7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(C7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))) }	$D
E7	{=WENN(D7="";"";WENN(MONAT(D7+MIN(WENN((WOCHENTAG(D7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(D7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))> MONAT(D7);"";D7+MIN(WENN((WOCHENTAG(D7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(D7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))) }	$E
F7	{=WENN(E7="";"";WENN(MONAT(E7+MIN(WENN((WOCHENTAG(E7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(E7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))> MONAT(E7);"";E7+MIN(WENN((WOCHENTAG(E7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(E7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))) }	$F
G7	{=WENN(F7="";"";WENN(MONAT(F7+MIN(WENN((WOCHENTAG(F7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(F7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))> MONAT(F7);"";F7+MIN(WENN((WOCHENTAG(F7+ZEILE(:)-1)=1)*ISTNV(VERGLEICH(F7+ZEILE(:);Tabelle2!$B:$B;0));ZEILE(:))))) }	$G

{} Matrixformel mit Strg+Umschalt+Enter abschließen

Füllfarben und Muster
Zelle	Rot	Grün	Blau	Color	Muster	Farbe
B1	0	176	240	15773696
C5	0	176	240	15773696

Zellen mit Füllfarbe automatisch werden nicht dargestellt

 

Hallo ihr beiden!
Danke für euren konstruktiven "Streit". Eine geniale Lösung so ohne VBA. Wie kommt man nur auf solche Formeln?!? Das Ausgangsdatum habe ich mit in der Ferientabelle erfasst.
A1 verweist auf diese Zelle, die Formel habe ich in meine erste Zeile geschoben. Funktioniert, die Ferientage bleiben "draußen".

Schönes Restwochenende!
Jens

 

Na toll,
meine Antwort sollte eigentlich auf Edgars Beitrag folgen. Nun hat hary sich nochmal dazwischen "geschummelt". Was baut ihr da zusammen - laienhaft gefragt?

Jens

 

Moin Jens

*Laienhaft* gesagt: was Du wolltest ;-)))))
Der zweite Teil in meiner Tabelle ist eigentlich nur fuer mich.
wegen:

Wollte nur testen wie man bei Texteingabe des Monats, die Montage fuer den betr. Monat angezeigt bekommt.
schoenes Rest WE
gruss hary
Bin selbst Laie und uebe.

 

Darf ich dieses Problem in diesem Thread noch schildern?

Ein nervendes Problem tritt auch bei Excel2010 auf. Wenn ich eine Platzierung ermitteln will "=RANG(A2;$A$2:$A$100;1)" und innerhalb des Bereiches $A$2-$A$100 einige Zeilen (z.Bsp. 5) löschen muß, dann kürzt Excel diesen Bereich auch in der Formel um die gelöschten Zellen RANG(A2;$A$2:$A$95;1). Brauche ich mal wieder mehr Zeilen, dann muß die Rangformel wieder erweitert werden, um alle Platzierungen zu erfassen. Kann man den gewünschten Bereich in der Formel nicht festnageln?

Die Rangformel brauche ich in meiner Anwesenheitstabelle.

 

Wieder gekreuzte Postings. Ja ne is klar, mein Datumsproblem habt ihr beiden bestens gelöst - DANKE!!
Das mit der Monatssache werde ich mal im Hinterkopf behalten. Vielleicht schaffe ich es allein, die Formel "verstummen" zu lassen, wenn der letzte Schultag erreicht ist - sprich: die letzten Spaltenköpfe bleiben leer/weiß*wink.gif*

Jens

 

Hallo
nimm:
INDIREKT("$A$2:$A$100")
gruss hary

 

Hallo hary, ...
ein jahrzehntelanges Problem in meiner Wettkampfberechnung als Nebenproblem meiner Anwesenheitsliste gelöst.
Ich bin zu Tränen gerührt und stinksauer, weil es so einfach ist, dass selbst ich hätte darauf kommen können.
Jens

 

Hallo,

meine Standardaussage dazu ist: solange die Zellen irgendwo in den Bereich eingefügt werden, kein Problem, da Excel den Bereich dann wieder erweitert.
Ansonsten den Bereich mit Namen versehen, funktioniert aber auch nur, wenn keine Zellen angehängt werden.

Statt indirekt: Index(A:A;2):Index(A:A;100)
ist zwar etwas länger, aber nicht volatil!

 

Da fehlt mir doch so einiges an Wissen... Die Indirekt-Version verstehe ich noch: Mein Bereich wird in Text umgewandelt und Excel kann zwischen den "" nix ändern. Die Index-Variante ist sicherlich die math. korrekte Version.
Jens