Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 09:54:17
Durchschnittswerte zwischen Tagen berechnen
16.04.2024 09:51:15
Frage: KPI - Logistik Dashboard
16.04.2024 09:22:11
Wenn Istleer über mehrere Blätter
15.04.2024 23:32:21
Darstellung im IMAGE sehr schlecht
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1100to1104
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1100to1104
1100to1104
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Samstagszuschlag (VBA)

Samstagszuschlag (VBA)
Uwe
Morgääähn,
zu Arbeitszeiten und Zeitzuschlägen (Samstagsarbeit, Nachtarbeit o.ä.) gibt`s im Archiv eigentlich genug Informationen. Eigentlich deshalb, da diese sich gewöhnlich Formeln oder Funktionen bedienen. Das, was ich hierzu suche, ist eine Lösung ausschließlich über VBA.
In einem Tabellenblatt ("Tabelle1") befinden sich in den Zellen ("B5:B35") die Angaben zu den Wochentagen (Mo-So) zu einem Kalendermonat. In den Zellen ("C5:C35") die Information "Arbeitsbeginn" sowie in ("D5:D35") die Angabe "Arbeitsende". Wie bekomm ich`s nun hin, das in den Zellen ("E5:E35") immer dann die Summe der Stunden steht, die an einem Samstag [Zellen ("B5:B35")] angefallen sind, sowie in die Zeit von 13:00 Uhr bis 20:00 Uhr fallen?
Die Zeit des Zuschlags (13:00-20:00) soll hierbei mit in die VBA-Routine aufgenommen werden und keinesfalls im Tabellenblatt ("Tabelle1") aufgeführt werden.
Für mich ist dieses Anliegen einmal mehr unlösbar! Aber für Euch wieder eine Herausforderung?
Besten Dank für Eure Mühe.
Uwe
PS: Aus unerfindlichen Gründen kann ich zur Zeit eine vorbereitete Tabelle nicht hochladen. Sorry!
AW: Samstagszuschlag (VBA)
mumpel
Hallo!
Hattest Du die Frage nicht schon einmal gestellt? https://www.herber.de/forum/messages/1101928.html
Dort habe ich eine Beispieltabelle gepostet.
Die Zeit des Zuschlags (13:00-20:00) soll hierbei mit in die VBA-Routine aufgenommen werden und keinesfalls im Tabellenblatt ("Tabelle1") aufgeführt werden
Wo soll das Ergebnis sonst stehen? In einer Userform? Irgendwo muss das Ergebnis ja hin.
Gruß, René
AW: Samstagszuschlag (VBA)
Uwe
Hallo Rene!
Dank für Deine Mühe. In der Tat hatte ich diese Frage schon mal gestellt. Und, wie auch da schon beschrieben, geht es mir darum, eine Lösung zu finden, die eben komplett auf VBA basiert. Nicht das Ergebnis sondern die Zeit des Zuschlags (13:00-21:00) soll mit in die VBA Routine aufgenommen werden. Auch Dein Beispiel arbeitet ja mit Hilfe von Funktionen.
Das Ergebis, so denn Samstagsstunden (Sa, 13-21:00) angefallen sind soll dann durchaus in das Arbeitsblatt ("Tabelle1"). Hier eben in Zelle ("E5:E35")
Ich denke mal das es mir nicht wirklich gut gelingt, das Problem verständlich dazustellen...
Uwe
Anzeige
AW: Samstagszuschlag (VBA)
Uwe
Hey Mumpel,
auch Dein Beispiel total gut und richtig. Das vorherige aber, vom Rene, ist für mich "pflegeleichter"...
Genial, dieses Forum. Wieder mal schneller Lösungen, als man(n) die sich richtig ansehen kann.
Uwe
AW: Samstagszuschlag (VBA)
Uwe
Heeey Tino!
MEGASUPERGUT!!! Und ob Du mich verstanden hast!!! DAS ist die Lösung!!! Das jeddenfalls sagen mir die ersten Tests!!!
Denke mal, das ich Deine Hilfe denn noch mal in Anspruch nehmen kann, so ich weitere Fragen habe?
Ganz ganz lieben Dank!
Uwe
Anzeige
AW: Samstagszuschlag (VBA)
mumpel
Aber was ist bei Tinos Lösung, wenn man bis 20:10 arbeitet oder vor 13:00 beginnt? Dann wird der Zuschlag nicht mehr berechnet. Müsste es aber trotzdem. Abzüglich der Zeit vor 13:00 und nach 20:00. Meine Funktion macht das. Müsste man nur so umstellen, dass die Zeiten in die Zelle eingetragen werden.
AW: Samstagszuschlag (VBA)
Uwe
Hey Mumpel,
genau das ist mir gerade auch aufgefallen. Hatte gerade dem Timo schon entsprechend geantwortet.
Kriegst vietlleicht auch Du das hin, Timos Routine derart zu ergänzen? Das wäre toll.
Denn ich bin mit einer Anwendung schon derart weit, das mir blos noch ein, zwei Lösungsansänsätze fehlen...
Und gelernt habe ich dabei schon mehr als ich zu träumen gewagt habe.
Uwe
Anzeige
AW: Samstagszuschlag (VBA)
mumpel
Das würde nur mit einer Formel gehen oder mit meiner Funktion, da man ja die Zeitperioden berechnen muss. Ich teste gerade etwas. Melde mich bei Erfolg.
AW: Samstagszuschlag (VBA)
Uwe
Hey Mumpel,
habe zwar zur Zeit keine Ahnung wie das gehen kann. Trotzdem glaube ich daran, das das durchaus mit einer Ergänzung zu Timos Routine funktionieren wird.
Wie gesagt, Formel oder Funktion sind für mich nicht brauchbar...
Uwe
AW: Samstagszuschlag (VBA)
Uwe
Hallo Tino!
Da war die Freude größer als die Geduld, Deinen Lösungsvorschlag zu Ende zu prüfen. Mmmhhh, `nen Fehler hab ich dann doch noch gefunden. Leider...
Arbeit an einem Samstag in der Zeit z.B. von 10:45 bis 13:15 müßte dann ein Zuschlag in Höhe von 0:15 ergeben (Zuschlag eben ab 13:00). In der Zeit von 16:45 bis 20:45 ergäbe das dann 3:15 (Zuschlag bis 20:00.
Denke mal, diese beiden Bedingungen hast Du nicht bedacht...
Kriegste das hin? Das nämlich wäre super!!!
Vielen Dank
Uwe
PS: Sorry das ich zu voreilig mit meinem ersten Urteil war...
Anzeige
AW: Samstagszuschlag (VBA)
Uwe
Hey Timo!
Deine Lösung funktioniert jetzt auch dann, wenn der Einsatz in die Zeit des Samstagszuschlags hineinreicht. Leider noch nicht aber dann, wenn der Einsatz über 20:00 hinausgeht (z.B. 18:45-21:45)
Super nett von Dir, so Du weiter daran "feilen" könntest!
Wie gesagt, es fehlt nicht mehr wirklich viel und ich komme zu einer beinahe perfekten Lösung für meine kleine Anwendung.
Lieben Dank
Uwe
AW: Samstagszuschlag (VBA)
Uwe
Tino?
Prüf mal bitte ob Deine Routine insgesamt richtig rechnet! Mir kommt es so vor, als ob nun bei Deinem letzten Versuch die Berechnung nur noch dann stimmt, so der Einsatz in die Zeit von 13:00-20:00 hineingeht.
Uwe
Anzeige
AW: Samstagszuschlag (VBA)
Tino
Hallo,
habe mal alles noch angepasst und zur Kontrolle die Daten mal von Hand dabei geschrieben.
https://www.herber.de/bbs/user/64378.xls
Jetzt müsste es passen.
Gruß Tino
AW: Samstagszuschlag (VBA)
Uwe
Hey Tino!
Jetzt passt es bis auf ein Missverständnis, was ich erst jetzt verstanden habe. Es sollen in der Spalte E nur die Zeiten des Zuschlags aufgeführt sein. Nicht aber die Summen. O.k.
Also zu jeder Zeile einfach der Zuschlag, wenn denn die Bedingungen erfüllt sind. Also im Ergebnis nicht die Zeiten vorheriger Zuschläge dazuzählen!
Sorry.
Uwe
PS: Die Summe der gesamten Samstagsstunden wärend eines Monats stehen später woanders! Diese dann zu bilden hab ich schon gecheckt...
Anzeige
AW: Samstagszuschlag (VBA)
Tino
Hallo,
mach aus der Zeile
    BereichSumme.FormulaR1C1 = _
    "=IF(AND((RC2=""Sa""),(RC3<=" & StundeBis & ")," & _
    "(RC4>" & StundeVon & ")),IF(AND((RC2=""Sa""),(RC3<=" & StundeBis & ")," & _
    "(RC4>" & StundeVon & ")),Min(RC4," & StundeBis & ")- IF(RC3>" & StundeVon & ",RC3," & StundeVon & "),0)+Max(R4C5:R[-1]C5),"""")"
diese
    BereichSumme.FormulaR1C1 = _
    "=IF(AND((RC2=""Sa""),(RC3<=" & StundeBis & ")," & _
    "(RC4>" & StundeVon & ")),IF(AND((RC2=""Sa""),(RC3<=" & StundeBis & ")," & _
    "(RC4>" & StundeVon & ")),Min(RC4," & StundeBis & ")-IF(RC3>" & StundeVon & ",RC3," & StundeVon & "),0),"""")"
so müsste es passen.
Gruß Tino
Anzeige
AW: Samstagszuschlag (VBA)
Uwe
Hey Tino!
Jetzt funktioniert es für einen Arbeitseinsatz perfekt! Leider gibt`s da noch ein Prolem: Insgesamt gilt es drei Arbeitseinsätze zu berücksichtigen!
In Deiner Beispieldatei stand der mögliche Beginn der Arbeit stets in Zelle ("C5:C35"), das Ende der Arbeit in Zelle ("D5:D35") Das Ergebnis eines Samstagszuschlages in Zelle ("E5:E35") Soweit perfekt...
In dem maßgeblichen Arbeitsblatt gibt es drei mögliche Arbeitseinsätze:
Einsatz 1 = Beginn der Arbeit in Zelle ("M5:M35")
Einsatz 1 = Ende der Arbeit in Zelle ("N5:N35")
Einsatz 2 = Beginn der Arbeit in Zelle ("Q5:Q35")
Einsatz 2 = Ende der Arbeit in Zelle ("R5:R35")
Einsatz 3 = Beginn der Arbeit in Zelle ("U5:U35")
Einsatz 3 = Ende der Arbeit in Zelle ("V5:V35")
Das Ergebis des Samstagszuschlages, so die Bedingungen (Sa, 13:00-20:00) erfüllt sind muß in die Zelle ("AE5:AE35")
Irgendwie find ich`s beinahe selber schon reichlich "frech", Dich nun zu bitten, mir auch das "mal eben" zu programmieren... Sorry,
so ich aber diese Lösung bekomme wär`s das dann für mich. Der beinahe schwierigste Teil meiner Aufgabe wäre dann gelöst.
Besten Dank für Dein Verständnis
Vielen, vielen dank
Uwe
Anzeige
gut das der erste Teil passt,
Tino
Hallo,
für den zweiten Teil könntest du bitte ein Beispiel hochladen, damit man gleich richtig anfangen kann.
Kann aber erst später noch mal reinschauen bin gerade beim Kaffee u. Kuchen.
Gruß Tino
AW: gut das der erste Teil passt,
Uwe
Hei Tino!
Vielen Dank für Deine Hilfe. Zur Zeit funktioniert es hier bei mir aus unerfindlichen Gründen nicht eine Beispielmappe hochzuladen.
Gehe dazu doch einfach hin und füge neben dem Einsatz von bis in Spalte C & D einfach zwei weitere Einsätze z.B. in Spalten H&I und K&L ein. Das Ergebnis (eben den Samstagszuschlag) schreibst in Spalte R
In Ordnung?
Das Ergebnis von z.B. drei Arbeitseinsätzen an einem Tag könnte dann so aussehen:
Einsatz 1 von 11:45 bis 13:30, Einsatz 2 von 15:00 bis 16:15, Einsatz 3 von 19:45 bis 21:00
In diesem Fall lautet das Eergebnis 2:00 Stunden
Samstagszuschlag (13:00-13:15+15:00-16:15+19:45-20:00)
Vielen, vielen Dank für Deine Unterstützung.
Uwe
PS: Die Routine später dann den richtigen Zellen zuzuordnen sollte ich dann wohl hinbekommen. Die benötigten Zellen aber habe ich Dir schon in meiner vorherigen Nachricht aufgeschrieben.
Anzeige
AW: gut das der erste Teil passt,
mumpel
Hast Du vielleicht Umlaute im Dateinamen oder ist der Dateiname zu lang? Vor allem Umlaute solltest Du vermeiden, um hier hochladen zu können.
AW: kein Plan ob es so richtig ist...
Uwe
Hallo Tino!
Genau So ist es richtig!!! Nur benötige ich nur die GEsamtsumme pro Tag nicht aber die Summe der Zuschläge zu den jeweiligen Einsätzen.
Zudem das alles ohne farblich markierte Zellen.
Ich seh aber nun auch mal nach ob ich den Teil der Routine soweit selber verstehe, das ich vielleicht die Summen der einzelnen Einsätze "killen" kann.
Wär aber trotzdem nett, so Du mir weiterhelfen kannst.
Wie gesagt, ich brauche nur das Ergebnis in der Spalte AE ("AE5:AE35")
Vielen Dank
Uwe
AW: so gehts...
Uwe
Heeey, GENAU!!!
Wenn ich jetzt nur die drei Arbeitsleistungen, OHNE die Werte der Spalten C & D, berücksichtigt würden, wär es perfekt! Bist Du so gut und erklärst mir vielleicht im Code die Stellen, wo eben die Werte in den jeweiligen Spalten abgefragt werden? Dann fällt es mir nicht so schwer, z.B. dann die Werte der Spalten C & D herauszunehmen, oder andere hinzuzufügen, sollte sich mal die Vorgabe ändern.
Irgendwie ist heute mal wieder nicht mein Tag! Ich "seh" einfach im Code nicht, wo die Angaben zu den Werten für die Berechnung des Zuschlages "stecken"
Aber insgesamt funktioniert das jetzt ganz genau! Bis auf eben die Tatsache das der erste Einsatz (C & D) eben wegfallen sollen.
Gruß und Danke
Uwe
AW: so gehts...
Tino
Hallo,
entferne aus der Zeile 
          BereichSumErg.FormulaR1C1 = _
"=IF(SUM(RC" & .Areas(1).Column & ",RC" & _
.Areas(2).Column & ",RC" & .Areas(3).Column & _
",RC" & .Areas(4).Column & ")=0,"""",SUM(RC" & .Areas(1).Column & _
",RC" & .Areas(2).Column & ",RC" & .Areas(3).Column & _
",RC" & .Areas(4).Column & "))"
Diesen Teil (ist zweimal enthalten)
          ",RC" & .Areas(4).Column &

Dies ist der Teil den wir oben in der Zeile 
          Set BereichEinzelErg = .Range("E5:E35,P5:P35,T5:T35,X5:X35")

Als X5:X35 bestimmt haben.
In dieser Zeile löschen wir aber E5:E35,
Nochmal zusammen
Aus der Zeile 
          Set BereichEinzelErg = .Range("E5:E35,P5:P35,T5:T35,X5:X35")

wird diese 
          Set BereichEinzelErg = .Range("P5:P35,T5:T35,X5:X35")
Aus 
          BereichSumErg.FormulaR1C1 = _
"=IF(SUM(RC" & .Areas(1).Column & ",RC" & _
.Areas(2).Column & ",RC" & .Areas(3).Column & _
",RC" & .Areas(4).Column & ")=0,"""",SUM(RC" & .Areas(1).Column & _
",RC" & .Areas(2).Column & ",RC" & .Areas(3).Column & _
",RC" & .Areas(4).Column & "))"
wird diese
          BereichSumErg.FormulaR1C1 = _
"=IF(SUM(RC" & .Areas(1).Column & ",RC" & _
.Areas(2).Column & ",RC" & .Areas(3).Column & _
")=0,"""",SUM(RC" & .Areas(1).Column & _
",RC" & .Areas(2).Column & ",RC" & .Areas(3).Column & "))"

Gruß Tino
AW: so gehts...
Uwe
Hallo Tino!
Jetzt ist`s PERFEKT!!! SUPER BESTEN DANK DAFÜR, FÜR DEINE HILFE!!!
Aber, bist Du so gut und erklärst mir noch, welche Bedeutung die Spalten P, T, und X in Deiner Routine haben?
In meiner späteren Tabelle stehen darin nämlich andere Werte (Uhrzeiten) die auf keinen Fall mit in den Samstagszuschlag einfließen dürfen.
Die Lösung muß später eigentlich "nur" die Einsätze 1 bis 3 darauf hin prüfen, ob Samstagsstunden angefallen sind und das Ergebnis - so wie jetzt realisiert - in Spalte AE ausgeben.
Für heute ist für mich Feierabend.
Ich bemüh mich morgen mal, Dir endlich mal die Tabelle hochzuladen, um die es geht. Gerne würde ich mich zudem mit Dir weiter über E-Mail austauschen. Denke mal Du wärst für den Rest (Kleinigkeiten) der richtige Ansprechpartner!?! Würde mich freuen, so Du mir flugs `ne Mail schreibst... Diese dann bitte an
uwe.siebers@online.de
Danke!
Uwe
müssen es anders machen
Tino
Hallo,
wenn die Zellen dazwischen nicht überschrieben werden dürfen, müssen wir dies anders angehen.
Ersetze den kompletten Code durch diesen.
Kommentare stehen im Code.
Option Explicit

Sub SummeSamstagZuschlag()
Dim meArWerte, meArErgebnis
Dim A As Long
Dim BereichEinzelErg As Range, BereichSumErg As Range
Dim iCalc As Integer
Dim StundeVon As Double, StundeBis As Double

With Application
 .EnableEvents = False
    
    'Überzeit von 
    StundeVon = CDbl(TimeSerial(13, 0, 0))
    'Überzeit bis 
    StundeBis = CDbl(TimeSerial(20, 0, 0))
    

    With Sheets("Tabelle1") 'Tabellenname 
        Set BereichEinzelErg = .Range("M5:O35,Q5:S35,U5:W35") 'Bereich mit den einzelwerten 
        Set BereichSumErg = .Range("AE5:AE35") 'Bereich fürs Ergebnis 
        BereichSumErg.Value = "" 'Ergebnis leer machen 
        meArErgebnis = BereichSumErg
    End With
      
      With Application.WorksheetFunction 'für min Max Funktion 
        'Einzelwerte berechnen 
        For Each BereichEinzelErg In BereichEinzelErg.Areas 'Bereiche durchlaufen 
              meArWerte = BereichEinzelErg 'Bereich in einem Array speichern 
             
             For A = 1 To Ubound(meArWerte) 'Schleife über Array 
               If meArWerte(A, 1) = "Sa" Then 'Sa? 
                    'liegt von bis im Bereich 
                    If meArWerte(A, 2) < StundeBis And meArWerte(A, 2) < StundeBis And meArWerte(A, 3) > StundeVon Then
                     'Ergebnis ausgen in Ergebnis Array (aufsummiert) 
                     meArErgebnis(A, 1) = meArErgebnis(A, 1) + .Min(meArWerte(A, 3), StundeBis) - .Max(meArWerte(A, 2), StundeVon)
                    End If
               End If
             Next A
        
        Next BereichEinzelErg
      End With
            'Ergebnis in Zellbereich Ergebnis schreiben 
            BereichSumErg = meArErgebnis
            'Format Ergebnisbereich festlegen 
            BereichSumErg.NumberFormat = "[h]:mm"

 .EnableEvents = True
End With

End Sub

'Makro zum löschen der Ergebnisse 
Sub LeoscheErgebnisse()
 Range("AE5:AE35").Value = ""
End Sub

Zu der E-Mail, wir sollten schon im Forum bleiben, damit andere auch etwas davon haben.
Gruß Tino
nochmal verbessert
Tino
Hallo,
neuer Tag neues Glück oder eine bessere Idee. ;-)
Habe den Code in eine VBA Funktion eingebaut,
damit wird der eigentliche Code übersichtlicher und für Dich leichter erweiterbar.
https://www.herber.de/bbs/user/64402.xls
Gruß Tino
AW: nochmal verbessert
Uwe
Guten Morgen Tino!
Deine letzte Version von gestern Abend war schon beinahe perfekt. Nur ist darin etwas, was nicht meinen Erwartungen entspricht.
Ich habe in meiner Tabelle ausscchließlich in den Zellen B5:B35 die Angabe dazu ob der Wochentag ein Samstag (Sa) ist. Dein Code von gestern Abend, bzw. auch der von heute Früh setzt aber voraus das diese Angaben jeweils in den Spalten M, Q und U vorhanden ist.
Wäre suuuper gut, so Du das hinkriegst. Wie gesagt, es fehlt nicht viel...
Besten Dank
Uwe
finde es nicht in Ordnung
Tino
Hallo,
jetzt machen wir mit der Tabelle schon so lange rum und erst jetzt kommt Dir der Gedanke,
dass der Aufbau doch anders ist?!
Sorry ist nicht böse gemeint aber solche Infos sollten schon rüber kommen,
daher hatte ich auch nach einem Beispiel gebeten.
Ok. hier die angepasste Version.
https://www.herber.de/bbs/user/64403.xls
Gruß Tino
AW: finde es nicht in Ordnung
Uwe
Sorry Tino!
Das ist natürlich nicht meine Absicht gewesen. Bisher bin ich davon ausgegangen, das ich das schon in den ersten meiner Beiräge das beschrieben habe.
Vielleicht kommt`s einfach daher, das ich in Sachen Abrechnungspraxis schon derart betriebsblind bin, das ich derartige, selbstverständlich anzusprechende Dinge, schlicht übersehe.
Wie gesagt, war keine Absicht. Mir hätte es in der Tat ja auch mal früher auffallen können, das Du von etwas anderen Vorgaben ausgehest.
Nun werde ich die von Dir vorgeschlagene Prozedur flugs testen.
Meld mich wieder
Uwe
PS: Hab irgendwie eh schon ein schlechtes Gewissen.
AW: finde es nicht in Ordnung
Uwe
Heeey, Tino!!!
ES PASST!!! Jetzt ist es GENAU so, wie ich`s mir gewünscht habe. Vielen, vielen Dank für Deine Hilfe, vor allem aber Dein Verständnis!!!
Jetzt ist die Frage wirklich abgeschlossen!!! (smile)
Ganz lieben Gruß
Uwe
ich bin erleichtert ;-) oT.
Tino
OT:...bewundere Tino...
bernd
........
AW: Samstagszuschlag (VBA)
Uwe
Hey Mumpel!
Vielen Dank für Deine Mühe. Um ehrlich zu sein komme ich mit Tinos Beispiel besser zurecht. Dennoch werde ich selbstverständlich Deine datei genau durchsehen um darin insbesondere den Nachtzuschlag für meine Anwendung zu übernehmen, der nämlich passt offensichtlich ganz gut.
Danke, Uwe

Links zu Excel-Dialogen

UserForm

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige