Excel Datum aus KW & Feldfarbe verändern durch Zahleneingabe

[d4chillin]

Hallo,

ich hab 2 Fragen:

1. Ich habe den Wochentag und die KW, kann ich mir daraus in Excel das datum des Tages anzeigen lassen ?

2. In einem Feld steht ein Text. Durch Zahleneingabe in dem Feld daneben möchte ich die Textfarbe(zur not auch Hintergrundfarbe) des Feldes mit Text verändern ?


Jemand eine Idee ? ich wär euch sehr dankbar!!!!!

Grüße

d4chillin

[Beverly]

Hi,

das 2. Problem kannst du mit Bedingter Formatierung lösen. Angenommen A1 soll in Abhängigkeit von B1 gefärbt werden: wähle unter Bedingung1 "Formel ist" aus und gib als Formel ein =B1=10 (wenn bei einem Wert von 10 gefärbt werden soll) und wähle dann unter Format das gewünschte Format aus.

[Hascheff]

Hallo,

zu 2.: wie bunt soll denn die Spalte werden und welche Excel-Version hast du?

zu 1.: Oh, das ist ja mal eine ganz neue Frage! Interessant!
@ Beverly: Tüftelst du schon? Wenn nicht, lass mich machen!

[Beverly]

@Hascheff,

Frage 1 überlasse ich dir gern. Bin nicht so der Formelspezialist.

[Hascheff]

Wie erwartet war die Lösung knifflig. Eine Schwierigkeit besteht darin, dass für die Funktionen KALENDERWOCHE und WOCHENTAG verschiedene Typen durch den zweiten Parameter möglich sind. (Woche beginnt Sonntag oder Montag, bei WOCHENTAG kann Montag außerdem 0 oder 1 sein.) Ich habe deshalb einen Korrekturfaktor eingebaut, der vom Anwender auch direkt in die Formel eingetragen werden kann.

Es gibt noch ein Problem mit dem Wochentag. Ich habe vergessen, den TO zu fragen, in welcher Form der Wochentag vorliegt. Da gibt es viele Möglichkeiten:
- Es handelt sich um ein Datum, welches als "TTT" oder "TTTT" formatiert wurde. (-> "Sa" oder "Samstag") In dem Fall ist keine Funktion nötig, man muss nur die Zelle anders formatieren.
- der Wochentag wird als Wort (Mo, Di, ... So oder Montag, Dienstag ... Sonntag) angegeben. Dies ist die wahrscheinlichste Variante. Ich habe diese Variante aber noch nicht berücksichtigt, ich komme später noch mal darauf zurück.
- Der Wochentag wird als Zahl dargestellt. Dafür gibt es, wie oben dargestellt, drei Varianten. Meine Formel geht von diesem Fall aus.

Meine Formel wurde in Excel 2000 entwickelt. Prüft bitte, ob Eure Excel-Version die Funktionen KALENDERWOCHE und WOCHENTAG auch so behandelt. Für WOCHENTAG ist der Typ-Parameter optional, Standard ist 1.

Ich habe schon daran gedacht, die Formel als benutzerdefinierte Funktion einzustellen, erst recht, wenn der Wochentag als Wort angegeben ist, dann würde ich die Umwandlung des Wortes in eine Zahl als eigenständige Funktion darstellen.
Bevor ich mich daran mache, wollte ich aber erst mal zeigen, dass es vorangeht.
@ Beverly: In der benutzerdefinierten Funktion bietet es sich an, einige Parameter optional zu stellen. Ich habe das aber noch nie probiert. Geht das bei benutzerdefinierten Funktionen überhaupt, wenn ja, wie?

Ich hänge die Excel-Datei an, mit der ich die Formel erstellt habe. Damit lässt sich manches besser verstehen, was ich im folgenden darstelle. In B7 und vielleicht auch anderswo erkennt man, dass ursprünglich mehrere Testspalten vorhanden waren.

Die Parameter der Formel sind:
B2: Kalenderwoche
B3: Wochentag
B4: Jahr
B6: Typ von WOCHENTAG
B7: Korrektur in Abhängigkeit vom Typ

Weiterhin enthält die Tabelle:
B5: Typ von KALENDERWOCHE

Die Korrektur hat folgende Größe:
Für Typ 1 (So=1 ... Sa=7) von KALENDERWOCHE:
WOCHENTAG ist Typ 1 (So=1 ... Sa=7): 1
WOCHENTAG ist Typ 2 (Mo=1 ... So=7): =WENN(B3=7;2;1)
WOCHENTAG ist Typ 3 (Mo=0 ... So=6): =WENN(B3=6;2;1)

Für Typ 2 (Mo=1 ... So=7) von KALENDERWOCHE:
WOCHENTAG ist Typ 1 (So=1 ... Sa=7): =WENN(B3=1;0;1)
WOCHENTAG ist Typ 2 (Mo=1 ... So=7): 1
WOCHENTAG ist Typ 3 (Mo=0 ... So=6): 1

Zusammengefasst: Korrektur 1 ist Standard, das liegt daran, dass in der ersten Woche null Wochen vergangen sind. Es ist unwahrscheinlich, dass in einer Tabelle verschiedene Zählweisen für den Beginn von KALENDERWOCHE und WOCHENTAG realisiert sind, aber wenn: Da je nach Variante Sonntag oder Montag dann in verschiedenen Wochen liegen, muss sieben Tage vor oder zurück gezählt werden.
Dass in der Formel unten Typ 2 und 3 von WOCHENTAG nicht unterschieden werden müssen, liegt daran, dass sich der Unterschied gleichermaßen auf den 1.1. und das gesuchte Datum auswirkt.

Nun steht in B17 die Formel:

=DATWERT("1.1."&B4)+7*(B2-B7)-WOCHENTAG(DATWERT("1.1."&B4);B6)+B3

[Beverly]

Hi Hascheff,

vom Prinzip her kann man sicher auch spezifische Parameter einbinden, denn eine benutzerdefinierte Funktion ist ja auch VBA-Code. Aber mir ist nicht so ganz klar, welche Parameter du meinst. Deine Datei kann ich leider nicht herunterladen, bzw. es wird Dateifehler beim Öffnen/Extrahieren angezeigt.

[Hascheff]

Deine Datei kann ich leider nicht herunterladen, bzw. es wird Dateifehler beim Öffnen/Extrahieren angezeigt.

Eine normale Excel2000-Datei, mit Winrar gepackt. Ich versuch es mal ohne Packen. Ich hänge einfach .txt an.

Aber mir ist nicht so ganz klar, welche Parameter du meinst.

Normale Parameter der Funktion sind Kalenderwoche und Wochentag. Das Jahr ist natürlich auch noch wichtig, aber wenn kein Jahr angegeben wird, kann man das aktuelle Jahr annehmen.
Ebenso ist es mit der Zählweise der Wochentage. Es macht einen kleinen Unterschied, ob die Funktion KALENDERWOCHE, mit der die Zahl erzeugt wurde, die Parameter der Funktion ist, die Wochenzählung am Sonntag oder am Montag beginnt.
Es muss auch angegeben werden, ob die Zahl 1 für den Wochentag Sonntag oder Montag bedeutet.
Man kann als Standard annehmen, dass die Woche in beiden Fällen am gleichen Tag beginnt. Wenn aber jemand anders zählen will oder muss, kann er das angeben.

[Beverly]

Hi Hascheff,

ach so, diese Parameter meinst du, klar. Auf welche Weise sollen sie denn übergeben werden - direkt als Konstanten in der Funktion festschreiben, sodass sie im Code ausgetauscht werden müssen oder sollen sie aus verschiedenen Zellen im Tabellenblatt entnommen werden?

Die Datei konnte ich nicht lesen, weil ich nur das in WIN integrierte Zip-Programm besitze und offensichtlich hat es Probleme mit diesem Zip-Format.

[Beverly]

Hi Hascheff,

ich bin mal jetzt davon ausgegangen, dass die Ausgangswerte in den selben Zellen stehen wie in deinem Beispiel

Private Function kwdatum()
    kwdatum = DateValue("1.1." & Range("B4")) + 7 * (Range("B2") - Range("B7")) - Weekday(DateValue("1.1." & Range("B4")), Range("B6")) + Range("B3")
End Function

Das Problem dabei ist, dass die Zelle im Anschluss erst noch als Datum formatiert werden muss, genau wie bei deiner Formel. Man könnte mit der Formel ja noch das Format anpassen

=TEXT(DATWERT("1.1."&B4)+7*(B2-B7)-WOCHENTAG(DATWERT("1.1."&B4);B6)+B3;"TT.MM.JJJJ")

was mit VBA dann so aussehen würde

Private Function kwdatum()
    kwdatum = Application.Text(DateValue("1.1." & Range("B4")) + 7 * (Range("B2") - Range("B7")) - Weekday(DateValue("1.1." & Range("B4")), Range("B6")) + Range("B3"), "dd.mm.yyyy")
End Function

Das Problem ist aber, dass ich mir nicht sicher bin, wie sich das auf irgendwelche weiteren Berechnungen auswirkt, da die Zelle auf diese Weise ja als Text formatiert ist.