Datum aus Kalenderwoche
Gerd-Wolfgang Götze
Hallo Excel-Spezialisten,
ich habe vorliegen die Kalenwoche nach DIN und das Jahr.
Nun möchte ich das Datum, z.B. vom montag dieser Woche ermitteln.
Kann mir jemand helfen?
Danke, Gerd
Peter Dorigo
Basis für die nachfolgende Berechnung bildet die mir
bekannte Definiton für Kalenderwoche 1, (für welche
ich aber keine Garantie übernehmen kann):
KW1 sei die erste Woche, beginnend mit Montag,
in welcher mindestens 4 Tage auf ein neues Jahr
entfallen. Daraus folgt, dass der 1. Donnerstag eines
Jahres immer in KW1 liegt.
=DATUM(Jahr;1;1)
liefert das Datum des 1.Januar von Jahr
=REST(5-WOCHENTAG(DATUM(Jahr;1;1));7
ermittelt den Tag des 1. Donnerstags von Jahr
=7*(KW-1)
ergibt Anzahl Tage zwischen Donnerstag von KW1 und
Donnerstag von KW
sodass die Summe der 3 Formeln:
=SUMME(DATUM(Jahr;1;1);REST(5-WOCHENTAG(DATUM(Jahr;1;1));7);7*(KW-1))
das Datum des Donnerstags von KW in Jahr liefert.
(als "serielle Zahl", Zelle ist entsprechend zu formatieren)
da der Montag 3Tg vor Do liegt, haben wir noch den
Summanden -3 in die Summenformel aufzunehmen:
=SUMME(DATUM(Jahr;1;1);REST(5-WOCHENTAG(DATUM(Jahr;1;1));7);7*(KW-1);-3)
eine allfällig mögliche Vereinfachung der Formel
möchte ich angesichts der fortgeschrittenen Uhrzeit
jemand anderem überlassen.
mfg Peter
Gerd-Wolfgang Götze schrieb in Nachricht ...
Peter Dorigo
Du hast mal wieder sehr schlampig gearbeitet!
Peter Dorigo schrieb in Nachricht ...
>
>=DATUM(Jahr;1;1)
>liefert das Datum des 1.Januar von Jahr
>
>=REST(5-WOCHENTAG(DATUM(Jahr;1;1));7
Hier hast Du 'ne Klammer vergessen:
=REST(5-WOCHENTAG(DATUM(Jahr;1;1));7)
>ermittelt den Tag des 1. Donnerstags von Jahr
wieder falsch, es muss heissen:
ermittelt Anzahl Tage zwischen 1. Jan und
1. Donnerstag von Jahr!
cu Peter
Gerd-Wolfgang Götze
Danke für Deine Mühe und Lösung.
Einen kleinen Fehler hast Du m. E. übersehen:
nicht
=7*(KW-1)
sondern
=7*KW
Nun kann ich auch die VBA-Version beitragen:
WoAnf = DateSerial(Jahr, 1, 1) + (5 - WeekDay(DateSerial(Jahr, 1, 1)))
Mod 7 + (7 * Woche) - 3
Gruß Gerd
Gerd-Wolfgang Götze
in der Formel steckt noch der Wurm.
weder =7*(KW-1) noch =7*KW ist richtig. Es gibt keine KW1.
Im ersten Fall stimmt die KW über'n Jahreswechsel im zweiten Fall stimmen
die KWs im Jahr.
Weißt Du (oder jemand and res) Abhilfe?
Gruß Gerd
Gerd-Wolfgang Götze <goe...@t-online.de> schrieb in im Newsbeitrag:
ezHomT5x#GA.98@cppssbbsa04...
Peter Dorigo
IMHO stimmt die Formel für die Berechnung eines Donnerstags für
KW1...KW52
immer (sowie für Fr, Sa und So), für die Tage vor Donnerstag ab KW2
auch immer.
Für KW1 liefert sie dann ein falsches Ergebnis, wenn der betreffende
Tag in KW1
nicht existiert. (Die erste Kalenderwoche kann nämlich uU auch ohne
Mo, Di und Mi
auskommen!). In diesem Fall ist der Rückgabewert ein Tag des Vorjahres
(29./30./31. Dez) was natürlich so nicht stimmt. Was Du als
Rückgabewert erwartest,
wenn Du einen solchen nicht existierenden Tag berechnest, weiss ich
nicht. Diesen
speziellen Fall könnte man durch Erweiterung der Formel abfangen, wenn
es nötig
sein sollte. Auch ein Spezialfall ist KW 53, hier ist ein Datum nach
dem 1.3. des
Folgejahres mit Sicherheit der KW1 des Folgejahres zuzurechnen. Auch
diese
Obergrenze könnte man wenn nötig durch Erweiterung der Formel bilden.
So wie
die Formel jetzt aufgebaut ist, könntest Du theoretisch auch ein Datum
in KW456
von Jahr berechnen!
Ich hoffe dir hiermit wenigstens einen Lösungsansatz geliefert zu
haben.
mfg Peter
Gerd-Wolfgang Götze schrieb in Nachricht
<#YfwAg6x#GA.289@cppssbbsa03>...
Karl Donaubauer
>ich habe vorliegen die Kalenwoche nach DIN und das Jahr.
>Nun möchte ich das Datum, z.B. vom montag dieser Woche ermitteln.
Schau dir mal in meiner Access-FAQ Punkt
1.18 MONTAG EINER KALENDERWOCHE an.
Funkt sicher auch im Excel-VBA-Dialekt.
HTH
Karl
**************
Access-FAQ und Info zur 2. Access-Entwickler-Konferenz (AEK) bei:
http://members.eunet.at/donkarl/
henner juengst
>ich habe vorliegen die Kalenwoche nach DIN und das Jahr.
>Nun möchte ich das Datum, z.B. vom montag dieser Woche ermitteln.
>Kann mir jemand helfen?
Hallo Gerd,
die Formel benötigt Jahr, Wochennummer und Wochentag (Mo=1..So=7)
In A1: Jahr
In B1: Wochennummer
In C1: Wochentag (1..7)
Formel in D1 (zur Berechnung des 1. Montags im Jahr, nötig für
Berechnung in E1):
=DATUM(A1-1900;1;1)+WENN(REST(DATUM(A1-1900;1;1);7)=6;3;2-REST(DATUM(A1-1900;1;1);7))
Formel in E1 das Ergebnis:
=D1+(B1-1)*7+C1-1
Format als Datum D1 + E1.
Ich hoffe es ist das was Du suchst.
Pfüati
Henner
Peter Dorigo
Habe mittlerweile neue Erkenntnisse erlangt, welche
sich mit meinen bisherigen nur zumTeil decken, das
betrifft vor allem Aussagen meines letztes Postings.
*Jede* KW hat 7Tage, beginnend mit Montag.
Ist eine Woche auf zwei Jahre verteilt so ist sie dem
Jahr zuzurechnen, in dem die Mehrzahl (4 oder mehr)
der Wochentage liegt.
Daraus ergibt sich zB, dass die ersten 3Tg von KW1
ein Datum der Vorjahres haben können.
http://www.gebr-melchert.de/Kalender/KW.htm
An der Formel ändert das allerdings nichts:
=SUMME(DATUM(Jahr;1;1);REST(5-WOCHENTAG(DATUM(Jahr;1;1));7);7*(KW-1);T
ag-4)
Dafür bedeutet es dass die Formel in jedem Fall,
in dem ihr gültige Werte übergeben werden auch
gültige Rückgaben liefert:
für Jahr 1900 ... 2074 / 1900 ... 9999 (abh von XL-Vers )
für Tag 1...7 (1->Mo.....7->So)
für KW 1...52 (oder 53) hier liegt die einzige Schwierigkeit;
einzelne Jahre umfassen 53 Wochen.
Ein Vergleich des errechneten Datums mit dem Datum
des letzten Wochentages von Jahr:
=SUMME(DATUM(Jahr+1;1;1);REST(5-WOCHENTAG(DATUM(Jahr+1;1;1));7);-4)
könnte die Gültigkeit des errechneten Datums verifizieren.
mfg Peter
Jörg Nissen
ich weiß nicht, ob Du bereits eine endgültige Lösung für Dein Problem hast.
Hier ist auf jeden Fall eine benutzerdefinierte Funktion, die Dir immer das
Datum des Montags in der gesuchten Woche zurückliefert (unter
Berücksichtigung der 52./53. KW sowie der Tatsache, daß der 1.1. noch in der
letzten KW des Vorjahres liegt).
Public Function GetKWDatum(cKW As String) As Date
Dim iWoche As Integer
Dim iResultWoche As Integer
Dim iJahr As Integer
Dim dDatum As Date
' Aufschlüsseln in Woche und Jahr
iWoche = Int(Left(cKW, InStr(1, cKW, "/") - 1))
iJahr = Int(Mid(cKW, InStr(1, cKW, "/") + 1))
' Kalenderwoche für den 1.1. des Jahres ermitteln
dDatum = DateSerial(iJahr, 1, 1)
iResultWoche = DatePart("ww", dDatum, vbMonday, vbFirstFourDays)
' Wenn die KW des 1.1. vor der gesuchten Woche liegt,
' gesuchte Woche um 1 vermindern
If iResultWoche <= iWoche Then
iWoche = iWoche - 1
End If
' Zum 1.1. des gesuchten Jahres Anzahl Tage bis zur
' gesuchten Woche addieren. Anschließend den Montag der
' Woche ermitteln
dDatum = DateAdd("d", iWoche * 7, dDatum)
dDatum = DateAdd("d", 1 - WeekDay(dDatum, vbMonday), dDatum)
GetKWDate = dDatum
End Function
Wenn Deine KW-Angabe nicht im Format "ww/jjjj" vorliegt, mußt Du die
Zuweisung auf "iWoche" und "iJahr" entsprechen anpassen.
Jörg
Gerd-Wolfgang Götze <goe...@t-online.de> schrieb in im Newsbeitrag:
#YfwAg6x#GA.289@cppssbbsa03...
Jörg Nissen
und nicht "GetKWDate =".
Jörg
Jörg Nissen <joerg....@netsurf.de> schrieb in im Newsbeitrag:
OYJnPTRy#GA.265@cppssbbsa05...
Gerd-Wolfgang Götze
ich danke Euch vielmals für Eure Mühe mir zu helfen, aber keine Eurer
Lösungen funktioniert einwandfrei.
Das Problem für jede Eurer unterschiedlichen Lösungen zu beschreiben, möchte
ich mir sparen und habe deswegen eine Excel-Mappe erstellt, in der Eure
Lösungen und deren Ergebnisse gegenübergestellt sind. Darin sind die
Probleme sofort zu erkennen.
Das Problem liegt auch an der Berechnung der KW ansich. Die KW zum
Jahreswechsel 97/98 ist z.B. die KW1 und 98/99 KW53. Damit kommt keine eurer
Lösungen zurecht.
Ich habe eine Denkblockade und bin z. Zt. nicht in der Lage selbst den Wurm
in der Berechnung zu erkennen.
Sollte jemand an der Lösung des Problems interessiert sein, bin ich gern
bereit diese Mappe zu mailen, teilt mir dazu nur die e-Mail-Adresse mit.
Also nochmals vielen Dank.
Gruß Gerd
NS: Ich wollte diese Mappe nicht dieser Nachricht anfügen. Ich glaube das
ist in der NG nicht gewünscht.
Jörg Nissen
bei meiner Lösung gibt es noch einen kleinen Bug, dann sollte Sie mit allen
Eventualitäten zurechtkommen.
Die Zeile
If iResultWoche = iWoche then
muß lauten
If iResultWoche = 1 then
Jörg
Gerd-Wolfgang Götze <goe...@t-online.de> schrieb in im Newsbeitrag:
eK0W9#dy#GA.248@cppssbbsa05...
Robert Feldmann
>
> Die Zeile
> If iResultWoche = iWoche then
Mal abgesehen davon, das ich die Zeile nicht finden kann, habe ich
alle Änderungen(09:41 und 09:45) eingebaut. Bekomme aber nur "#Wert".
Fehlt noch irgend was?
--
bis dann,
Robert
Nicht was Du sagst ist entscheidend,
sondern was verstanden wird.
henner juengst
> Hallo Peter, hallo Jörg, hallo Karl, hallo Henner,
>ich danke Euch vielmals für Eure Mühe mir zu helfen, aber keine Eurer
>Lösungen funktioniert einwandfrei.
>Das Problem für jede Eurer unterschiedlichen Lösungen zu beschreiben, möchte
>ich mir sparen und habe deswegen eine Excel-Mappe erstellt, in der Eure
>Lösungen und deren Ergebnisse gegenübergestellt sind. Darin sind die
>Probleme sofort zu erkennen.
>Das Problem liegt auch an der Berechnung der KW ansich. Die KW zum
>Jahreswechsel 97/98 ist z.B. die KW1 und 98/99 KW53. Damit kommt keine eurer
>Lösungen zurecht.
>Ich habe eine Denkblockade und bin z. Zt. nicht in der Lage selbst den Wurm
>in der Berechnung zu erkennen.
>Sollte jemand an der Lösung des Problems interessiert sein, bin ich gern
>bereit diese Mappe zu mailen, teilt mir dazu nur die e-Mail-Adresse mit.
>Also nochmals vielen Dank.
Hallo Gerd,
vielleicht hängst Du ander KW, die Funktion von Excel funktioniert
nicht in DIN-Land. Versuch folgende benutzerdefinierte Funktion 'von:
"c.i.c.i" <c.i...@gmx.net>
'===========================
Function KWoche(d As Date)
Dim t As Long
t = DateSerial(Year(d + (8 - WeekDay(d)) Mod 7 - 3), 1, 1)
KWoche = ((d - t - 3 + (WeekDay(t) + 1) Mod 7)) \ 7 + 1
End Function
'===========================
Bei mir stimmt die Berechnung:
A B C D E F
Jahr WN WT 1. Mo 1. d.1. W WN d.1.1.A
1990 1 1 Mo 1. Mo 1. 1
1991 1 1 Mo 31. Mo 31. 1
1992 1 1 Mo 30. Mo 30. 1
1993 1 1 Mo 4. Mo 4. 53
1994 1 1 Mo 3. Mo 3. 52
1995 1 1 Mo 2. Mo 2. 52
1996 1 1 Mo 1. Mo 1. 1
1997 1 1 Mo 30. Mo 30. 1
1998 1 1 Mo 29. Mo 29. 1
1999 1 1 Mo 4. Mo 4. 53
2000 1 1 Mo 3. Mo 3. 52
Schick mir mal Dein Problem.
Pfüati
Henner
Marco Amistadi
> Excel 97
> Hallo Excel-Spezialisten,
> ich habe vorliegen die Kalenwoche nach DIN und das Jahr.
> Nun möchte ich das Datum, z.B. vom montag dieser Woche ermitteln.
> Kann mir jemand helfen?
>
> Danke, Gerd
Hallo Gerd-Wolfgang,
ich habe mir auch mal Gedanken um Dein Problem gemacht.
Hier mal mein Vorschlag:
Ich suche mir den ersten Montag im Jahr (was ja gegeben ist).
Dann kann ich ja schauen, ob das der 3., 4. oder 5. Tag ist. Wie
das hier mit der DIN-Norm aussieht, weiß ich auch nicht, aber
Du kannst ja das Makro anpassen. Nun gehe ich einfach die
Wochen nach oben. Du kannst das Ganze auch 'nur' als
Denkanstoss nehmen.
Ach ja, die Funktionen 'Zeller', 'Schaltjahr' und 'Tagesnummer'
sind nicht auf meinem Mist gewachsten. Hab sie mir aus Büchern
und Zeitungen zusammengesammelt.
Nun hier das Makro:
Sub testy()
Dim Dat As Date
Dat = DatumFinden(52, 1999)
End Sub
Function DatumFinden(KW%, Jahr%) As Date
Dim intWT%, strWT$, Tag%, Dat As Date, intKW
Tag = 0
Do
Tag = Tag + 1
intWT = Zeller(Jahr, Tagesnummer(Jahr, 1, Tag))
Loop Until intWT = 1
'Hier noch nach DIN anpassen!!
If Tag <= 4 Then intKW = 1 Else intKW = 2
Dat = DateSerial(Year:=Jahr, Month:=1, Day:=Tag)
Do Until KW = intKW
Dat = Dat + TimeSerial(Hour:=7 * 24, Minute:=0, Second:=0)
intKW = intKW + 1
Loop
DatumFinden = Dat
End Function
Function Zeller(JA%, Tn%) As Integer
N = Tn - 1
J = Int((JA% - 1) Mod 100)
c = Int((JA% - 1) / 100)
Zeller = Int((28 + J + N + (J / 4) + (c / 4) + 5 * c)) Mod 7
End Function
Function Schaltjahr(JA%) As Integer
Dim J1%, J2%, J3%
J1 = ((JA Mod 4) = 0)
J2 = ((JA Mod 100) = 0)
J3 = ((JA Mod 400) = 0)
If J1 = True And J2 = False Or J1 = True And J3 = True Then
Schaltjahr = 1 Else Schaltjahr = 0
End Function
Function Tagesnummer(JA%, M%, T%) As Integer
D = Int((M + 10) / 13)
e = Int(T + (611 * (M + 2)) / 20 - 2 * D - 91)
Tagesnummer = e + Schaltjahr(JA%) * D
End Function
Ich hoffe, es ist verständlich, ansonsten einfach mal melden.
Gruß
Marco
Marco Amistadi
wenn ich diese News nun zum 2. mal geschickt habe, SORRY.
Ich habe sie nur nach 10 Std. nicht gefunden und dachte mir,
sie wäre verlorengegangen.
Marco
Marco Amistadi schrieb:
Andreas Hensen
wie wäre es mit dieser Loesung:
Function KW(d, j)
' Umrechnung von Kalenderwoche und Jahr in ein Datum
' erster Parameter ist die Kalenderwoche
' zweiter Parameter ist das jahr
' Rückgabewert ist der Montag der übergebenen KW
Dim b As Integer
If d > 53 Then
KW = ""
Exit Function
End If
b = WeekDay(DateSerial(j, 1, 1))
If d = 1 Then
If b = 1 Then
KW = DateSerial(j, 1, 2)
Else
KW = DateSerial(j, 1, 1)
End If
Else
If b < 6 Then
KW = DateSerial(j, 1, 1) + ((d - 2) * 7) + 9 - b
Else
KW = DateSerial(j, 1, 1) + ((d - 1) * 7) + 9 - b
End If
End If
If year(KW) > j Then KW = ""
End Function
Sub Test()
MsgBox KW(10, 2000)
End Sub
Gruss
Andreas
--
MfG
Andreas Hensen
________________________________________________________________
Andreas Hensen mailto:andreas...@quantum.de
SER Quantum GmbH Tel. (0231) 97 49 - 259
Fax (0231) 97 49 - 3
... gut, dass wir verglichen haben !
Gerd-Wolfgang Götze
bin erst jetzt dazugekommen Deine Formel konkret anzuwenden.
Als Formel funktioniert sie einwandfrei. Danke für Deine Hilfe.
Gruß Gerd