Tag/Nachtfahrten addieren bzw subtrahieren

Computer: Software & Programmierung Office Software
#1

Hallo und guten Tag
nachfolgende Daten habe ich in einer Exceltabelle
A B C D E F
Datum von Datum bis Fahrzeit Arb-zeit
01.01.12 19:00 01.01.12 19:10 D5-B5
01.01.12 19:40 01.01.12 19:55 D6-B6
01.01.12 22:00 02.01.12 02:00 D7-B7 D7-B5

02.01.12 16:30 02.01.12 16:55 D8-B8
usw 5 Fahrten
03.01.12 00:10 03.01.12 00:30 D14-B14
03.01.12 00:45 03.01.12 01:45 D15-B15 D15-B8

Diesen Daten erfolgen für jeden Tag mit unterschiedlichen Fahrten. Mein Problem ist
die automatische Summierung von Arbeitszeit

Derzeit setze ich folgende Formel dafür ein:
WENN(B5=B6;"";F5-INDEX(C:C;VERGLEICH(B5;B:B;0)))
Diese Formel rechnet aber nur 24 Stunden und
nicht über 00:00 bzw fängt immer wider bei
00:00 an.

könnt Ihr mir helfen
Gruß Giessel

#2

Diesen Daten erfolgen für jeden Tag

Hallo,
summieren fuer einen Tag, bedeutet doch auch, der Tag endet um 24:00 Uhr. Danach ist der naechste Tag, von 00:00 bis 01:00 ist eine Stunde zu Beginn eines anderen Tages.
Loesungsansatz, jeweils innerhalb eines Tages bleiben.
Gruss Helmut

#3

Hallo Giessel,

ich hoffe, ich habe dein Problem richtig verstanden. Wenn ja, schau dir mal an, wie ich es hier gemacht habe:
http://www.file-upload.net/download-6794629/Fahrzeit…
Schau dir die Formeln in den Spalten E und F an. Diese Spalten sind als Uhrzeit formatiert.

Gruß, Andreas

#4

Hallo giessel,

ich nehme einfach mal an, dass Dein Fahrtzeiten 24h nicht überschreiten. Dann prüfst Du zunächst, ob das Datum beider Punkte (von / bis) gleich ist.
Wenn ja, hast Du die Differenz durch einfache Substraktion gebildet. Fertig.
Wenn nein, bildest Du zwei Differenzen. Erster Tag bis 24:00 und zweiter Tag von 0:00 bis Ende.
Formel anbei:

=WENN(C1>A1;D1+(24-B1);D1-B1)

MfG MwieMichel

#5

Guten Tag,

Ich danke allen die bisher geantwortet haben. Die Formel von MwieMichel und der Upload von Andreas sind
mir soweit geläufig. Zum besseren Verständnis: Es
handelt sich um Daten aus einem Taxibetrieb mit 3 Tag/
Nachtschichten pro Tag von 3 Fahrzeugen. zZ. habe ich
13000 Datensätze. Mir geht es um die Formel für die Arbeitszeit zur automatischen Ergebnisrechnung der
Tagessalden pro Schicht. Die jetzige Formel rechnet
nur für 24 Stunden und addiert daher 2 Schichten zusammen. Kann man das anpassen???
Die Fahrzeit und Arbeitszeit werden eingefügt und bearbeitet. Die anderen Daten kommen aus einem
Erfassungsprogramm per Schnittstelle
Ich habe Auszüge aus meiner Datei mal hochgeladen
http://www.file-upload.net/download-6796576/Fahrzeit…
Danke für diesen Tipp an Andreas.Ich hoffe man kann
im Forum mir helfen

mfG Giessel

#6

Hallo giessel,
ich fürchte, dass die Frage zu Deinem Problem unglücklich gestellt ist. Das Vorgehen von Andreas kannte ich noch nicht. Es ist die wohl kürzeste Methode, auf „die“ Summe zu kommen.
Offensichtlich ist das aber nicht die Lösung Deines Problems. Die drei Fahrzeuge und die drei Schichten bringen ggf. neue Parameter in’s Spiel.
Wenn die Differenz von E+F und B+C nicht Dein gewünschtes Ergebnis abbildet, nach welchen Kriterien möchtest Du dann differenzieren? Möchtest Du zwischen Tagesschicht Arbeitszeit und Nachtschicht Arbeitszeit unterscheiden? Dann müsstest Du benennen, wann eine Tagesschicht bzw. eine Nachtschicht beginnt / endet?
MfG MwieMichel

Taxibetrieb 3 Tag/Nachtschichten pro Tag
3 Fahrzeugen
Formel für Arbeitszeit
Ergebnisrechnung der Tagessalden pro Schicht

Fahrzeit und Arbeitszeit werden eingefügt und bearbeitet
anderen Daten aus einem Erfassungsprogramm

#7

Hallo Giessel,

ich muss mich leider der Ungewissheit von MwieMichel anschließen: Mir ist auch nicht klar (trotzdem ich deine Datei angeschaut habe), was du genau addieren möchtest.
Geht es dir darum, Fahrten, die über einen Datumswechsel hinausliefen, auf zwei Tage zu verteilen, also den Teil der Fahrt vor Mitternacht zum ersten Tag zu addieren und den Rest der Fahrt zum nächsten Tag?
Bitte versuch’s noch mal präzieser zu erklären.

Gruß, Andreas

#8

Guten Tag, allen im Forum die mir helfen.
Ich versuche mal mich besser zu erklären

Aufgabenstellung:

  1. Ermittlung der Fahrzeit, erfolgt ohne Probleme
  2. Ermittlung der Arbeitszeit, wo die Probleme liegen
    Alle Daten kommen aus einer externen Quelle.
    Die Spalten D G L werden von mir eingefügt. Die Formel in Spalte D gibt als Ergebnis T/N Schicht wieder(6-18 Uhr =Tagschicht / 18-6 Uhr =Nachtschicht) und kann angepasst werden. Die Spalte
    G gibt die Fahrzeit wieder (E+F-B+C) macht keine Probleme. Die Spalte L ist mein Problem. In Spalte L soll als Ergebnis F18-C9 stehen, das habe ich in der Vergangenheit manuell gemacht. Beispiele in Spalte K, wo Änderung steht. Diese Ergebnisrechnung wird durch die Formel in Spalte L übernommen. (Die Formel gebe ich in die erste Zeile, danach doppelklick und die ganze Spalte wird in einem Rutsch berechnet. darum
    geht es mir auch. In der hochgeladen Tabelle im Bereich A44:J58 und A87:J112 liegt mein Problem oder das Problem. Diese Formel rechnet immer nur für 24 Stunden. Kann die Formel dahingehend geändert werden, das die Ergebnisse in K54/K58 und K103/K112 in der Formel so berechnet werden und nicht als Ergebnis 23:50 bzw 23:55. Dann wäre alles OK
    Spalte G=Fahrzeit, Spalte L=Arbeitszeit. Ziel ist, das ich am Monatsende die Arbeitszeit-Fahrzeit errechnen kann, das wäre dann Stand oder Arbeitsbereitschaft. Diese Zeit wird zur Stundenlohnberechnung benötigt, während die Fahrzeit nach anderen Kriterien berechnet wird.
    Bisher wird in unserem Gewerbe überwiegend nach Umsatzprovision
    bezahlt. Ich gehe davon aus, das im nächsten Jahr bundesweit der Mindestlohn kommen wird und sich die Lohnkalkulation grundlegend ändern wird und dafür benötige ich die Stand/Einsatzbereitschaft die bisher nicht gebraucht wurde.
    Ich hoffe ich habe mich verständlicher geäußert und Ihr mich verstanden. Es kann auch eine andere Formel sein, die mir das gewünschte Ergebnis liefert.

Ich bedanke mich schon mal für Eure Hilfe
Sofern gestattet kann man mit mir auch telef.Kontakt
aufnehmen bzw ich rufe zurück. 0521-2089199 oder
01714930808

Gruß Giessel

#9

Nachfragen
Hallo Giessel,

ich habe nochmal versucht, dein Problem zu durchschauen.
In Zeile 3 deiner Datei steht "Die Errechnung der Arbeitszeit ist notwendig, um hinterher von der Summe die Fahrzeit abzuziehen und die reine Standzeit zu bekommen "
Dein Ziel ist also eine Berechnung der Standzeit der Fahrzeuge, also die Summe aller Zeiten, in denen nicht gefahren wird. Ist das so richtig?
Wenn ja, welche Standzeiten sollen jeweils aufaddiert werden? Alle Standzeiten eines Monats, eines Tages, einer Schicht?
Ist eine 5 Minuten Pause zwischen 2 Fahrten auch eine Standzeit?
Oder ist es nur dann eine Standzeit, wenn eine komplette Schicht lang nicht gefahren wird?
Bitte versuche möglichst genau auf die Fragen zu antworten.

Gruß, Andreas

#10

Hallo giessel,

habe 'ne Weile gebraucht, aber ich denke ich ein loses Ende gefunden.

Die Frage in Spalte L lautet: ändert sich das Datum von der aktuellen auf die nächste Spalte (=WENN(B9=B10)…). Unter dieser Randbedingung macht die Formel in Spalte L ganz genau das, wofür sie geschrieben wurde. Sie bildet in Zeile 58 die Summe für den ganzen Tag vom 25.08.

Für die zusätzliche Summe in Zeile 54 führst Du ein weiteres Kriterium ein, nämlich: wenn Pause (lang genug) dann Zwischensumme. Zwischen 5:30 (bzw. 5:43 als Fahrtende) und 20:55 liegt soviel Zeit, dass angenommen werden darf, dass die neue Fahrt in eine neue Schicht fällt.

Liege ich mit meiner Vermutung richtig?
Wenn ja, muss ich gestehen: Ich bin mir nicht sicher, ob ich das mit Formeln hinbekäme.
Könntest Du Dir vorstellen, dieses Problem auch mit VBA anzugehen (Summen über Makros bilden)?
Und welcher Zeitabstand zwischen Fahrtende (Spalte E und F) und Fahrtanfang (Spalte B und C) wäre notwendig um Anlass für eine Zwischensumme bzw. Bildung einer neuen Schicht zu geben?

Wenn ich mich unverständlich ausgedrückt habe, gib mir einfach Bescheid.

LG MwieMichel

#11

Hallo Andreas,
ich vermute, dass neben der Summe über einen Tag bei längeren Pausen auch noch die Zuordnung auf eine neue Schicht erforderlich ist.
Keine Ahnung wie das mit Formeln zu erschlagen wäre. Würde wahrscheinlich 'n Makro nutzen.
LG MwieMichel

#12

Ja,

wenn man die genauen Bedingungen für das Berechnen der Zwischensummen kennt, mit Makros kein Problem.

Gruß, Andreas

#13

Hallo MwieMichel,
du liegst in Deiner Vermutung richtig.Mit VBA kenne ich mich überhaupt nicht aus und könnte zZt. das Problem damit nicht lösen.Anlass für eine Zwischensumme oder Neue Summe wäre das Fahrtende einer Tag oder Nachtschicht, gekennzeichnet durch
T/N in Spalte D oder der PNR in Spalte P. Schichtanfang T 06:00-18:00; N 18:00-06:00. Ein weiteres Problem kann vorkommen, wenn die Nachtschicht vor 18:00 anfängt zu arbeiten, dann wird diese Zeit mit T und ab 18:00 mit N gekennzeichnet oder umgekehrt die Nachtschicht arbeitet bis 08:00.
Bei den Zeilen 41-54; 55-81; 82-103; 104-131 handelt
es sich jeweils um eine komplette Schicht. Die Zeit
zwischen F54+E55; F103+E104 ist das Fahrzeug nicht im Einsatz gewesen und soll daher nicht in der Formel L berechnet werden, könnte aber durch eine Tagschicht belegt sein. Zu der Frage von Andreas ob eine 5 Minuten Pause zwischen zwei Fahrten Standzeiten sind, ja, ist aber in der Summenberechnung Arbeitszeit(L)- Fahrzeit(G) dann enthalten, was ausreicht. Zu der Frage alle Standzeiten: wenn in einer Formel / VBA
möglich, ja!!! Die Standzeiten, wenn das Fahrzeug nicht im Einsatz ist, sollen nicht erfasst werden. Ist es sinnvoll die komplette Datei
hochzuladen???. Bis auf die Fahrzeit wäre diese unbearbeitet.

Ich finde es großartig das es Menschen gibt, die in Ihrer Freizeit die Probleme anderer lösen zu helfen,
dafür meinen Dank

mfG Giessel

#14

Hallo MwieMichel,

Hallo giessel

Mit VBA kenne ich mich
überhaupt nicht aus und könnte zZt. das Problem damit nicht
lösen.

Dafür gibt es das Forum. Aber schauen wir mal…

Anlass für eine Zwischensumme oder Neue Summe wäre das
Fahrtende einer Tag oder Nachtschicht, gekennzeichnet durch
T/N in Spalte D oder der PNR in Spalte P.

Beide Kriterien sind nicht hart genug, da z.B. in Deinem Beispiel im Wesentlichen PNR 11 mit dem Fahrzeug 2 unterwegs ist, und dies ggf. während der Tagesschicht steht. Also alles N. Und PNR steht, glaube ich in Spalte N, richtig?

Schichtanfang T 06:00-18:00; N 18:00-06:00.

Auch das sind keine harten Kriterien, da die Schichten früher anfangen oder später aufhören dürfen / können.

Bei den Zeilen 41-54; 55-81; 82-103; 104-131 handelt
es sich jeweils um eine komplette Schicht.

Und für diese eine Schicht möchtest Du wissen, ob der wesentliche Teil Tag oder Nacht war (also alles N oder alles T)?
Und für diesen Bereich soll eine einzige Summe gebildet werden?

Die Zeit
zwischen F54+E55; F103+E104 ist das Fahrzeug nicht im Einsatz
gewesen und soll daher nicht in der Formel L berechnet werden,
könnte aber durch eine Tagschicht belegt sein.

Ich könnte mir die Markierung eines Schichtanfangs vorstellen. Wie, wäre noch zu prüfen. Nach Deiner Tabelle endet diese Schicht dann nach spätestens 12:00 h. Die nächste Schicht (erst mal ganz gleich ob Tag oder Nacht) dürfte dann frühestens 12:01 h danach beginnen? Könnte es auch vorkommen, dass innerhalb dieser 12h eine neue Schicht beginnt? Dann müssten wir woanders suchen.

Zu der Frage
von Andreas ob eine 5 Minuten Pause zwischen zwei Fahrten
Standzeiten sind, ja, ist aber in der Summenberechnung
Arbeitszeit(L)- Fahrzeit(G) dann enthalten, was ausreicht. Zu
der Frage alle Standzeiten: wenn in einer Formel / VBA
möglich, ja!!! Die Standzeiten, wenn das Fahrzeug nicht im
Einsatz ist, sollen nicht erfasst werden. Ist es sinnvoll die
komplette Datei
hochzuladen???. Bis auf die Fahrzeit wäre diese unbearbeitet.

Lass uns erst mal versuchen anhand dieser Datei harte Kriterien zu finden…

Ich finde es großartig das es Menschen gibt, die in Ihrer
Freizeit die Probleme anderer lösen zu helfen,
dafür meinen Dank

Gern, wenn ich schon meine eigenen nicht lösen kann…:wink:

mfG Giessel

Könntest Du Dir vorstellen, dass der 12:00 Abstand von Schicht zu Schicht mit einem Fahrzeug als Kriterium funktionieren könnte?

mfG MwieMichel

#15

Ich klink mich hier mal aus, bisschen OT
Hallo MwieMichel,

ich sehe schon, du bist der Sache mächtig auf der Spur.
Da ich noch ein paar andere Baustellen habe, lass ich dich hier mal alleine weiter machen. Viel Erfolg.

Gruß, Andreas

#16

Hallo giessel,
ich versuche gerade ein VBA-Makro zu schreiben. Du sagst, dass ein erheblicher Teil der Datensätze „irgendwie“ eingelesen wird.
Gibt es dafür Regeln? Nach Fahrzeug, nach Fahrer, nach wasauchimmer? Ich habe bspw. in dem hochgeladenen Fragment festgestellt, dass das Datum vom ersten zum zweiten Block in die Vergangenheit springt. Ist das ungewöhnlich, eine Ausnahme, oder kann es ausgeschlossen werden?
Werden ggf. verschiedenen Fahrzeuge (ich erinnere mich an 3) direkt hintereinander erfasst?
Ich würde vermuten, dass die Erfassung nach Fahrzeugen hochgeladen wird. Dass also in Deiner finalen excel-Datei Sprünge in die Vergangenheit (Ende Fahrzeug 1, Anfang Fahrzeug 2) möglich sind, nicht aber zwei Schichten die zeitlich ähnlich beginnen (Fahrzeug 1 und 2), im Arbeitsblatt direkt aufeinander folgen. Wenn ich falsch liege bitte hier unbedingt Rückmeldung!!! Sonst bin ich in der falschen Richtung unterwegs.
Benötigst Du die Summe als echte Arbeitsblatt-Summe (=Summe…), oder reicht dir die Summe als Zahl.
Dank Dir für Dein Feedback.
MfG MwieMichel

#17

Hallo MwieMichel, lag ein paar Tage mit Grippe im Bett
daher keine Rückmeldung. Du liegst mit deinen Vermutungen richtig. Ich lade Dir die ganze Datei nach
Import hoch: http://www.file-upload.net Standzeitberechnung für Forum. Ich denke dann kannst
Du das besser einschätzen. Die Frage Summe oder Zahl, wie es am besten passt. Ich hoffe das Du damit
weiter kommst
Gruß Giessel

#18

Hallo giessel,
die beste Krankheit taugt halt nix. Nachträglich gute Besserung.
Mit file-upload musst Du einem alten Mann ggf. auf die Sprünge helfen. Brauche ich da nicht irgendwie noch einen speziellen Link?
Du hast keine VBA-Erfahrung, hast Du gesagt, richtig?. Evtl. müsstest Du noch Deine Excel Version mitteilen. Bei Office 2010 werden die Datein mit Makros unter einer anderen Dateierweiterung gespeichert.
Also:
Bitte zum Testen unbedingt eine Kopie der Exceldatei anlegen! Für den Anfang machst Du am besten nur diese eine Exceldatei auf (wegen der Übersicht).
Excel öffnen. Mit Alt + F11 kommst Du zur Entwicklungsumgebung (und später auch wieder zurück). Hier markierst Du auf der linken Seite Deine Datei (möglicherweise ist eine Datei Personal.* auch noch auf). Mit Einfügen // Modul legst Du einen Ordner für Makros an. Doppelklick auf dieses Modul. Auf der rechten Seite sollte nun ein leeres Code-Fenster zu sehen sein. Hier kopierst Du folgenden Code rein:

Option Explicit
’ Hier beginnt die Subroutine
Sub SchichtSumme()
’ Hier werden Variablen definiert
Dim Pruefen As Variant 'Wert pruefen auf Datumsformat
Dim IstWahr As Boolean 'Wahr / Falsch Abfrage von Pruefen
Dim Start As Date, Ende As Date, Summe As Date 'Start, Ende und Differenz als Datum
Dim Schicht As Date, HelpDate As Date 'Schichtdauer und Kriterium fuer Schichtende
Dim DSpalteA As Integer, ZSpalteA As Integer 'Spaltenindex fuer Beginn (Datum und Zeit)
Dim DSpalteE As Integer, ZSpalteE As Integer 'Spaltenindex fuer Ende (Datum und Zeit)
Dim Zeile As Integer 'Zaehler fuer Zeilenvorschub
Dim SSpalte As Integer 'Zeiger fuer die Spalte in der die Summe stehen soll
’ Initialisieren oder Vorgaben fuer Variablen vergeben
Zeile = 1 'Anfang in Zeile 1
DSpalteA = 2 'Datum Anfang in Spalte 2
ZSpalteA = 3 'Zeit Anfang in Spalte 3
DSpalteE = 5 'Datum Ende in Spalte 5
ZSpalteE = 6 'Zeit Ende in Spalte 6
SSpalte = 18 'Summe in Spalte 18 schreiben
Schicht = „10:00“ 'Schicht dauert hoechstens 10 h
’ Finden des ersten Datums oder: Kopfzeilen filtern
Pruefen = Cells(Zeile, DSpalteA).Value + Cells(Zeile, ZSpalteA).Value
Do Until IstWahr IsDate(Pruefen)
Zeile = Zeile + 1
Pruefen = Cells(Zeile, DSpalteA).Value + Cells(Zeile, ZSpalteA).Value
Loop
’ Erstes Datum und Zeit gefunden, Schleife bis leere Zeile
’ Bitte Leerzeilen vermeiden
Do Until Pruefen = „“
Start = Cells(Zeile, DSpalteA).Value + Cells(Zeile, ZSpalteA).Value
HelpDate = Start + Schicht
Zeile = Zeile + 1
Ende = Cells(Zeile, DSpalteA).Value + Cells(Zeile, ZSpalteA).Value
Do Until Ende > HelpDate
Zeile = Zeile + 1
Ende = Cells(Zeile, DSpalteA).Value + Cells(Zeile, ZSpalteA).Value
If Not IsDate(Ende) Then Exit Do
If Ende

#19

Hallo MwieMichel, danke für die Antworten, die VBA
schaue ich morgen mal in ruhe an. Habe Excel 2007+2010
An bei der Link
http://www.file-upload.net/download-6833937/Standzei…

mfg Giessel

#20

Hallo giessel,
mit der neu hochgeladenen Datei hat mein olles Makro nicht funktioniert. Das liegt an den Zeilen, in denen kein üblicher Datumseintrag vorliegt.
Ich arbeite dran.
MfG MwieMichel