Supportnet Computer Supportnet Games Supportnet Kochen Explipedia
Login: guestBesucher online: 183
Supportnet Computerforum
SUPPORT
Home
Forum
Tipps & Infos
Blitz Angebote
Members
Hilfe
Video

TOP THEMEN
SSD Test
Alles über SSDs

Android Tipps
iPad Tipps
Google Tipps
Windows 8 FAQ
Windows 7 FAQ
E-Mail FAQ
Netzwerk FAQ
Festplatten FAQ
Datenrettung FAQ
Bildbearbeitung FAQ

Top iPhone Apps
Computer Einsteiger
Die 5 besten...
Explipedia
Themen
Direktlinks

Neue Einträge
News einsenden News einschicken
Tipps einsenden Tipp einschicken

SN-LINKS

Suche
Befreundete Seiten
Top Seiten

Supportnet/Forum/Tabellenkalkulation



Supportnet/Forum/Tabellenkalkulation
von MaryJo vom 26.01.2018, 10:20 Diese Seite den Supportnet Favoriten hinzufügen  Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden


Excel-Differenz zwischen 2 Zeiten in einem bestimmten Zeitraum

 (591 Hits)

Guten morgen zusammen,

ich bin noch nicht so gut, wenn es um Formeln in Excel geht und bei VBA bin ich noch blutiger Anfänger, deshalb benötige ich eure Hilfe.

Ich habe in Spalte B und Spalte C Zeitangaben im Format "dd.mm.yy hh:mm:ss". Von diesen Uhrzeiten (Start und Ende) habe ich bereits die Differenz und den Durchschnitt ausgerechnet.

Allerdings sollen bei diesen Berechnungen nur ein bestimmter Zeitraum betrachtet werden.
Zum Beispiel von 07:00 bis 17:00. Die Zeit zwischen 17:01 und 6:59 soll nicht mit berechnet werden.

Beispiel:
Start Ende
20.01.18 09:00:00 21.01.18 09:00:00
22.01.18 07:03:34 22.01.18 08:03:00
25.01.18 09:03:40 25.01.18 13:03:00

Aktuell zeigt es mir einen Durchschnitt von 9h und 40min an.
Mein Ziel wäre aber der Durchschnitt: 5h
Ich möchte also die Formel für die Differenzen so verändern, dass nur noch der Zeitraum zwischen 07:00 und 17:00 betrachtet wird.
Die Berechnung soll also nicht über die Arbeitszeit hinaus gehen und erst wieder am nächsten Arbeitstag weiter rechnen.

Ich würde mich freuen, wenn mir jemand weiterhelfen könnte.

Vielen Dank für eure Hilfe
MaryJo


Antwort schreiben 50 Bonuspunkte

Antworten...
Antwort 1 von xlKing vom 13.02.2018, 00:43 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Hallo MaryJo,

ich bin nicht sicher, ob ich dich richtig verstanden habe. Falls deine Daten in A1 und B1 beginnen probier mal für C1 die folgende Formel aus:

=WENN(ABRUNDEN(A1;0)<>ABRUNDEN(B1;0);ABRUNDEN(A1;0)+ZEIT(17;0;0)-WENN(STUNDE(A1)<7;ABRUNDEN(A1;0)+ZEIT(7;0;0);WENN(STUNDE(A1)>=17;ABRUNDEN(A1;0)+ZEIT(17;0;0);A1))+WENN(STUNDE(B1)<7;ABRUNDEN(B1;0)+ZEIT(7;0;0);WENN(STUNDE(B1)>=17;ABRUNDEN(B1;0)+ZEIT(17;0;0);B1))-(ABRUNDEN(B1;0)+ZEIT(7;0;0));WENN(STUNDE(B1)<7;ABRUNDEN(B1;0)+ZEIT(7;0;0);WENN(STUNDE(B1)>=17;ABRUNDEN(B1;0)+ZEIT(17;0;0);B1))-WENN(STUNDE(A1)<7;ABRUNDEN(A1;0)+ZEIT(7;0;0);WENN(STUNDE(A1)>=17;ABRUNDEN(A1;0)+ZEIT(17;0;0);A1)))

Gruß Mr. K.


'Gute Antwort' meinte 1 Mitglied
Antwort 2 von MaryJo vom 19.02.2018, 08:41 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Hallo xlKing,

erstmal vielen Dank für die Antwort. Ich hab nicht mehr damit gerechnet ^^.
Und vielen Dank für deine Formel, ich bin mit meinen Formeln einfach nicht weiter gekommen.

Eine Frage hätte ich noch:
Ist es auch möglich die Wochenenden(Samstag & Sonntag) ebenfalls aus der Berechnung auszuschließen?
Sodass nur die Zeit innerhalb der Werktage berechnet wird.

LG
MaryJo


Antwort noch nicht bewertet Als gute Antwort bewerten
Diese Antwort hat mein Problem gelöst
Antwort 3 von xlKing vom 21.02.2018, 18:12 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Hallo MaryJo,

ohne Hilfsspalten ist das leider nicht möglich, die dafür nötige Formel ist so lang, dass sie Excel nicht mehr verarbeiten kann. Falls du dich für Hilfsspalten entscheidest, wo du die bereinigten Datumsangaben berechnest, könnte deine Formel in C1 so
=WENN(WOCHENTAG(A1;2)>5;ABRUNDEN(A1;0)+1/24*7+(8-WOCHENTAG(A1;2));ABRUNDEN(A1;0)+WENN(STUNDE(A1)>=17;1/24*17;WENN(STUNDE(A1)<7;1/24*7;A1-ABRUNDEN(A1;0)))) und in D1 so
=WENN(WOCHENTAG(B1;2)>5;ABRUNDEN(B1;0)+1/24*17-(WOCHENTAG(B1;2)-5);ABRUNDEN(B1;0)+WENN(STUNDE(B1)>=17;1/24*17;WENN(STUNDE(B1)<7;1/24*7;B1-ABRUNDEN(B1;0)))) aussehen. Beides als Datum formatiert. Dann ließen sich in E1 die Std. berechnen: =WENN(D1<C1;0;WENN(ABRUNDEN(D1;0)=ABRUNDEN(C1;0);D1-C1;D1-(ABRUNDEN(D1;0)+1/24*7)+(ABRUNDEN(C1;0)+1/24*17-C1)))

Das mag für deinen Fall ausreichen. So richtig zufriedenstellend ist das aber nicht, zumal die Formeln für Dritte immer noch recht unübersichtlich sind. Einfacher und korrekter wäre hier eine Benutzerformel über VBA, die viel mehr Möglichkeiten bietet. Gib dazu in einem Standardmodul den folgenden Code ein:

Function Arbeitsstunden(FirstDay As Date, LastDay As Date, BeginOfWork As String, EndOfWork As String) As Date
  
  Dim std As Date
  
  If FirstDay < Int(FirstDay) + TimeValue(BeginOfWork) Then FirstDay = Int(FirstDay) + TimeValue(BeginOfWork)
  If LastDay > Int(LastDay) + TimeValue(EndOfWork) Then LastDay = Int(LastDay) + TimeValue(EndOfWork)
  If FirstDay > Int(FirstDay) + TimeValue(EndOfWork) Then FirstDay = Int(FirstDay) + 1 + TimeValue(BeginOfWork)
  If LastDay < Int(LastDay) + TimeValue(BeginOfWork) Then LastDay = Int(LastDay) - 1 + TimeValue(EndOfWork)
  If Weekday(FirstDay, vbMonday) > 5 Then FirstDay = Int(FirstDay) + 8 - Weekday(FirstDay, vbMonday) + TimeValue(BeginOfWork)
  If Weekday(LastDay, vbMonday) > 5 Then LastDay = Int(LastDay) - (Weekday(LastDay, vbMonday) - 5) + TimeValue(EndOfWork)
    
  If Int(FirstDay) = Int(LastDay) Then
    Arbeitsstunden = LastDay - FirstDay
  Else
    std = Int(FirstDay) + TimeValue(EndOfWork) - FirstDay + LastDay - Int(LastDay) - TimeValue(BeginOfWork)
    If LastDay > FirstDay + 1 Then
      std = std + (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * (Int(LastDay) - Int(FirstDay) - 1)
      std = std - Int((LastDay - FirstDay) / 7) * (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
      If Weekday(LastDay, vbMonday) < Weekday(FirstDay, vbMonday) Then std = std - (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
    End If
    Arbeitsstunden = std
  End If

End Function
Damit bist du nun nicht nur die leidigen Hilfsspalten los sondern sogar in der Lage die Stunden auch für mehrere Tage anzuzeigen oder Arbeitsbeginn und -ende schneller neu zu definieren. Die Formel in Excel lautet dann schlicht:
=arbeitsstunden(A1;B1;"7:00";"17:00")
Sollter der Fehler #WERT! ausgegeben werden, bedeutet das, dass der Zieltag (B1) selbst im bereinigten Zustand noch vor dem Starttag (A1) liegt. Eine Berechnung ist hier dann unsinnig. Den Fehler kannst du vermeiden indem du noch ein WENNFEHLER(...;0) drumrum baust. Probiers aber lieber erstmal ohne, damit du ein Gefühl für das jeweils richtige Ergebnis bekommst.

Gruß Mr. K.


Antwort noch nicht bewertet
Antwort 4 von xlKing vom 21.02.2018, 18:18 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Achja: Die Zelle mit dem Ergebnis musst du natürlich noch so formatieren, dass du das Ergebnis auch lesen kannst. Zahlenformat: [hh]:mm:ss

Antwort noch nicht bewertet
Antwort 5 von xlKing vom 21.02.2018, 18:33 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Noch ne kleine Korrektur: Der Fehlerwert #Wert! steht für falsche Eingaben in der Formel. Wenn das Zieldatum kleiner als das Startdatum ist wird 0 zurückgegeben.

Function Arbeitsstunden(FirstDay As Date, LastDay As Date, BeginOfWork As String, EndOfWork As String) As Date

Dim std As Date

If FirstDay < Int(FirstDay) + TimeValue(BeginOfWork) Then FirstDay = Int(FirstDay) + TimeValue(BeginOfWork)
If LastDay > Int(LastDay) + TimeValue(EndOfWork) Then LastDay = Int(LastDay) + TimeValue(EndOfWork)
If FirstDay > Int(FirstDay) + TimeValue(EndOfWork) Then FirstDay = Int(FirstDay) + 1 + TimeValue(BeginOfWork)
If LastDay < Int(LastDay) + TimeValue(BeginOfWork) Then LastDay = Int(LastDay) - 1 + TimeValue(EndOfWork)
If Weekday(FirstDay, vbMonday) > 5 Then FirstDay = Int(FirstDay) + 8 - Weekday(FirstDay, vbMonday) + TimeValue(BeginOfWork)
If Weekday(LastDay, vbMonday) > 5 Then LastDay = Int(LastDay) - (Weekday(LastDay, vbMonday) - 5) + TimeValue(EndOfWork)

If LastDay > FirstDay Then
If Int(FirstDay) = Int(LastDay) Then
Arbeitsstunden = LastDay - FirstDay
Else
std = Int(FirstDay) + TimeValue(EndOfWork) - FirstDay + LastDay - Int(LastDay) - TimeValue(BeginOfWork)
If LastDay > FirstDay + 1 Then
std = std + (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * (Int(LastDay) - Int(FirstDay) - 1)
std = std - Int((LastDay - FirstDay) / 7) * (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
If Weekday(LastDay, vbMonday) < Weekday(FirstDay, vbMonday) Then std = std - (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
End If
Arbeitsstunden = std
End If
End If

End Function


'Gute Antwort' meinte 1 Mitglied
Antwort 6 von MaryJo vom 22.02.2018, 08:41 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Hallo xlKing,

wenn ich die Formel =arbeitsstunden(A1;B1;"7:00";"17:00") eingebe, spuckt Excel mir einen Fehler aus, weil er angeblich nichts mit dem Namen anfangen kann.
Hast du eine Idee an was das liegen könnte?


Gruß MaryJo


Antwort noch nicht bewertet Als gute Antwort bewerten
Diese Antwort hat mein Problem gelöst
Antwort 7 von xlKing vom 22.02.2018, 11:11 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Hi MaryJo,

wie gesagt musst du zuvor den Code in ein allgemeines Modul der gleichen Arbeitsmappe kopieren. Sonst kann Excel die Formel nicht finden.
Die Arbeitsmappe muss als .xls oder .xlsm gespeichert werden, sonst löscht Excel beim Schließen den Code wieder.

Gruß Mr. K.


Antwort noch nicht bewertet
Antwort 8 von xlKing vom 22.02.2018, 11:16 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Für den Fall, dass du noch nicht weißt wo der Code hin muss: Öffne den VBA-Editor (Alt + F11) und füge über Menü Einfügen ein Modul ein. Dort
kopierst du den Code rein (z.B. mit Strg + V)

Mr. K.


Antwort noch nicht bewertet
Antwort 9 von MaryJo vom 22.02.2018, 11:21 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Soweit bin ich schon^^
Also den Code hab ich schon eingefügt, deswegen ist mir auch nicht klar, wieso Excel den Namen nicht erkennt.

LG MaryJo


Antwort noch nicht bewertet Als gute Antwort bewerten
Diese Antwort hat mein Problem gelöst
Antwort 10 von xlKing vom 22.02.2018, 11:26 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Hast du vielleicht das Ausführen von Makros deaktiviert? Prüf mal deine Sicherheitseinstellungen.

Antwort noch nicht bewertet
Antwort 11 von MaryJo vom 22.02.2018, 11:50 Mißbrauch, Beleidigungen und Blödsinn den Moderatoren melden

Ich hab den Fehler gefunden :)
Vielen Dank für deine Hilfe!!

Liebe Grüße MaryJo


Antwort noch nicht bewertet Als gute Antwort bewerten
Diese Antwort hat mein Problem gelöst




Antwort schreiben
    Bitte einen 'Nickname' wählen.
Nickname:*
    (eMail-Adresse wird nicht veröffentlicht.)
eMail:
Nachricht: Ich möchte bei Antworten benachrichtigt werden.
    Hilfe zur Beitragsformatierung gibts [hier]
                   
Antwort:*
  Die Nutzungsbedingungen habe ich gelesen und akzeptiert.

MACHEN SIE IHRE WEBSITE ATTRAKTIVER
Sie haben eine eigene Website und wollen Ihre Besucher auf den Supportnet-Service aufmerksam machen? Kopieren Sie einfach den Quellcode in Ihre Seite und jeder Besucher Ihrer Seite kann direkt auf die Supportnet-Datenbank zugreifen.

My Supportnet


SUCHE

Gruppen im Forum
Betriebsysteme
Software
Hardware
Netzwerk
Programmierung
Sonstiges

Impressum © 1997-2018 Supportnet
Version: supportware 1.8.230E / 18.10.2010, Startzeit:Fri May 18 11:28:04 2018