Office: (Office 365) Aufteilung eines Zeitraums auf Kalenderwochen

Hallo zusammen,

Ich brauche mal Hilfe bei der Aufteilung eines Zeitraums auf Kalenderwochen.

Ich möchte den Zeitraum (siehe Spalte F und L im Anhang) auf die jeweiligen KW verteilen.

Klappt aber nicht, eventuell ist meine Zeitleiste dafür nicht korrekt aufgebaut.


Vielleicht gibt es ein Bsp., an das man sich orientieren kann.


Vielen Dank


Gruß

Hape

 

das ist einfach, es ist nmur ein Betrag, der kann nur in einer Woche sein.

 

Moin Hape,
bis HaJo in die Puschen kommt... kopiere diesen Code mal in ein leeres Blatt

Code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Integer, sFirst As String, sNext As String
Cancel = True

If Target.Column = 1 Then
    If Target.Row = 1 Then
'erst mal aufräumen
    Cells.Clear
    Cells(1.1) = Date
'''100 Datümer ab heute in Zeile 1 schreiben
        For i = 2 To 100
            Cells(1, i) = Cells(1, i - 1) + 1
        Next i
    End If
'''jeweils einmal pro Woche die KW in Zeile 2 schreiben
    If Target.Row = 2 Then
'ersten Vergleichswert setzen und schreiben
        sFirst = "KW " & Format(WorksheetFunction.WeekNum(Cells(1, 1)), "00")
        Cells(2, 1) = sFirst
        For i = 2 To 100
'zweiten Vergleichswert setzen
            sNext = "KW " & Format(WorksheetFunction.WeekNum(Cells(1, i)), "00")
'Vergleich sFirst / sNext
            If sNext <> sFirst Then
'in Zeile 2 schreiben und als neuen Vergleichswert setzen
                Cells(2, i) = sNext
                sFirst = sNext
            End If
        Next i
    End If
End If

End Sub

(rechte Maus auf den Tabelle1 > Code anzeigen > ein weißes Fenster für VBA) speichere die Datei als XLSM oder XLSB.
Dann erst ein Doppelklick auf Zelle A1 -> staunen und dann auf A2 --> noch mehr staunen . Ich habe den Code etwas kommentiert, was beim Verständnis / Einbau in Deinen Code helfen sollte.

Viel Spaß damit!

 

Hi
"Klappt aber nicht," ist jetzt nicht besonders aussagekräftig.
was genau macht denn deine Formel falsch und wie hättest du es denn gerne?
Gruß Daniel

 

Moin Hape,

und hier noch eine weitere KW-VBAstelei: Die Wochen zeilenweise als Tabelle mit KW xx in Spalte 1. Der Code gehört in ein Allg. Modul und wird per Alt+F8 > Make_Vertical_Weeks aufgerufen:

Code:

Option Explicit

Sub Make_Vertical_Weeks()
Dim i As Integer, r As Integer, c As Integer, a As Integer
Dim iKW As Integer, sKW As String, iDay As Integer, dDate As Date
Dim aDays: aDays = Array("Mo", "Di", "Mi", "Do", "Fr", "Sa", "So")

With ActiveSheet
 
        c = .Cells(2, .Columns.Count).End(xlToLeft).Column
        For i = 2 To c
            .Columns(i).ClearContents
        Next i
      
    If IsDate(.Cells(1, 1)) Then
        dDate = .Cells(1, 1)
    Else
        dDate = Date
    End If
    iKW = InputBox("Anzahl der Wochen", "integer")

  
    For a = LBound(aDays) To UBound(aDays)
        .Cells(1, a + 2) = aDays(a)
    Next a

a = 0
iDay = Weekday(dDate, vbMonday)
    For r = 2 To iKW + 2
        For c = iDay + 1 To 8
            .Cells(r, c) = dDate + a
            a = a + 1
        Next c
        iDay = 1
    Next r
  
sKW = "KW " & Format(WorksheetFunction.WeekNum(dDate), "00")
    .Cells(2, 1) = sKW

        For i = 3 To r - 1
            .Cells(i, 1) = "KW " & Format(WorksheetFunction.WeekNum(.Cells(i, 2)), "00")
        Next i
  
.Cells(1, 1).Activate

End With

End Sub

Der Code ist unkommentiert. Hole ich bei Bedarf gerne nach. Lass wissen !

Viel Spaß damit! und schöne Grüße

 

Hallo,

magst du mal beschreiben, was die Werte in Zeile 15 ab Spalte O besagen?

 

Hallo,

ziehe meine Rückfrage zurück, habe es selbst herausgefunden.

 

Hallo,

danke für die Rückmeldungen,
@d’r Bastler, es sollte bzw. muss erstmal ohne Code gehen.

Zur Formel:

Wenn in „ChangePeriod“ Daily ausgewählt ist, sollte der Bereich zwischen Datum Start (Spalte F) und Datum End (Spalte L) in der Time area mit „1“ abgebildet werden.

Das funktioniert auch soweit.


Wenn in „ChangePeriod“ Weekly ausgewählt ist, wird die Timeline auf Wochen umgestellt.

Dann soll die Formel, den Bereich zwischen Datum Start (Spalte F) und Datum End (Spalte L) in der Time area mit der Anzahl der Arbeitstage in der entsprechenden KW abbilden.


Bei dem Beispiel müsste für KW1 bis KW6 die Reihenfolge 3;5;5;5;5;5 erscheinen.

Das funktioniert aber nicht.


Wenn man dann mit „ScrollBar“ die Timeline verändert, sollen die Daten angepasst werden, bzw. mitwandern.


Bei ScrollBar „Daily“ funktioniert das, bei „Weekly“ verschwinden die Zahlen, wenn das Datum in Zeile11 auf ein Wochenende fällt, z. B. 04 und 05.01

Ich hoffe das hilft.

Gruß
Hape

 

Hallo Hape,

als VBA-lastiger Excelanwender kann ich mir nicht vorstellen, wie man eine solch komplexe Anforderung mit Formeln lösen könnte.

 

An dem Beispiel erkenne ich, Du ermittelst die KW nicht nach der für Europa gültigen ISO 8601 oder berücksichtigst keine Feiertage, denn die 1. KW im Jahr 2025 hat einen Feiertag (Neujahr) und somit nur 2 Arbeitstage (Do 02.01.2025 und Do 03.01.2025).




Die Feiertage sind zudem abhängig vom Bundesland. Für den Jan 25 in Bawü (und Bayern):


Fazit: Es gibt noch mehr zu tun, als Du dachtest. Ich würde die Struktur überarbeiten und das mit PQ lösen.

 

Hallo,

solange Hape sich nicht klar äußert, worum es genau geht, kann er kaum eine Lösung erwarten.
Sollen nur die Kalendertage runtergezählt werden? Geht es um Arbeitstage? Wo sind dann die Feiertage?
Bei der KW hat Hape den Parameter "2" verwendet. Das ist die amerikanische KW. Soll wohl so sein, da seine Texte oft englischsprachig sind.
Also lieber Hape, was willst du hier jetzt genau haben?

Gruß Sigi

 

Ich bin immer wieder zutiefst beeindruckt, was die Formelfüchse hier so alles bewerkstelligen. Aber in Deinem Fall ... bin ich mal sehr gespannt

Viel Erfolg!

 

Hi,

mit einer Änderung in Zeile 10:
=ISOKALENDERWOCHE(O$11) formatiert mit benutzerdef. Format "CW: "0
ginge es so:

Code:

=LET(va;$F15;vb;REST(va;7);wa;WENN(vb<2;va+2-vb;va);wb;$L15;wc;O$11;wd;O$10;
xa;ISOKALENDERWOCHE(wa);xb;ISOKALENDERWOCHE(wb);xc;REST(wa;7)-1;
xd;REST(wb;7)-1;xe;ChangePeriod;ya;--((xa>wd)+(xb<wd)=0);yb;MAX(0;wc-wa);
yc;ya*xc;
yd;ya*WENN(wd=xa;6-xc;WENN(wd=xb;xd;NETTOARBEITSTAGE.INTL(wc-xc;wc-xc+6;1)));
za;(va<=wc)*(wb>=wc)*(REST(wc;7)>1);WECHSELN(WAHL(xe;za;yd);0;""))

 

Hi,

habe vergessen zu erwähnen:

Formel in O15, nach rechts und unten ziehen, soweit benötigt.

 

Hi,

noch 'ne Verbesserung:

Code:

=LET(va;$F15:.$F25;vb;REST(va;7);wa;WENN(vb<2;va+2-vb;va);
wb;$L15:.$L25;wc;O$11:.AS$11;wd;O$10:.AS$10;
xa;ISOKALENDERWOCHE(wa);xb;ISOKALENDERWOCHE(wb);xc;REST(wa;7)-1;
xd;REST(wb;7)-1;xe;ChangePeriod;
ya;--((xa>wd)+(xb<wd)=0);yb;MAX(0;wc-wa);yc;ya*xc;
yd;ya*WENN(wd=xa;6-xc;WENN(wd=xb;xd;NETTOARBEITSTAGE.INTL(wc-xc;wc-xc+6;1)));
za;(va<=wc)*(wb>=wc)*(REST(wc;7)>1);WECHSELN(WAHL(xe;za;yd);0;""))

nur in O15 eintragen, alle Zellen rechts dvon und darunter müssen frei sein!