Zählen wenn Pausen unterschritten sind

[Schrader]

Hallo zusammen

ich habe eine etwas "größere" Datei, wo Als Jahresplanung für mehrere Monteure dient.

Hier ist auch hauptsächlich der Notdienst hinterlegt.

Wie bekomme ich es hin das Excel mir die freien Wochen zählt  und sobald summe "X" unterschritten wird immer +1 zählt

Also es müssen min 3 Wochen (21Tage/zellen) zwischen jedem Notdienst (7Tage /Zellen) sein (ND, FND, HND) Sobald dies unterschritten wird z.B. nur 20 Tage.
Dann soll in einer Anderen Zelle  immer 1 hochgezählt werden pro Verstoß, damit ich gleich sehe ob die Pausen eingehalten werden

Ein kleiner Auszug hänge ich als Datei an
[
Bild bitte so als Datei hochladen: Klick mich!
]

[UweD]

Hallo

in ein Normales Modul.

Code:

    Dim LR As Long, Z As Long, Z1 As Integer, TB1 As Worksheet, TB2 As Worksheet
    Dim S As Integer, LC As Integer, Erster As Date, tmp As Boolean
       
    Set TB1 = Sheets("Tabelle1")
    Set TB2 = Sheets.Add(after:=Sheets(Sheets.Count))
    Z1 = 6 'Erste Zeile mit Datum
   
    With TB1
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte einer Zeile
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
   
        'Überschrift kopieren
        .Cells(1, 2).Resize(1, LC - 1).Copy TB2.Cells(1, 2)
       
        For S = 2 To LC
            For Z = Z1 To LR
                If Right(.Cells(Z, S), 2) = "ND" Then
                    If Not tmp Then 'fürs erste mal
                        Erster = .Cells(Z, 1) 'erster Einsatztag
                        tmp = True
                    Else
                        If .Cells(Z, 1) - Erster < 21 Then
                            TB2.Cells(2, S) = TB2.Cells(2, S) + 1 'Hochzählen
                       
                        Else
                            Erster = .Cells(Z, 1) ' Neuer Erster Tag
                        End If
                    End If
                    Z = Z + 6
                End If
            Next Z
            tmp = False
        Next S
    End With
End Sub

LG UweD

[Schrader]

Hallo uwe D

danke für deine Antwort, leide kenne ich mit  Makro/VBA , so gut wie garnicht aus. Also ich kann es erstellen, verstehe aber nicht zu 100% die Code`s

Ich habe dien Code mal verwendet, aber irgendwie funktioniert er nicht richtig.
Nehmen wir mal den Monteur Engelhartd(Spalte G) , ich selbst Zähle 3x verstöße , aber der Code zählt nur 1x

Und sobald ich im Code "ND" verändere z.B. SND oder FND . zählt er garnicht mehr , obwohl diese in Tabelle 1 auftauchen .
bei "ND" zählt er alle Zellen wo ND vorkommt also auch FND usw?
Ignoriert der Code den Inhalt einer Zelle wie U und GLZ?

Ich habe die Datei mit deinem Code mal hochgeladen.

[Rudi'S]

Hallo,

ich habe mir erlaubt den Code von Uwe etwas zu ergänzen.
Da der Code immer auf den 1.Tag der Arbeitsperiode gesetzt ist, muss demnach auch eine Zeit von 28 Tagen angesetzt werden.

Zum Anderen habe ich die Suchkriterien zusammengefasst (also die beiden rechten Buchstaben "LZ" und "ND").

Jetzt sollte es zu deiner Zufriedenheit laufen.

Vielleicht macht es Sinn die Berechnung etwas umzustellen, weil deine Vorgabe mit 7 Einsatztagen stimmt nicht in jedem Fall. Und dafür ist die Formel angelegt. Evtl. Umstellung auf den letzten Einsatztag.

Gruß Rudi

[UweD]

Und sobald ich im Code "ND" verändere z.B. SND oder FND . zählt er garnicht mehr , obwohl diese in Tabelle 1 auftauchen .
bei "ND" zählt er alle Zellen wo ND vorkommt also auch FND usw?

Laut erstem Post sollen Notdienste berücksichtigt werden. Ich bin davon ausgegangen, das das ND dieses bedeutet
Der Code nimmt die 2 rechten Zeichen  also immer ND (bei SND, HND und FND)
Alle anderen werden ignoriert. 
War doch so gewollt oder? 

Nehmen wir mal den Monteur Engelhartd (Spalte G) , ich selbst Zähle 3x verstöße , aber der Code zählt nur 1x

1. Notdienst  04.02. bis 10.02.
2. Notdienst  18.02. bis 24.02. zum Vorgänger nur 7 Tage, also Fehler
3. Notdienst  18.03. bis 24.03. zum Vorgänger genau 21, Tage also OK
4. Notdienst  15.04. bis 21.04. zum Vorgänger genau 21, Tage also OK
5. Notdienst  27.05. bis 02.06. zum Vorgänger 28, Tage also OK
6. Notdienst  18.06. bis 23.06. zum Vorgänger nur 14 Tage, also Fehler


Ich habe das Makro nochmal angepasst und es kommt jetzt 2 heraus.

Code:

Option Explicit

Sub test()
    Dim LR As Long, Z As Long, Z1 As Integer, TB1 As Worksheet, TB2 As Worksheet
    Dim S As Integer, LC As Integer, Letzter As Date, Anz As Integer
      
    Set TB1 = Sheets("Tabelle1")
    Set TB2 = Sheets.Add(after:=Sheets(Sheets.Count))
    Z1 = 6 'Erste Zeile mit Datum
  
    With TB1
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte einer Zeile
       
        'Überschrift kopieren
        .Cells(1, 2).Resize(1, LC - 1).Copy TB2.Cells(1, 2)
      
        For S = 2 To LC
            LR = .Cells(.Rows.Count, S).End(xlUp).Row 'letzte Zeile der Spalte
              
            For Z = Z1 To LR
                If Right(.Cells(Z + 1, S), 2) <> "ND" And Right(.Cells(Z, S), 2) = "ND" Then
                    Letzter = .Cells(Z, 1)
                End If
               
                If Right(.Cells(Z, S), 2) = "ND" And Right(.Cells(Z - 1, S), 2) <> "ND" Then
                    If .Cells(Z, 1) <> Letzter And .Cells(Z, 1) - Letzter < 21 Then
                        TB2.Cells(2, S) = TB2.Cells(2, S) + 1 'Hochzählen
                        Anz = Anz + 1
                    End If
                End If
            Next Z
            Letzter = 0 'zurücksetzen
        Next S
    End With
   
    MsgBox "Fertig" & vbLf & vbLf & Anz & " Verstöße gefungen"
End Sub

LG UweD

[Schrader]

Hallo Rudi

danke für die bemühungen

in der Original datei funktionirt es nicht zu 100%. muss ich noch schauen.

aber in Testdatei ist mir aufgefallen, dass U und GLZ auch einbezogen werden.

Kann man es so machen das wenn nicht ND FND SND usw drin steht die Zellen als "leer" angesehen werden?

Ich habe in der Testdatei nun die Urlaube und Freitage und Gleitzeit erst mal rausgelöscht , damit ich reale Zahlen habe

gruß sascha

[Rudi'S]

Hallo,

so ist es, wenn man nicht immer alle Fakten mit anführt, bzw. es nicht ganz klar ist was du möchtest.

Es sollte reichen, wenn du im Makro die Zeile von 

If Right(.Cells(Z, S), 2) = "ND" Or Right(.Cells(Z, S), 2) = "LZ" Then

auf  

If Right(.Cells(Z, S), 2) = "ND" Then

änderst, wie es schon im Ursprung war.
Da die anderen Einträge nicht diese Endung mit diesen 2 Buchstaben haben, muss es eigentlich funktionieren, aber wie gesagt, besser wäre es, die Zählschleife auf den letzten Tag der Einsatztage zu beziehen, Oder bei der jetzigen Betrachtung die Einsatztage bei den freien Tagen abziehen.
Wäre wahrscheinlich eine Zählschleife für notwendig.
Schau ich mir mal an, was sinnvoller ist

Gruß Rudi

[UweD]

Hallo Rudi und Schrader


Seht euch erstmal den Post von mir von eben an.

Da hab ich den Letzten Tag bereits berücksichtigt.


LG UweD

[Rudi'S]

Hallo Uwe,

ist klar. In der Aufzählung bist du von der Annahme seiner Vorgabe mit 7 zusammenhängenden Einsatztagen ausgegangen.
Aber seine Daten sagen etwas anderes aus, ZB. 2Tage 1Tag Ruhe 4 Tage oder ein anderes Beispiel mit 11 Tagen - und da passt deine Zählweise mit den +6 Tagen leider nicht.
Aber diesen Punkt hatte ich schon mal angesprochen, aber darauf wurde nicht eingegangen. Und so meine ich, muss die Berechnung etwas anders aufgestellt werden.

Ansonsten stimme ich deiner Auffassung in der Abarbeitung seines Wunsches vollkommen zu.
Ok, deinen letzten Beitrag habe ich noch nicht gesehen, da scheint schon eine Änderung drin zu sein. 
Muss jetzt leider mal weg.

Gruß Rudi

[Schrader]

Hallo zusammen

macht euch bitte nicht so verückt um mein Problem

an sich hat es vom Prinzip her funktioniert.

Von daher schon mal ein großeß Danke an euch

Wenn Ihr nicht weiter machen möchtet ist das OK. Ich muss dann in Zukunft nur meine Grunddaten abändern in der Datei dann passt das schon....

Wie gesagt in VBA / Makros bin ich überhaupt nicht fitt drin..aber irgendwann vielleicht

liebe grüße Sascha