Umrechnung KW --> Monat und umgekehrt...
Christian Kern
ich entwerfe so'ne Art Kostenrechnung und hab'in dem Zusammenhang ein
kleines Problem: Ich brauche eineFunktion für Excel 97, welche mir per
VBA-Code zu einem Monat die entsprechenden Kalenderwochen liefert (nach
DIN) und zu einer KW den Monat. Am Besten sollte auch noch die
Möglichkeit bestehen das Ganze für frei definierbare Perioden (die
Produktion plant auf Periode = 4 Wochen; nicht = 1 Monat) funktionieren.
Über die Ermittlung der Perioden werden verschiedene Kosten zugeordnet,
da die Abteilungen auf Kw und Monat (bzw. Perioden s.o.) planen.
Wer hat 'ne Idee? Für eine Lösung wäre ich echt dankbar! (nachdem
meinem Chef der tolle Kalender von Hans W. Herber nicht ausreicht ...
bzw. er nicht von Hand nach schlagen will, welche Kw zu welchem Monat
... *seuftz*)
CU Christian
Silvia Widmer
1. Es gibt die Excel-Funktion =Kalenderwoche()
2. VB-technisch geht es auch Woche = Format("01/05/99", "ww")
3. Der Algorithmus ist wählbar (Woche beginnt mit So. od. Mo. und erste
Woche im Jahr ist jene, welche mindestens 4 Tage aufweist, etc.) Kannste
unter Online-Hilfe Stichwort Format nachschlagen.
Gruss
Silvia
Robert Feldmann
hierzu schrieb Hans W. Herber am 10 Feb.
Hallo Thomas, Peter,
ich habe diesen von Microsoft stammenden Code inzwischen gegen den
intelligenteren von Michael Kremer ausgetauscht (es funktionieren
beide):
Function KWoche(d As Date) As Integer
''von Christoph Kremer, Aachen
Dim t&
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
hans
Die Funktion =Kalenderwoche(), die erst nach Installation und
Aktivierung der Analysefunktion im Add-in-Manager zur Verfügung steht,
rechnet nämlich falsch.
Öffne also mit alt+f11 die VBA Entwicklungsumgebund, und kopiere die
Funktion in ein Modul.
Christian Kern schrieb:
>
> Hi,
>
> ich entwerfe so'ne Art Kostenrechnung und hab'in dem Zusammenhang ein
> kleines Problem: Ich brauche eineFunktion für Excel 97, welche mir per
> VBA-Code zu einem Monat die entsprechenden Kalenderwochen liefert (nach
> DIN) und zu einer KW den Monat. Am Besten sollte auch noch die
> Möglichkeit bestehen das Ganze für frei definierbare Perioden (die
> Produktion plant auf Periode = 4 Wochen; nicht = 1 Monat) funktionieren.
>
Was denn nun, DIN oder Chef?
Wie dem auch sei, bei der Umwandlung von KW nach Monat muß ich passen.
Sorry.
--
bis dann,
Robert
Nicht was Du sagst ist entscheidend,
sondern was verstanden wird.
Christian Kern
Hallo Robert,
danke für eure Lösungen. Die haben mich ein ganzes Stück weiter gebracht,
aber leider noch nicht ganz bis zur gewünschten Lösung.
Ich kann jetzt zu einem Datum die Kw bestimmen, d.h. für mein Problem
"welche Kw gibt es in einem Monat" ermittle ich die KW für den 1. eines
Moants und für den letzten. Aber wie bestimme ich den letzten Tag eines
MOnats?
Das größere Problem ist für mich: Wie rechne ich die Kosten von Monat auf Kw
um, wenn ich in einer Kw 2 Monate habe? Wenn ich z.B. in einem Monat genau 4
Kw's habe teile ich die KOsten pro Monat durch 4 und lege mir für jede kw
eine Zeile in einer Tabelle an. So weit so gut (sprich dies wäre auch
richtig). Aber bei 4 Kw und eine angefangenen Kw würde ich dann durch 5
teilen, was nicht richtig wäre. Für den folgenden Monat würde ich wieder
"falsch" rechnen, da auch hier eine angefangene Kw vorhanden wäre. Dies
würde dazu führen, daß ich in meiner Tabelle 2 Datensätze für eine Kw habe,
deren Werte (=KOsten) zuhoch und beim Erstellen eines Berichts summiere ich
die Werte für den gewünschten Zeitraum auf --> Werte für die 2 halbe kws
ungefähr doppelt so hoch, wie in Wirklichkeit ...
Meine nächste Überlegung war nun einfach für nicht komplette Kws die Werte
zuhalbieren, aber
1. sind es ja nicht immer halbe Wochen und
2. ist nicht immer sicher gestellt, daß bei der ERstellung des Berichts auch
schon die Werte für den "2. Teil der Kw" vorliegen. --> Werte im Bericht
wäre nicht für eine ganze Kw und damit vergleiche ich Äpfel mit Birnen, wenn
ich die verschiedenen Kws gegenüberstelle....
Und noch ein 3. Problem:
Für einen anderen Bericht habe ich eine Kw und suche den Monat dazu. Hat
dafür jemand eine Idee?
Auch hier wieder: was mache ich, wenn eine Kw über 2 Monate geht?
Tja, Fragen über Fragen die sich da vor mir auftürmen ... oder seh' ich vor
lauter Bäumen den Wald nicht mehr...?
Für weitere Vorschläge und Lösungen dankt euch im Voraus
ein müder und grübelnder
Christian
Roger Friederich
Hi Christian,
>danke für eure Lösungen. Die haben mich ein ganzes Stück weiter gebracht,
>aber leider noch nicht ganz bis zur gewünschten Lösung.
>Ich kann jetzt zu einem Datum die Kw bestimmen, d.h. für mein Problem
>"welche Kw gibt es in einem Monat" ermittle ich die KW für den 1. eines
>Moants und für den letzten. Aber wie bestimme ich den letzten Tag eines
>MOnats?
schaue, ob Du unter EXTRAS die Analysefunktionen findest, wenn nicht,
dann EXTRAS->ADD-IN MANAGER->Analysefunktionen auswählen.
Hier ein Bsp für den letzten Tag eines Monats:
=MONATSENDE("1.3.99";0)
Wenn Du Funktion KWoche von C.Kremer verwendest, dann kannst Du z.B.
mit
=Kwoche(MONATSENDE(A1;0))-Kwoche(A1)
Die Anzahl der ganzen Wochen in einem Monat errechnen (Datum steht in
A1). Vorsicht bei Jahreswechseln!
>..
>Meine nächste Überlegung war nun einfach für nicht komplette Kws die Werte
>zuhalbieren, aber
>1. sind es ja nicht immer halbe Wochen und
>2. ist nicht immer sicher gestellt, daß bei der ERstellung des Berichts auch
>schon die Werte für den "2. Teil der Kw" vorliegen. --> Werte im Bericht
>wäre nicht für eine ganze Kw und damit vergleiche ich Äpfel mit Birnen, wenn
>ich die verschiedenen Kws gegenüberstelle....
M.E. ist das ein Kostenrechnungs- und kein Excelproblem. Hier wird Dir
keiner sagen können, wie Du rechnen willst.
Mein Vorschlag: Wie wärs mit Tagen?
>Und noch ein 3. Problem:
>Für einen anderen Bericht habe ich eine Kw und suche den Monat dazu. Hat
>dafür jemand eine Idee?
Hier eine benutzerdefinierte Funktion die das erledigt:
Function KWzuMonat(kw As Integer, j As Long) 'As Integer
Dim FirstMonday As Long
FirstMonday = DateSerial(j, 1, 1) - WeekDay(DateSerial(j, 1, 6)) + 8
KWzuMonat = Month(DateAdd("ww", kw - 1, FirstMonday))
End Function
Die Funktion ist allerdings mit Vorsicht zu geniessen. Z.B. fällt der
31.12.2001 in KW1, ist aber Monat 12.
D.h. wenn Du =KWzuMonat(1;JAHR("31.12.2001")) eingibst, erhältst Du
ein falsches Ergebnis.
Allerdings sollte das für Deine Zwecke irrelevant sein. Denn wenn Du
das Datum hättest, könntest Du den Monat gleich direkt bestimmen ...
>Auch hier wieder: was mache ich, wenn eine Kw über 2 Monate geht?
s.o.
Ciao,
Roger
Christian Kern
Hi Roger,
Vielen Dank für die Antworten. Ich muß allerdings noch einen Überfall starten ...
mir sind nämlich noch einige Sachen unklar.
> Hier ein Bsp für den letzten Tag eines Monats:
> =MONATSENDE("1.3.99";0)
>
> Wenn Du Funktion KWoche von C.Kremer verwendest, dann kannst Du z.B.
> mit
> =Kwoche(MONATSENDE(A1;0))-Kwoche(A1)
> Die Anzahl der ganzen Wochen in einem Monat errechnen (Datum steht in
> A1). Vorsicht bei Jahreswechseln!
1. Liefert die Func wirklich nur ganze Wochen?
> >Für einen anderen Bericht habe ich eine Kw und suche den Monat dazu. Hat
> >dafür jemand eine Idee?
> Hier eine benutzerdefinierte Funktion die das erledigt:
> Function KWzuMonat(kw As Integer, j As Long) 'As Integer
> Dim FirstMonday As Long
> FirstMonday = DateSerial(j, 1, 1) - WeekDay(DateSerial(j, 1, 6)) + 8
> KWzuMonat = Month(DateAdd("ww", kw - 1, FirstMonday))
> End Function
>
> Die Funktion ist allerdings mit Vorsicht zu geniessen. Z.B. fällt der
> 31.12.2001 in KW1, ist aber Monat 12.
> D.h. wenn Du =KWzuMonat(1;JAHR("31.12.2001")) eingibst, erhältst Du
> ein falsches Ergebnis.
> <schnipp>
2. Für die Umrechung von Kw auf Monate sollte ich noch bestimmen können, ob die
1. und die letzte Kw eine ganze Woche sind und wenn nicht, soll als Ergebnis der
MOnat ermittelt werden, auf welchen mehr Arbeitstage der Woche entfallen.
z.B. in der Kw 13/99 fallen 3 Arbeitstage auf den März und 2 Arbeitstage auf den
April --> Als Monat für die gesamte Kw soll der März ermittelt werden.
Im voraus schon mal Danke für sicherlich wieder so tollen und gutenAntworten!
CU Christian
Roger Friederich
>> Wenn Du Funktion KWoche von C.Kremer verwendest, dann kannst Du z.B.
>> mit
>> =Kwoche(MONATSENDE(A1;0))-Kwoche(A1)
>> Die Anzahl der ganzen Wochen in einem Monat errechnen (Datum steht in
>> A1). Vorsicht bei Jahreswechseln!
>
>1. Liefert die Func wirklich nur ganze Wochen?
Nein, tut sie nicht, war ein Fehler, sorry
>...
>2. Für die Umrechung von Kw auf Monate sollte ich noch bestimmen können, ob die
>1. und die letzte Kw eine ganze Woche sind und wenn nicht, soll als Ergebnis der
>MOnat ermittelt werden, auf welchen mehr Arbeitstage der Woche entfallen.
>z.B. in der Kw 13/99 fallen 3 Arbeitstage auf den März und 2 Arbeitstage auf den
>April --> Als Monat für die gesamte Kw soll der März ermittelt werden.
>
>Im voraus schon mal Danke für sicherlich wieder so tollen und gutenAntworten!
Hast Du's denn selbst schoneinmal versucht?
Roger
Christian Kern
> <schnipp>...
> >2. Für die Umrechung von Kw auf Monate sollte ich noch bestimmen können, ob die
> >1. und die letzte Kw eine ganze Woche sind und wenn nicht, soll als Ergebnis der
> >MOnat ermittelt werden, auf welchen mehr Arbeitstage der Woche entfallen.
> >z.B. in der Kw 13/99 fallen 3 Arbeitstage auf den März und 2 Arbeitstage auf den
> >April --> Als Monat für die gesamte Kw soll der März ermittelt werden.
> >
> >Im voraus schon mal Danke für sicherlich wieder so tollen und gutenAntworten!
>
> Hast Du's denn selbst schoneinmal versucht?
>
Na klar, ich bin aber leider nicht klar gekommen. Mit den Datum- und Zeitfunktionen
(muß ich zugeben) stehe ich ein klein wenig auf Kriegsfuß. Meine Überlegungen waren
folgende:
1. bestimmen erste und letzteWoche ganze Wochen:
dieKalenderwochen für 6 Tage nach dem 1. (bzw. maximal 6 Tage vor dem Letzten)
des Monates bestimmen, wenn die gleichen KWs wie des 1. (bzw. des Letzten) handelt es
sich um eine ganze Woche.
(Mit einer FOR-Next-Schleife?)
2. bestimmen Anzahl Arbeitstage bei nicht ganzen Wochen:
Anzahl der Tage der nicht ganzen Wochen, wären die Tage bei denen ermitttelte KW
gleich der KW des 1. bzw des Letzten (könnte ich auch in der Schleife ermitteln) Ich
weis allerdings nicht wie ich die Arbeitstage ermitteln kann bzw. prüfen ob es ich um
einen Arbeitstag handelt.
Sinngemäß hab ich an folgendes gedacht:
........
in A1 steht das DAtum).
For datum = A1 +1 to A1 +6 step 1
if not kwoche (datum) = kwoche (A1) then exit for
next
if datum - A1 >4 then monat = month (A1)
else monat = (month (A1))-1
end if
.......
Tja aber halt nur sinngemäß, ich hab es gar nicht erst versucht in VBA-Code
umzusetzen ... wie gesagt der Kriegsfuß und so ... und außerdem hat Hans W. Herber ja
schon richtig festgestellt: z.Z. seh' ich vorlauter Bäumen den Wald nicht mehr.
Gerade deswegen bin ich für so super tolle Antworten, wie ich Sie bisher auf meine
Fragen erhalten hab' auch super toll dankbar. (kein Scheiß, sonder richtig ernst
gemeint!!!)
Wer hilft mir beim Bäume fällen????
Christian
Helmut Warmann
Voraussetzungen und Vereinbarungen:
- Feiertage werden nicht berücksichtigt
- Namensfestlegung von Zellen
Zelle mit aktueller Monatszahl: aMonat
Zelle mit aktueller Jahreszahl: aJahr
- Namensfestlegung berechneter Zellen
Zelle mit Datum des Monatsersten des aktuellen Monats: Tag1
Zelle mit Datum des Monatsletzten des aktuellen Monats: Ultimo
1. Monatserster und -letzter
Tag1 = Datum(aJahr;aMonat;1)
Ultimo = MONATSENDE(Tag1;0)
2. Arbeitstage in Start-/Ultimo-Woche:
Arbeitstage der Woche des aktuellen Monats, in die der Monatserste fällt
AT_Startwoche = MAX(5-WOCHENTAG(Tag1;3);0)
Arbeitstage der Woche des aktuellen , in die der Monatsletzte fällt
AT_Endwoche = MIN(WOCHENTAG(Ultimo;3)+1;5)
2. Zuweisung der Arbeitstage der Start-/Ultimo-Woche zum (Abrechnungs-)Monat
rgMonat = wenn(AT_Startwoche>2;aMonat;aMonat-1)
rgMonat = wenn(AT_Endwoche>2;aMonat;aMonat+1)
3. Anzahl der ganzen Wochen eines Monats
nWochen =
(TAG(Ultimo)-(7-WOCHENTAG(Tag1;3)*(WOCHENTAG(Tag1;3)>0)-(WOCHENTAG(Ultimo;3)
+1)*(WOCHENTAG(Ultimo;3)<>6))/7
Gruß Helmut Warmann
Christian Kern schrieb in Nachricht <36CF51FF...@gmx.de>...
Christian Kern
Vielen Danke für deine Hilfe. Deine Zeilen haben mich wirklich weitergebracht,
auch wenn ich gestehen muß, daß ich nochnicht die Zeit hatte mein Problem
"KW/Monat" komplett zulösen. Ich hoffe mal morgen kann ich mich noch mal so
richtig "vertiefen" und kann das Problem aus der Welt schaffen. Ich berichte
aber auf jedenfall von meinen ERfahrungen bzw. von "meiner" Lösung.
CU Christian