Hallo
vieleicht kann mal jemand helfen:
Ich bräuchte eine VBA Function, mit der ich den Nachtarbeitszeitraum aus der gesamten Arbeitszeit einer Schicht ermitteln kann.
Bsp.
Nachtarbeit 23:00 bis 06:00 Uhr des folgenden Tages
Beginn Schicht < oder = oder > Beginn Nachtarbeitszeit
und
Ende Schicht < oder = oder > Ende Nachtarbeitszeit
= Nachtarbeitszeit in Dezimalstunden
Wäre Super, wenn jemand eine Idee oder Lösung hat.
Gruß
falke1
Hallo,
gib mal ein paar Beispiele der Möglichkeiten mit Uhrzeiten.
Wenn die Frühschicht (1) von 6:00:00 - 14:29:59, die Spätschicht (2) von 14:30:00 - 22:29:59 und die Nachtschicht (3) von 23:00:00 - 5:59:59 reicht, kannst Du leicht vergleichen, indem 7:00:00 Stunden vom Zeitpunkt subtrahiert werden. Dann verschiebt sich das ganze Muster auf einen Tag: (1) 0:00:00 - 8:29:59, (2) 8:30:00 - 16:59-59 und (3) 17:00:00 - 23:59:59. Kannst Du das selbst zu einer Funktion (auch ein kurzer Ausdruck in Abfragen wäre möglich) hinbiegen?
Hallo
habe schon ein paar Funktionen ausprobiert. (Bsp.)
Public Function GetMinutesOfNightShiftWork( _
ByVal StartWork, ByVal EndWork, _
Optional ByVal StartNightHour = #11:00:00 PM#, _
Optional ByVal EndNightHour = #6:00:00 AM#)
If IsNull(StartWork) Or IsNull(EndWork) Then Exit Function
StartWork = TimeValue(CDate(StartWork))
EndWork = TimeValue(CDate(EndWork))
If StartWork < StartNightHour And StartWork > EndNightHour Then _
StartWork = StartNightHour
If EndWork > EndNightHour And EndWork < StartNightHour Then _
EndWork = EndNightHour
GetMinutesOfNightShiftWork _
= CLng(TimeValue(Format(StartWork - 1 - EndWork, "short time")) * 24 * 60)
End Function
Der Schlips kommt ins Rad, wenn z.B. Dienstbeginn 06:00 Uhr ist und Dienstende auch 06:00 Uhr (24 Std.-Dienst, z.B. Feuerwehr), dann kommt 0 Std.
Oder die komplette Dienstzeit liegt vor dem Beginn der Nachtzeit, dann kommen 17 Std.
Keine Ahnung was da schief läuft.
Hilfe wäre schön.
Gruß falke1
Zitat24 Std.-Dienst, z.B. Feuerwehr
Bleibt es bei 24 Stunden, oder werden auch längere Zeiträume ausgewertet, wo ja der Nachtstundenzeitraum mehrfach durchlaufen wird?
ZitatKeine Ahnung was da schief läuft.
Vor dem Rechnen sollte man ein Verständnis für Datum/Uhrzeit entwickeln: Info - Grundsätzliches und Zusammenfassendes über den Datum/Uhrzeit-Datentyp (http://www.ms-office-forum.net/forum/showthread.php?t=206125)
Hallo
Die Ermittlung der Nachtzeiten erfolgt nur Tage-/Schichtweise.
Allerdings ist der Gedanke mit den längeren Zeiträumen auch nicht schlecht.
M.E.müsste man das Ganze dann mit Start- und End-Datum betrachten sowie Wochentage ermitteln und dabei Werktage, Sonntage und Feiertage definieren und die Dienstzeiten dahingehend differenzieren....
Wird ein mächtiges Ding.
Im Moment ist die tage-/schichtweise Ermittlung von Dienstzeit und Nachtzeit bis max 24 Std. tagesübergreifend (z.B. 07:00 bis 07:00) völlig ausreichend.
Gruß
falke1
Unabhängig vom Uhrenlesen muss man sich über Grenzen von Zahlenbereichen klar sein - sie dürfen nicht überlappen, um eindeutig identifizierbar zu sein. Ein Tag von 24 * 60 * 60 Sekunden = 86400 Sekunden beginnt mit 0 und endet mit 86399 und nicht mit 86400 - sonst wäre er genau eine Sekunde zu lang.
Hallo
heisst also vom Ende der Dienstzeit 1 Sec abziehen?
wie ?
Gruß
falke1
Im folgenden ein Ansatz als Abfrage (womit man einen, selbstredend aber auch viele Datensätze berechnen kann):
' ungetestet
SELECT
A.IDTaetigkeit,
COUNT(B.Zeiteinheit) / 4 AS NachtstundenInDezimal
FROM
tblTaetigkeit AS A,
(
SELECT
TT.IDTaetigkeit,
TT.Kalendertag + Z.Zeit AS Zeiteinheit
FROM
(
SELECT
Zeit
FROM
Zeittabelle
WHERE
Zeit >= #23:00:00#
OR
Zeit < #06:00:00#
) AS Z,
(
SELECT
T.IDTaetigkeit,
K.Kalendertag
FROM
tblTaetigkeit AS T,
Kalendertabelle AS K
WHERE
K.Kalendertag BETWEEN DateValue(T.VonDatumUhrzeit)
AND
DateValue(T.BisDatumUhrzeit)
) AS TT) AS B
WHERE
B.Zeiteinheit >= A.VonDatumUhrzeit
AND
B.Zeiteinheit < A.BisDatumUhrzeit
AND
B.IDTaetigkeit = A.IDTaetigkeit
GROUP BY
A.IDTaetigkeit
Ich gehe da von einer tblTaetigkeit aus mit einer IDTaetigkeit, VonDatumUhrzeit, BisDatumUhrzeit, fIDPerson, fIDTaetigkeitsart, ...
Zusätzlich kommen als Hilfstabellen eine Kalendertabelle sowie eine Zeitentabelle (hier in Viertelstundentaktung) zum Einsatz. Solche Hilfstabellen kann man mit etwas Planung und Weitsicht vielfältig nutzen, nicht nur für diese Aufgabe (= Bemerkung für jene, die hier zuviel Aufwand erwarten).
Über zusätzliche Filterungen und andere Gruppierung kann man gezielt nach bestimmten Betrachtungszeiträumen, Personen, Taetigkeitsarten usw. auswerten.
Über die Kalendertabelle kann man zusätzlich Arbeitstage-, Wochenend- und Feiertagsbetrachtungen einbinden.
Nicht zuletzt könnte man die Zeitenfilterung variabel gestalten statt wie hier fixiert auf starre Nachtstunden.
Hier dann auch noch eine mögliche VBA-Umsetzung.
Public Function Min(ByRef a As Variant, ByRef b As Variant) As Variant
If a < b Then Min = a Else Min = b
End Function
Public Function Max(ByRef a As Variant, ByRef b As Variant) As Variant
If a > b Then Max = a Else Max = b
End Function
Public Function RangeOverlap( _
ByRef Range1Start As Variant, _
ByRef Range1End As Variant, _
ByRef Range2Start As Variant, _
ByRef Range2End As Variant) As Variant
RangeOverlap = Max(Min(Range1End, Range2End) - Max(Range1Start, Range2Start), 0)
End Function
'eine Implementierung mit Tagesausschlüssen
'Im Code weiter unten entsprechende Kommentare '!!! entfernen und
'ParamArray mit Empty, oder mit einer Liste von Wochentagen bestücken
'Public Function TimeSpan( _
' ByVal TimeStart As Date, _
' ByVal TimeEnd As Date, _
' ByVal ShiftStart As Date, _
' ByVal ShiftEnd As Date, _
' ParamArray ExcludedWeekdays() As Variant) As Date
Public Function TimeSpan( _
ByVal TimeStart As Date, _
ByVal TimeEnd As Date, _
ByVal SliceStart As Date, _
ByVal SliceEnd As Date) As Date
Dim DayStart As Date
Dim DayEnd As Date
Dim i As Long
'!!!Dim j As Long
'!!!Dim WDay As VbDayOfWeek
For i = Int(TimeStart) To Int(TimeEnd)
'!!!WDay = Weekday(i)
'!!!For j = 0 To UBound(ExcludedWeekdays)
'!!! If WDay = ExcludedWeekdays(j) Then GoTo NextDay
'!!!Next
'Start um Mitternacht, außer ggf. beim ersten Tag
DayStart = Max(TimeStart, i)
'Ende um Mitternacht des nächsten Tages, außer ggf. beim letzten Tag
DayEnd = Min(TimeEnd, i + 1)
'Zeitspanne aufaddieren
TimeSpan = TimeSpan + DayEnd - DayStart
If SliceStart < SliceEnd Then
'Zeit vom Tagesanfang bis Bereichsanfang subtrahieren
TimeSpan = TimeSpan - RangeOverlap(DayStart, DayEnd, i, i + SliceStart)
'Zeit ab Bereichsende subtrahieren
TimeSpan = TimeSpan - RangeOverlap(DayStart, DayEnd, i + SliceEnd, i + 1)
Else
'Zeit zwischen Bereichsanfang und Bereichsende subtrahieren; Ende < Start!!!
TimeSpan = TimeSpan - RangeOverlap(DayStart, DayEnd, i + SliceEnd, i + SliceStart)
End If
'!!!NextDay:
Next
End Function
Hier einige Beispielaufrufe aus dem VBA-Direktbereich:?CLng(TimeSpan(#2017-10-04#, #2017-10-06 1:00#, #23:00#, #6:00#) * 24 * 60)
900
?CLng(TimeSpan(#2017-10-04 03:00#, #2017-10-05 1:00#, #23:00#, #6:00#) * 24 * 60)
300
?CLng(TimeSpan(#2017-10-04 6:00#, #2017-10-06 1:00#, #23:00#, #6:00#) * 24 * 60)
540
?CLng(TimeSpan(#2017-10-04 6:00#, #2017-10-06#, #6:00#, #14:30#) * 24 * 60)
1020
?CLng(TimeSpan(#2017-10-04 15:00#, #2017-10-06 17:00#, #14:30#, #23:00#) * 24 * 60)
1140
PS: Für Abfragen ist Eberhards Vorschlag zu bevorzugen, weil er schneller in der Ausführung sein dürfte.
Hallo
ersteinmal vielen Dank für die Tips. Habe einiges kapiert und denVBA-Code von Lachtaube ausprobiert - Variante 1 funktioniert supi.
Bloß eine Frage noch:
In Variante 2 :
.....
' ParamArray ExcludedWeekdays() As Variant) As Date
wie und wo rufe ich das auf bzw. setze ich das Array.
Bitte ein Beispiel für Variante 2.
Gruß
falke1
In Variante zwei könntest Du Wochentage angeben, die ausgeschlossen werden sollen. Weil ein ParamArray immer mindestens einen Wert haben muss, muss bei keinem Ausschluss ein Parameter, der keinen Wochentag darstellt, angegeben werden; dieser sollte dann Null sein.
'Version 2, hier ohne Samstag und Sonntag
TimeSpan(#2017-10-04 15:00#, #2017-10-06 17:00#, #14:30#, #23:00#, vbSaturday, vbSunday)
'ohne Ausschluss
TimeSpan(#2017-10-04 15:00#, #2017-10-06 17:00#, #14:30#, #23:00#, Null)Bei Nachtschichten musst Du hier aber die Zeiten (in Deinem Fall um -6 Stunden) zurückverschieben, um die schon in den anderen Tag hineinreichenden Stunden auch zu erfassen, wenn der Betrachtungszeitraum an einem der angegebenen Tage beginnt oder endet. D. h. Du begibst Dich gedanklich nach Chikago, das -06:00 Stunden Zeitdifferenz zur MEZ aufweist.
Hallo, funktioniert super. Ich bräuchte eine ganz einfache Funktion. für die ich nur die Beginn und Endezeit erfasse. Und je nachdem wenn die Beginnzeit größer als Endezeit ist Nachtschicht, wenn nicht Tagschicht. In etwa so:
Function Arbeitszeit(Beginn As Date, Ende As Date) As Double
If Beginn < Ende Then
Arbeitszeit = (Ende - Beginn) * 24
Else
Arbeitszeit = (((Ende - Beginn) + 0.1) * 24)
End If
End FunctionTagschicht gibt korrekten Wert aus, Nachtschicht ist immer Negativ
Hallo,
Du musst 1 addieren und nicht 0,1.
Wo hast Du denn die 0,1 her ?
Arbeitszeit = ((Ende - Beginn) + 1) * 24
Oder einfach als Einzeiler ohne If.
Function Arbeitszeit(Beginn As Date, Ende As Date) As Double
Arbeitszeit = ((Ende - Beginn) + Abs((Beginn > Ende))) * 24
End Function
Vielen vielen Dank, ich war jetzt schon am probieren mit der DatDiff-Funktion. Hätte ich bestimmt hinbekommen, aber das sieht elegant, schlank und schnell aus!
Die 0,1 war nur der Versuch, weil es bei mir mit der 1 zu einem negativen Ergebnis