Office: (Office 2003) Probleme mehr als 5 Wochentag Funktionen in einer Formel

Hallo !

Ich bin verzweifelt. Folgende Formel funktioniert (in L6 steht das Jahr (2020) und in L7 steht der Monat (Ziffer 1-12) :

=WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=1;MONAT(DATUM(L6;L7;29))=L7);DATUM(L6;L7;29);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=2;MONAT(DATUM(L6;L7;28))=L7);DATUM(L6;L7;28);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=3;MONAT(DATUM(L6;L7;27))=L7);DATUM(L6;L7;27);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=4;MONAT(DATUM(L6;L7;26))=L7);DATUM(L6;L7;26);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=5;MONAT(DATUM(L6;L7;25))=L7);DATUM(L6;L7;25);"")))))

Ich möchte aber insgesamt 7 Wochentage abfragen
Allerdings geht schon folgende Formel nicht mehr:

=WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=1;MONAT(DATUM(L6;L7;29))=L7);DATUM(L6;L7;29);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=2;MONAT(DATUM(L6;L7;28))=L7);DATUM(L6;L7;28);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=3;MONAT(DATUM(L6;L7;27))=L7);DATUM(L6;L7;27);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=4;MONAT(DATUM(L6;L7;26))=L7);DATUM(L6;L7;26);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=5;MONAT(DATUM(L6;L7;25))=L7);DATUM(L6;L7;25);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=6;MONAT(DATUM(L6;L7;24))=L7);DATUM(L6;L7;24);""))))))

wenn ich statt dessen aber

=WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=1;MONAT(DATUM(L6;L7;29))=L7);DATUM(L6;L7;29);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=2;MONAT(DATUM(L6;L7;28))=L7);DATUM(L6;L7;28);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=3;MONAT(DATUM(L6;L7;27))=L7);DATUM(L6;L7;27);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=4;MONAT(DATUM(L6;L7;26))=L7);DATUM(L6;L7;26);
WENN(UND(
WOCHENTAG(DATUM(L6;L7;1);2)=5;MONAT(DATUM(L6;L7;25))=L7);DATUM(L6;L7;25);
WENN(UND(
2+2=4;4+4=8);"666";""))))))

schreibe, geht es wieder

Wo liegt der Fehler ?



Danke

Dirk

 

Hola,

beschreib doch mal was du eigentlich vorhast - vielleicht gehts einfacher/kürzer. "Geht nicht" ist übrigens nicht hilfreich.

Gruß,
steve1da

 

Zunächst mal möchte ich wissen, warum das nicht geht ? :-)

Bei 5 Abfragen klappt es, ab der 6. nicht - das erschließt sich mir nicht

Excel sagt mir dann "Die Formel enthält einen Fehler" usw.

 

Hola,

"warum das nicht geht" ist, sorry, genauso hilfreich wie "klappt nicht".
Was passiert denn, oder was passiert nicht?

Gruß,
steve1da

 

Ich kann die 2. Formel gar nicht eingeben. Es kommt sofort die Fehlermeldung "Die eingegebene Formel enthält einen Fehler"-

Wenn ich die letzte Zeile wie in 3 Beschrieben eingebe funktioniert es

 

Hola,

die Formel an sich erzeugt von der Syntax her keinen Fehler bei mir.

Gruß,
steve1da

 

Hmm, ich dreh' noch durch .. :-)

Was mache ich denn falsch ?
Ich dachte an zu viele WENN Abfragen, deswegen die abgeänderte Zeile, aber mit der klappt es ja

Verwendest Du denn Excel 2003 ?

 

Also folgende Ausgangssituation:

Wir verwenden eine Formular zur Erfassung der Arbeitsstunden. Das Layout soll nicht verändert werden.
Jeder Monat ist ein Arbeitsblatt
Dort stehen untereinander die Wochentage
Montag
Dienstag
Mittwoch
Donnerstag
Freitag
Samstag 1-3
Samstag ab 4
Sonntag
Montag
Dienstag
Mittwoch
Donnerstag
Freitag
Samstag 1-3
Samstag ab 4
Sonntag
Montag
Dienstag
Mittwoch
Donnerstag
Freitag
Samstag 1-3
Samstag ab 4
Sonntag
Montag
Dienstag
Mittwoch
Donnerstag
Freitag
Samstag 1-3
Samstag ab 4
Sonntag
Montag
Dienstag
Mittwoch
Donnerstag
Freitag
Samstag 1-3
Samstag ab 4
Sonntag
Montag
Dienstag

Die Tage werden dann passend eingetragen. Wenn der 1. ein Montag ist, dann beim ersten Montag, wenn der 1. ein Dienstag ist, dann beim ersten Dienstag, usw.
Das wurde bisher manuell gemacht.
Jetzt wollte ich das dynamisch machen.
Wenn ein Monat nun aber weniger als 31 Tage hat, soll er die Zelle leer lassen.
Für die erste Woche war meine Lösung (Beispiel Sonntag)
=WENN(
WOCHENTAG(DATUM(L6;L7;1);2)=1;DATUM(L6;L7;7);
WENN(
WOCHENTAG(DATUM(L6;L7;1);2)=2;DATUM(L6;L7;6);
WENN(
WOCHENTAG(DATUM(L6;L7;1);2)=3;DATUM(L6;L7;5);
WENN(
WOCHENTAG(DATUM(L6;L7;1);2)=4;DATUM(L6;L7;4);
WENN(
WOCHENTAG(DATUM(L6;L7;1);2)=5;DATUM(L6;L7;3);
WENN(
WOCHENTAG(DATUM(L6;L7;1);2)=6;DATUM(L6;L7;2);DATUM(L6;L7;1)))))))

Das geht auch soweit ganz gut, aber Excel zählt die Tage ja einfach weiter, auch wenn der Monat zu ende ist.
Das wollte ich dann mit der obigen Formel verhindern

Klar soweit ? :-)

 

Hola,

sorry, nicht klar soweit Captn Sparrow :)
Hast du eine Beispieldatei?

Gruß,
steve1da

 

Hallo,

kann es sein, dass du eine Formel haben möchtest, die das Datum des 1. Sonntags im Monat liefern soll, wenn in L6 das Jahr und in L7 der Monat stehen?
Diese Formel lautet:
=DATUM(L6;L7;7)-WOCHENTAG(DATUM(L6;L7;7))+1

 

Folgende Tabelle möchte ich dynamisch machen

Das Problem fängt bei B34 an.

Meine eingangs genannte Formel wird nicht akzeptiert.

Warum ?


Den Anhang Überstunden - Test.xls betrachten

 

Hallo,

ich nehme an, dass diese Formeln funktionieren sollten, insbesondere sollten sie auch die Schaltjahre richtig darstellen:

 

So einfach, ja ?
Ups...

Danke

Könntest Du mir bitte die Formel in B9 erklären ?

 

Hallo,

die Formel in Zelle B9 lautet:
=DATUM(L6;L7;7)-WOCHENTAG(DATUM(L6;L7;7))+1

In ihr kommt zweimal DATUM(L6;L7;7) vor. Damit wird das laufende Datum des 7. Tages des L7. Monats des Jahres L6 ermittelt.

Die Datum/Zeit-Einheit in den Office-Programmen ist 1 Tag. Jedem Tag wird eine eindeutige natürliche Zahl zugeordnet. Dabei ist der Tag mit der Zahl 1 (fortlaufendes Datum =1 ) der 1.Jan.1900. Der 2.Jan.1900 hat demnach als fortlaufendes Datum die Zahl 2, der 3.Jan.1900 als fortlaufendes Datum die Zahl 3, usw. Zählt man auf diese Weise weiter, kommt man beim 1.Feb.2021 auf die Zahl 44228 als fortlaufendes Datum und beim 7.Feb.2021 auf 44234.
In Form dieser fortlaufenden Datumszahlen speichert daher auch Excel die Datumsangaben in seinen Zellen. Hat man eine Zelle, in der die Zahl 44234 gespeichert ist, kann man sich diese Zahl durch Eingabe der Formatierung "TT.MMM.JJJJ" in diese Zelle als 07.Feb.2021 anzeigen lassen oder mit der Formatierung "T.M.JJJJ" als "7.2.2021" oder aber mit der Formatierung "TT" als "07" usw.

Ferner kommt die Funktion WOCHENTAG(FortlaufendesDatum) vor: Diese Funktion ermittelt anhand des fortlaufenden Datums eine Wochentagszahl, wobei der Sonntag jahrtausendelang der 1. Tag der Woche war und der Samstag (=Schabbat) der 7. Tag. Daher liefert diese Funktion folgende Werte als Funktionsresultate: 1=Sonntag, 2=Montag, 3=Dienstag, ..., 6=Freitag, 7=Samstag.

Der Formelausdruck WOCHENTAG(DATUM(L6;L7;7)) liefert daher die Wochentagszahl des 7.Tages des L7.Monats des Jahres L6. Geht man vom 7.2.2021 aus, so entspricht diesem Datum die fortlaufende Datumszahl 44234 (diese wird durch DATUM(2021;2;7)) ermittelt) und aus WOCHENTAG(44234) ergibt sich die Zahl 1, d.h. es handelt sich beim 7.2.2021 um einen Sonntag.

Die gesamte Formel ergibt demnach für den 7.2.2021 folgenden Ausdruck: =44234-1+1 , was wieder 44234 ergibt. Somit handelt es sich um den 1. Sonntag im Feb.2021, denn der Sonntag davor hat als fortlaufendes Datum 44234-7 = 44227, was als Datum formatiert den 31.1.2021 ergibt.

Nimmt man als Datum den 7.1.2021 (L6=2021; L7=1) . Diesem entspricht die fortlaufende Zahl 44203 und die Wochentagszahl 5 (=Donnerstag). Die Formelberechnung ergibt =44203-5+1 = 44199 und diese fortlaufende Zahl entspricht dem 3.1.2021 als Datum und das ist wieder ein Sonntag. Das muss wieder der 1. Sontag im Jan.2021 sein (denn einen noch früheren kann es dann im Jan.2021 nicht geben).

 

#10 gekürzt:
=KÜRZEN(DATUM(L6;L7;6)/7)*7+1

Hat evtl. nix mit dem weiteren zu tun, aber bekanntes KÜRZEN muss dann doch durchgesetzt werden, um die Zahl gleicher Eingangsparameter zu cutten.