Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1104to1108
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Pausenzeiten berücksichtigen

Pausenzeiten berücksichtigen
Uwe
Und wieder mal: Hallo zusammen,
in der gerade flugs mal hochgeladenen Datei https://www.herber.de/bbs/user/64886.xls habe ich mir jeweils Arbeitszeiten in Spalte I zusammengerechnet. (vergl. Prozedur Summe im Modul 1) Nun ist diese Summe jedoch an Bedingungen geknüpft:
Dauert ein Einsatz - jeder Einsatz für sich betrachtet - länger als 6 Stunden so sind der Summe 30 Minuten, bzw. bei einem Einsatz länger als 9 Stunden 45 Minuten (Pause) abzuziehen. So ergibt z.B. der Zeitraum 7:00 - 15:30 stets 8:00 Stunden anstelle von 8:30 Stunden.
Um ehrlich zu sein, fehlt mir nun schon im Ansatz `ne Idee, wie denn der Code der Summe anzupassen ist, da ja jeder Einsatz für sich zu sehen ist. In Spalte I finden sich, so gesehen, nur jeweils die Gesamtstunden eines Tages wieder.
Tja, bin mal wieder auf Eure Hilfe angewiesen. Allein deshalb schon...
Vielen, vielen Dank für Eure Ideen.
Uwe
AW: Pausenzeiten berücksichtigen
05.10.2009 14:16:19
JogyB
Hi.
Da ich die Mappe hier nicht runterladen kann, konnte ich es mit der nicht testen, mit einer selbstgebastelten Stundenliste (Zeiten als hh:mm eingegeben) geht es aber damit (Datenbereich I2 bis I20):
{=SUMME(WENN(I2:I20>9,75/24;I2:I20-0,75/24;WENN(I2:I20>6/24;I2:I20-0,5/24;I2:I20)))}
Ist eine Matrixformel, die {} nicht eingeben, sondern die Eingabe mit Strg+Shift+Enter abschliessen.
Zellenformat der Summe ist [h]:mm
Gruss, Jogy
AW: Pausenzeiten berücksichtigen
05.10.2009 14:22:13
Uwe
Hallo Jogy!
Vielen Dank für Deine Mühe. ABER leider benötigte ich die Lösung ausschließlich in VBA
Gruß
Uwe
AW: Pausenzeiten berücksichtigen
05.10.2009 14:50:56
JogyB
Hi.
So vielleicht?
' Gibt die Stundensumme des Bereichs als Dezimalzahl zurück
' inkl. der gewünschten Pausen
Function summeStundenmitPause(ByVal daTen As Range) As Double
Dim adR As String
adR = daTen.Address(, , , True)
summeStundenmitPause = _
Evaluate("=sum(if(" & adR & ">9.75/24," & adR & _
"-0.75/24,if(" & adR & ">6/24," & adR & "-0.5/24," & adR & ")))") * 24
End Function
Gruss, Jogy
Anzeige
kleines Problem...
05.10.2009 16:19:56
JogyB
Hi.
Die Adressierung macht hier ein wenig Probleme... um die Bezüge mit Sicherheit korrekt zu haben, muss die "externe" Addressierung wählen. Dadurch wird die Formel aber schnell zu lang, da der Dateiname auch noch mit drin steht.
Als Alternative:
Function summeStundenmitPause2(ByVal daTen As Range) As Double
Dim adR As String
' Variant, da Sheet oder Chart
Dim tempWsh
Dim tempUpdtState As Boolean
' altes Sheet zwischenspeichern
Set tempWsh = ActiveSheet
' Screenupdating Status zwischenspeichern und dann aus
tempUpdtState = Application.ScreenUpdating
Application.ScreenUpdating = False
adR = daTen.Address(False, False)
' Arbeitsmappe aktivieren
daTen.Parent.Parent.Activate
' Arbeitsblatt aktivieren
daTen.Parent.Activate
' Jetzt ist das Arbeitsblatt mit den Daten aktiv, damit reichen Bezüge
' Ohne die Angabe des Blattes bzw. der Arbeitsmappe
summeStundenmitPause2 = _
Evaluate("=sum(if(" & adR & ">9.75/24," & adR & _
"-0.75/24,if(" & adR & ">6/24," & adR & "-0.5/24," & adR & ")))") * 24
' wieder auf alte Arbeitsmappe und Arbeitsblatt
tempWsh.Parent.Activate
tempWsh.Activate
' Screenupdating wieder auf alten Status
Application.ScreenUpdating = tempUpdtState
' Gibt es als Stunden:Minuten im Direktbereich aus
Debug.Print Evaluate("=TEXT(" & Replace(summeStundenmitPause2 / 24, ",", ".") & ",""[h]:mm"" _
)")
End Function
Ist zwar durch die Aktiviererei etwas unschön, finde aber grade keine andere Lösung.
Gruss, Jogy
Anzeige
Alternative...
06.10.2009 08:34:03
JogyB
Hi.
Mir ist noch eine Alternative eingefallen, nämlich den Code der Funktion direkt in das Klassenmodul des Arbeitsblattes mit den Daten zu packen:
Public Function summeStundenmitPause3(ByVal daTen As Range) As Double
Dim adR As String
adR = daTen.Address(False, False)
' Jetzt ist das Arbeitsblatt mit den Daten aktiv, damit reichen Bezüge
' Ohne die Angabe des Blattes bzw. der Arbeitsmappe
summeStundenmitPause3 = _
Evaluate("=sum(if(" & adR & ">9.75/24," & adR & _
"-0.75/24,if(" & adR & ">6/24," & adR & "-0.5/24," & adR & ")))") * 24
' Gibt es als Stunden:Minuten im Direktbereich aus
Debug.Print Evaluate("=TEXT(" & Replace(summeStundenmitPause3 / 24, ",", ".") & ",""[h]:mm"" _
)")
End Function
Wenn das Arbeitsblatt als "Tabelle1" benannt ist, dann erfolgt der Aufruf mit:
ThisWorkbook.Worksheets("Tabelle1").summeStundenmitPause3(#DATENBEREICH#)
Bzw. über den Objektnamen (wäre für Tabelle1 standardmäßig auch Tabelle1)
Tabelle1.summeStundenmitPause3(#DATENBEREICH#)
#DATENBEREICH# entspricht den Zellen mit Deinen Daten, also z.B. Range("I2:I20")
Gruss, Jogy
Anzeige
AW: Alternative...
06.10.2009 10:03:05
Uwe
Hallo zusammen, hallo Jogy!
Leider ist das noch nicht die von mir gesuchte Lösung. Anhand der ursprünglich hochgeladenen Tabelle brauche ich einfach eine Prozedur die nicht nur die reine Summe bildet (wie hinterlegt) sondern eben doch die vorgegebenen Pausenzeiten mit berücksichtigt und von der Gesamtsumme dann abzieht.
Mmmhhh, jemand noch ne Idee?
Lieben Dank
Uwe
AW: Alternative...
09.10.2009 09:23:47
JogyB
Hi.
Sorry, war zwei Tage unterwegs. daher erst jetzt die Antwort:
Die Pausenzeiten werden doch berücksichtigt, was fehlt denn jetzt noch?
Ich schaue mir heute abend mal die Datei an, gib bitte Bescheid, wenn die Lösung vom mumpel ok ist, wir brauchen die Arbeit ja nicht doppelt erledigen.
Gruss, Jogy
Anzeige
AW: Alternative...
09.10.2009 15:41:09
Uwe
Hallo Jogy!
Vielen Dank für Deine Rückmeldung. Mumpel`s Idee funktioniert tatsächlich für sich betrachtet. Nun ist es einmal mehr wieder so, das ich eine, eben die Lösung ZWINGEND via VBA benötige!
Wär wirklich nett, so Du Dir die Datei noch ansiehst und diese mir dann wieder hochlädst. Gerne natürlich mit einer Lösung via VBA...
Lieben Dank dafür schon mal aus dem sonnigen Kleve
Uwe
AW: Alternative...
09.10.2009 19:55:51
Jogy
Hi.
So wie ich es verstanden habe, sollen immer zwei Von und Bis-Spalten bzgl. der Pause betrachtet werden, d.h. wenn dort zwei Mal 6:30 rauskommt, dann sind jeweils 30 min abzuziehen - richtig?
Das Makro prüft auch noch, ob die Berechnung sinnvoll ist.
Sub SummeMitPause()
Dim zeIle As Long
Dim einSatz As Byte
Dim zeiT(5 To 35) As Double
Dim timeDif As Double
Const sumSp = 9
With ActiveSheet
For zeIle = 5 To 35
For einSatz = 1 To 4
' Ein paar Überprüfungen, ob Berechnung sinnvoll
If IsNumeric(.Cells(zeIle, 2 * einSatz - 1)) And _
.Cells(zeIle, 2 * einSatz - 1)  "" And _
IsNumeric(.Cells(zeIle, 2 * einSatz)) And _
.Cells(zeIle, 2 * einSatz)  "" And _
.Cells(zeIle, 2 * einSatz) > .Cells(zeIle, 2 * einSatz - 1) Then
timeDif = .Cells(zeIle, 2 * einSatz) - .Cells(zeIle, 2 * einSatz - 1)
zeiT(zeIle) = zeiT(zeIle) + IIf(timeDif > 9.75 / 24, timeDif - 0.75 / 24, _
IIf(timeDif > 6 / 24, timeDif - 0.5 / 24, timeDif))
End If
Next
Next
With .Cells(5, sumSp).Resize(31)
' Und hier wird es eingetragen
.Value = Application.Transpose(zeiT)
' Und sicherheitshalber noch das Zahlenformat setzen
.NumberFormat = "[h]:mm"
End With
End With
End Sub

Anzeige
AW: Alternative...
09.10.2009 21:22:34
Uwe
Hey Jogy!
Vielen Dank für den Code. ABER: Deine Vermutung ist leider (noch) nicht ganz richtig. JEDER "Einsatz" muß für sich betrachtet werde. Ist die Summe bis-von größer 6:00 Std müssen von dieser Summe 30 Minuten abgezogen werden. Ist die Summe größer 9:00 Std sind es 45 Minuten die abgezogen werden müssen.
Wichtig dabei ist, das JEDER Einsatz (von-bis) hierbei für sich betrachtet werden muss.
Denke jetzt mal, das das bestimmt lösbar ist? Wäre super nett, so Du mir dabei noch behilflich sein köntest.
Rückmeldung wäre sehr nett.
Gruß
Uwe
AW: Alternative...
10.10.2009 08:29:22
Uwe
Hallo Jogy!
Erst heute Früh hatte ich Gelegenheit, mich mit Denem Vorschlag zu beschäftigen. Dieser funktioniert auch schon wirklich gut. In der ursprünglich hochgeladenen Tabelle hat sich nun aber ein, na, Fehler ist hier nicht gganz richtig, eben ein Detail eingeschlichen, was so nicht benötigt wird.
Du gibtst mit Deiner Prozedur nun das Ergebnis in Spalte 9 aus. (ist auch so gewünscht) Nun ist es ja ein leichtes, diese Anweisung zu verändern, um Deine Routine gleich mal in mehrere Dateien unterschiedlichen Aufbaus zu nutzen.
Wie aber erkenne ich im Code nun die Eingabefelder? Hier ist blöde, das in meiner nunmehr gültigen Tabelle stets neben Einsatzzeiten von-bis sich noch Werte befinden, die NICHT berücksichtigt werden dürfen.
Wäre halt gut, so sich die Spalten, auf die sich die Berechnungen (bis-von) beziehen, ebenso klar benennen lassen. Das aber krieg ich selber eindeutig nicht hin.
Kurze Rückmeldung wäre sehr nett.
Besten Dank schon mal...
Uwe
Anzeige
AW: Alternative...
10.10.2009 13:27:02
Jogy
Hi.
Um das allgemein zu machen, muss eine gewissen Regelmäßigkeit bzw. Markierungen in den Daten vorhanden sein. Wenn Du jetzt z.B. sagen würdest, dass die Zeitspalten immer mit "Von" und "Bis" bezeichnet sind und die Summenspalte mit "Summe", dann könnte man die zu Beginn suchen. Hilfreich wäre dabei, wenn die Überschrift immer in derselben Zeile wäre.
Dann wäre es auch egal, ob da zwei oder 20 Einsätze drinstehen und ob da sonst noch irgendwelche Daten stehen.
Gruss, Jogy
AW: Alternative...
10.10.2009 13:56:39
Uwe
Hey Jogy.
Genau so ist das!!! Letztlich stehen die 4 Einsätze IMMER in den Zeilen 5 bis 35 (das hattest Du ja bereits realisiert...) Die Einsätze 1 bis 4 sind zudem IMMER mit von bis bezeichnet. Im Einzelnen steht:
Einsatz 1 von = E5:E35 und Einsatz 1 bis = F5:F35
Einsatz 2 von = O5:O35 und Einsatz 2 bis = P5:P35
Einsatz 3 von = S5:S35 und Einsatz 3 bis = T5:T35
Einsatz 4 von = W5:W35 und Einsatz 4 bis = X5:X35
Die zu erreichnende Summe steht hierbei IMMER in Spalte Z. Also Z5:Z35
Wäre super nett, so Du mir nun Deinen Code so anpassen kannst, das es funktioniert von der Summe der einzelnen Einsätze stets dann 0:30 Minuten abzuziehen, so die Einzelsumme größer 6:00 Stunden ist, bzw. 0:45 Minuten abzuziehen, so die Einzelsumme eines Einsätzes größer 9:00 Stunden ist.
Wichtig wäre noch, das in Spalte Z stets nur die richtige Gesamtsumme aller vier möglichen Einsätze steht. Aber, das funktionierte ja schon in Deinem letzten Code gut.
Besten Dank für Deine Hilfe. Glaube nun, das das lösbar ist. Wäre zudem super nett, so Du mir in Deinen Code Kommentare hinzufügst, damit ich`s besserr verstehen kann, wie`s funktioniert. Denn, um ehrlich zu sein, machen mir gerade Berechnungen, wie ich sie brauche immer noch Probleme.
Rückmeldung wäre nett.
Gruß
Uwe
Anzeige
AW: Alternative...
10.10.2009 16:18:25
Uwe
Hey Jogy!
Da wäre mir fast ein Detail "entwischt". Nix dramatisches. Das was ich vorhin beschrieben habe, ist soweit komplett richtig. Übersehgen hatte ich dabei blöderweise nur, das stets von der Summe des ersten Einsatzes die Summe der Spalte D (also D5:D35) abzuziehen ist. Hierzu ein Beispiel:
Einsatz 1 von (E5:E35) 6:45
Einsatz 2 bis (F5:F35) 15:45
Summe = 8:30 da Summe ursprünglich größer 6:00 daher 0:30 schon mal abgezogen
minus 8:00 (dem Wert aus Spalte D (D5:D35) ergibt 0:30 (vorausgesetzt es ist der einziges "Einsatz" des Tages.
Der Wert aus Spalte D (D5:D35) ist AUSSCHLIESSLICH vom Ergebnis des ersten Einsatzes abzuziehen. Alle anderen Einsätze (2 bis 4) bleiben unverändert.
Super, so auch das für Dich lösbar ist...
Freu mich auf Deine Rückmeldung.
Lieben Gruß
Uwe
Anzeige
AW: Alternative...
10.10.2009 16:43:03
Jogy
Hi.
Noch eine Nachfrage, damit das auch gleich drin ist...
Wenn Einsatz 1 abzgl. des Wertes aus Spalte D negativ wird, was dann?
Wäre auch nett, wenn Du mir nochmal eine Beispieldatei hochladen könntest, bei der die Spalten gleich korrekt sind, dann muss ich die nicht erst basteln.
Gruss, Jogy
AW: Alternative...
10.10.2009 17:00:41
Uwe
Hallo Du da...
Negative Werte entstehen nicht!!! Der niedrigste Wert wäre entsprechen NULL. In meiner Datei an der ich arbeite stecken bereits Daten, die ich nicht hochladen darf. Aber warte mal...

Die Datei https://www.herber.de/bbs/user/65010.xls wurde aus Datenschutzgründen gelöscht


Jetzt müsste es o.k. sein. Es geht hier um das Blatt TVöD
Vielen, vielen Dann für Deine Mühe und Hilfsbereitschaft
Gruß
Uwe
PS: Kleiner Tipp zuvor: Nach dem Runterladen der Datei solltest Du ruhig mal im Blatt Zeitdaten andere Werte in Zelle A1 eintragen und jeweils im Blatt TVöD nachsehen... Du erkennst dann eher um was es konkret geht. Das aber nur am Rande.
PPS: Nenne Dein Modul ruhig einfach Modul1 so Du bitte ein neues für Deine Routine einfügst. Aber, egal... mach DU ruhig so, wie Du magst...
Anzeige
AW: Alternative...
11.10.2009 03:31:52
Uwe
Morgääähn Jogi,
da gibts eigentlich nur ein Wort zu Deinem Beispiel: PASST. Lange Rede, kurzer Sinn... Genau SO hab ich mir das vorgestellt.
Vielen, vielen Dank dafür...
Uwe
AW: Pausenzeiten berücksichtigen
05.10.2009 17:19:31
mumpel
Hallo!
Noch eine Lösung.
Die Formeln (Tabellenauschnitt):
Die UDF:
Gruß, René

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige