Supportnet / Forum / Tabellenkalkulation
Zeitdifferenzen in Excel richtig berechnen
Frage
Hallo zusammen,
ich bastle gerade an einer Excel-Tabelle herum, mit der ich meine Arbeitszeiten auswerten und kontrollieren möchte.
In der Spalte B der aktuellen Tabelle steht meine Anfangszeit und in Spalte C die Endzeit. Zusätzlich gibt es noch eine 2. Tabelle Interna, auf der in einer Zelle meine tägliche Soll-Arbeitszeit steht.
Solange ich einen positiven oder negativen Saldo habe, klappt auch alles.
Beispiel:
Soll-Arbeitszeit: 08:21
B17: 08:40
C17: 17:01
In diesem Fall entspricht die tatsächliche Arbeitszeit genau der Soll-Arbeitszeit.
Wenn ich nun eine einfache Funktion ( = C17 - B17 - Soll-Arbeitszeit ) berechnen lasse, kommt auch korrekt 00:00 heraus.
Allerdings habe ich noch eine wenn-abfrage eingebaut:
=[wenn( C17=""; 0; C17 - B17 - Soll-Arbeitszeit )
Nun behauptet Excel die ganze Zeit, daß der Wert negativ ist. Wenn ich die Abfrage noch mit (-1) multipliziere, kommt auch wieder der korrekte Wert heraus.
Auch mit einem Marko, dem ich Anfangs- und Ende-Zeit übergebe, kommt immer der negative Wert heraus.
mein Makro:
---------------------------------------------
Function zeit_berechnen(AnfangsZeit As Date, EndeZeit As Date)
Dim summe As Date
Dim arbzeit As Date
arbzeit = TimeValue("08:21")
summe = EndeZeit - AnfangsZeit - arbzeit
MsgBox (summe)
zeit_berechnen = summe
End Function
----------------------------------------------
Wenn die Zellen mit B17= 08:39 und C17= 17:00 gefüllt sind, wird das Ergebnis plötzlich wieder positiv, obwohl sich beide Ergebnisse vom Wert her nicht unterscheiden.
Ich bin mittlerweile völlig ratlos, woran dieses Verhalten liegt.
Kann mir da jemand helfen??
Danke im Voraus!
Liebe Grüße
ComputerFee
Antwort 1 von Anonymus
Aus Deiner Beschreibung lese ich eigentlich, das, wenn keine Endzeit eingetragen (C17=""), keine Ausgabe erfolgen soll, oder?
Wenn ja, würde ich die Formel für Deine Arbeitszeit anders fassen:
WENN(C17;C17-B17-Soll_Arbeitszeit;""). Alternativ kannst Du natürlich statt keine Ausgabe das "" durch 0 ersetzen.
Wenn ja, würde ich die Formel für Deine Arbeitszeit anders fassen:
WENN(C17;C17-B17-Soll_Arbeitszeit;""). Alternativ kannst Du natürlich statt keine Ausgabe das "" durch 0 ersetzen.
Antwort 2 von ComputerFee
Hallo Anonymous,
das ist schon richtig. Wenn keine Endzeit angegeben ist, erfolgt auch keine Berechnung.
Das funktioniert aber auch ganz problemlos mit der Funktion.
Mir macht das Verhalten von Excel bei den Beispieldaten Kopfzerbrechen.
1.
B17= 08:40 / C17= 17:01 / Ergebnis= -00:00
2.
B17= 08:39 / C17= 17:00 / Ergebnis= 00:00
Liebe Grüße
ComputerFee
das ist schon richtig. Wenn keine Endzeit angegeben ist, erfolgt auch keine Berechnung.
Das funktioniert aber auch ganz problemlos mit der Funktion.
Mir macht das Verhalten von Excel bei den Beispieldaten Kopfzerbrechen.
1.
B17= 08:40 / C17= 17:01 / Ergebnis= -00:00
2.
B17= 08:39 / C17= 17:00 / Ergebnis= 00:00
Liebe Grüße
ComputerFee
Antwort 3 von cmkatz
Hallo,
probiers mal mit der Funktion:
=WENN(C17="";0;ABS(C17-B17-B20))
ABS gibt den Absolutwert aus, also den reinen Betrag unabhängig vom Vorzeichen.
Gruß
cmkatz
probiers mal mit der Funktion:
=WENN(C17="";0;ABS(C17-B17-B20))
ABS gibt den Absolutwert aus, also den reinen Betrag unabhängig vom Vorzeichen.
Gruß
cmkatz
Antwort 4 von ComputerFee
Hallo cmkatz,
das hatte ich auch schon überlegt.
Allerdings sind dann keine Minuszeiten mehr möglich.
Allerdings kommt mir gerade die Idee, in dem Makro abzufragen, ob der Absoultwert 0 ist und dann diesen zurückzugeben. Ansonsten eben das Ergebnis.
Danke, werde ich gleich mal ausprobieren.
Gruß
ComputerFee
das hatte ich auch schon überlegt.
Allerdings sind dann keine Minuszeiten mehr möglich.
Allerdings kommt mir gerade die Idee, in dem Makro abzufragen, ob der Absoultwert 0 ist und dann diesen zurückzugeben. Ansonsten eben das Ergebnis.
Danke, werde ich gleich mal ausprobieren.
Gruß
ComputerFee
Antwort 5 von Aliba
Hi ComputerFee, dann machs doch mit RUNDEN.
=WENN(C17="";0;RUNDEN(C17-B17-B20;-10))
CU Aliba
=WENN(C17="";0;RUNDEN(C17-B17-B20;-10))
CU Aliba
Antwort 6 von Jürg
Hallo,
Wenn keine Minuszeiten erscheinen:
Menü Extras / Optionen / Berechnung / bei "1904-Datumswerte" einen Haken setzen.
Gruss
Jürg
Wenn keine Minuszeiten erscheinen:
Menü Extras / Optionen / Berechnung / bei "1904-Datumswerte" einen Haken setzen.
Gruss
Jürg
Antwort 7 von Olimat
Hallo
schau Dir mal diese Seite an, da sind Excel
Vorschläge. Da wird bestimmt auch ein Tipp für
Dich dabei sein. Der User hat dort eine super
Tabelle zur Arbeitszeiterfassung gebastelt.
http://demond.bei.t-online.de/excel.htm
Gruss
Olimat
schau Dir mal diese Seite an, da sind Excel
Vorschläge. Da wird bestimmt auch ein Tipp für
Dich dabei sein. Der User hat dort eine super
Tabelle zur Arbeitszeiterfassung gebastelt.
http://demond.bei.t-online.de/excel.htm
Gruss
Olimat
Antwort 8 von ComputerFee
Hallo Olimat,
Danke für den Tip mit dem Link. Die Datei ist ja super.
Allerdings wird auch hier das Gleiche berechnet wie in meiner Tabelle.
Das mit dem -00:00 scheint ein echter Excel-Fehler zu sein.
Muß ich wohl mit leben.
Liebe Grüße
ComputerFee
Danke für den Tip mit dem Link. Die Datei ist ja super.
Allerdings wird auch hier das Gleiche berechnet wie in meiner Tabelle.
Das mit dem -00:00 scheint ein echter Excel-Fehler zu sein.
Muß ich wohl mit leben.
Liebe Grüße
ComputerFee
Antwort 9 von ComputerFee
Hallo Aliba,
Runden hilft mir gar nicht, weil Excel dann aus meiner Zeitangabe in Std. und Min. leider eine Dezimalzahl macht.
Liebe Grüße
ComputerFee
Runden hilft mir gar nicht, weil Excel dann aus meiner Zeitangabe in Std. und Min. leider eine Dezimalzahl macht.
Liebe Grüße
ComputerFee
Antwort 10 von ComputerFee
Hallo Jürg,
die Anzeige der Minuszeiten sind nicht mein Problem.
Liebe Grüße
ComputerFee
die Anzeige der Minuszeiten sind nicht mein Problem.
Liebe Grüße
ComputerFee
Antwort 11 von Aliba
Hi ComputerFee,
musste halt als [hh]:mm formatieren.
CU Aliba
musste halt als [hh]:mm formatieren.
CU Aliba
Antwort 12 von nighty
hi alle :)
versteh zwar nur die haelfte wahrscheinlich :)
doch FEE hat ja gute vorkenntnisse
hatte mal dieses makro geschrieben,wird zwar das problem glaub ich nicht so loesen doch dient es vielleicht der motivation fuer eigene makros,leichter basicdialekt :)
eine beliebige zelle ist sollzeit,die rechts nebenliegende die istzeit die wieder rechtsliegende die zeitdifferenz.
erste wie zweite zelle werden vorrausgesetzt,das makro produziert das ergebnis dann in die dritte.
gruss nighty
Sub Makro1()
On Error GoTo fehler
adress$ = ActiveWindow.RangeSelection.Address
adress1 = Len(adress$)
For mo = 1 To adress1
If Mid$(adress, mo, 1) = "$" Then
llp = llp + 1
Else
If llp = 1 Then
spalte$ = spalte$ + Mid$(adress, mo, 1)
End If
If llp = 2 Then
zeile$ = zeile$ + Mid$(adress, mo, 1)
zeile1 = Val(zeile$)
End If
End If
Next mo
b0 = Asc(spalte$)
Range(Chr$(b0) & zeile1) = ""
b0 = b0 - 2
GoSub modul1
c4 = c333
b0 = b0 + 1
GoSub modul1
b0 = b0 + 1
If c4 > c333 Then c5 = c4 - c333
If c333 > c4 Then c5 = c333 - c4
c6 = c5
Do
If c6 < 60 Then
Exit Do
Else
b = b + 1
c6 = c6 - 60
End If
Loop
b1$ = Str(b)
If Mid$(b1$, 1, 1) = " " Or Mid$(b1$, 1, 1) = "-" Then b1$ = Mid$(b1$, 2, Len(b1$))
b2$ = Str(c6)
If Mid$(b2$, 1, 1) = " " Or Mid$(b2$, 1, 1) = "-" Then b2$ = Mid$(b2$, 2, Len(b2$))
a23$ = b1$ + "." + b2$
a24 = Val(a23$)
If Len(b2$) = 1 Then b2$ = "0" + b2$
If Range(Chr$(b0 - 1) & zeile1) > Range(Chr$(b0 - 2) & zeile1) Then Range(Chr$(b0) & zeile1) = "+" + b1$ + "." + b2$
If Range(Chr$(b0 - 1) & zeile1) < Range(Chr$(b0 - 2) & zeile1) Then Range(Chr$(b0) & zeile1) = "-" + b1$ + "." + b2$
End
modul1:
laenge = Len(Range(Chr$(b0) & zeile1))
If Mid$(Range(Chr$(b0) & zeile1), 1, 1) = " " Then Range(Chr$(b0) & zeile1) = Mid$(Range(Chr$(b0) & zeile1), 2, laenge - 1)
laenge = Len(Range(Chr$(b0) & zeile1))
For t = 1 To laenge
If Mid$(Range(Chr$(b0) & zeile1), t, 1) = "," Then
c1$ = Mid$(Range(Chr$(b0) & zeile1), 1, t - 1)
c11 = Val(c1$)
c111 = c11 * 60
c2$ = Mid$(Range(Chr$(b0) & zeile1), t + 1, laenge)
c22 = Val(c2$)
c333 = c111 + c22
t = laenge + 1
zr = 1
End If
Next t
If zr = 0 Then
c1 = Val(Range(Chr$(b0) & zeile1))
c333 = c1 * 60
End If
zr = 0
Return
fehler:
End Sub
versteh zwar nur die haelfte wahrscheinlich :)
doch FEE hat ja gute vorkenntnisse
hatte mal dieses makro geschrieben,wird zwar das problem glaub ich nicht so loesen doch dient es vielleicht der motivation fuer eigene makros,leichter basicdialekt :)
eine beliebige zelle ist sollzeit,die rechts nebenliegende die istzeit die wieder rechtsliegende die zeitdifferenz.
erste wie zweite zelle werden vorrausgesetzt,das makro produziert das ergebnis dann in die dritte.
gruss nighty
Sub Makro1()
On Error GoTo fehler
adress$ = ActiveWindow.RangeSelection.Address
adress1 = Len(adress$)
For mo = 1 To adress1
If Mid$(adress, mo, 1) = "$" Then
llp = llp + 1
Else
If llp = 1 Then
spalte$ = spalte$ + Mid$(adress, mo, 1)
End If
If llp = 2 Then
zeile$ = zeile$ + Mid$(adress, mo, 1)
zeile1 = Val(zeile$)
End If
End If
Next mo
b0 = Asc(spalte$)
Range(Chr$(b0) & zeile1) = ""
b0 = b0 - 2
GoSub modul1
c4 = c333
b0 = b0 + 1
GoSub modul1
b0 = b0 + 1
If c4 > c333 Then c5 = c4 - c333
If c333 > c4 Then c5 = c333 - c4
c6 = c5
Do
If c6 < 60 Then
Exit Do
Else
b = b + 1
c6 = c6 - 60
End If
Loop
b1$ = Str(b)
If Mid$(b1$, 1, 1) = " " Or Mid$(b1$, 1, 1) = "-" Then b1$ = Mid$(b1$, 2, Len(b1$))
b2$ = Str(c6)
If Mid$(b2$, 1, 1) = " " Or Mid$(b2$, 1, 1) = "-" Then b2$ = Mid$(b2$, 2, Len(b2$))
a23$ = b1$ + "." + b2$
a24 = Val(a23$)
If Len(b2$) = 1 Then b2$ = "0" + b2$
If Range(Chr$(b0 - 1) & zeile1) > Range(Chr$(b0 - 2) & zeile1) Then Range(Chr$(b0) & zeile1) = "+" + b1$ + "." + b2$
If Range(Chr$(b0 - 1) & zeile1) < Range(Chr$(b0 - 2) & zeile1) Then Range(Chr$(b0) & zeile1) = "-" + b1$ + "." + b2$
End
modul1:
laenge = Len(Range(Chr$(b0) & zeile1))
If Mid$(Range(Chr$(b0) & zeile1), 1, 1) = " " Then Range(Chr$(b0) & zeile1) = Mid$(Range(Chr$(b0) & zeile1), 2, laenge - 1)
laenge = Len(Range(Chr$(b0) & zeile1))
For t = 1 To laenge
If Mid$(Range(Chr$(b0) & zeile1), t, 1) = "," Then
c1$ = Mid$(Range(Chr$(b0) & zeile1), 1, t - 1)
c11 = Val(c1$)
c111 = c11 * 60
c2$ = Mid$(Range(Chr$(b0) & zeile1), t + 1, laenge)
c22 = Val(c2$)
c333 = c111 + c22
t = laenge + 1
zr = 1
End If
Next t
If zr = 0 Then
c1 = Val(Range(Chr$(b0) & zeile1))
c333 = c1 * 60
End If
zr = 0
Return
fehler:
End Sub
Antwort 13 von nighty
hi alle :)
ups ,nachtrag :)
die aktive zelle ist das ergebnis,dementsprechend die beiden vorgehenden der zeile soll und ist zeit.
gruss nighty
ups ,nachtrag :)
die aktive zelle ist das ergebnis,dementsprechend die beiden vorgehenden der zeile soll und ist zeit.
gruss nighty
Antwort 14 von nighty
hi fee :)
mit folgenden befehlen kannst du die uhrzeit zerpfluecken :)
laenge eines string
llaemge=len(var)
abtastung von einzelnen zeichen
ab der zweiten pos wird ein zeichen gelesen
einZeichen=mid$(var,2,1)
for t=1 to laenge
einZeichen=mid$(var,t,1)
next t
string zu integer
izahl=str$(var)
integer zu string
istring=val(var)
gruss nighty
mit folgenden befehlen kannst du die uhrzeit zerpfluecken :)
laenge eines string
llaemge=len(var)
abtastung von einzelnen zeichen
ab der zweiten pos wird ein zeichen gelesen
einZeichen=mid$(var,2,1)
for t=1 to laenge
einZeichen=mid$(var,t,1)
next t
string zu integer
izahl=str$(var)
integer zu string
istring=val(var)
gruss nighty
Antwort 15 von pelle
@all,
wenn ich ComputerFee richtig verstanden habe,
ist sein Problem die Minus-Darstellung,
wie in Antwort 2 Beispiel 1.
Es ist schon eine seltsame Begebenheit, für die ich leider auch keine Erklärung habe.
Selbst die sonst zu 99 % Trefferquote von Aliba hilft nicht weiter.
Das Format habe ich ausprobiert/ eingesetzt!
Habe die Formel von ComputerFee probiert.
Gleiche Ergebnis Minus-Zeichen bei Wert 00:00,
seltsam.
Habe dann mal die Beginn und Ende Zeiten beginnend bei 00.00 bzw. 08:21 um eine Minute erhöht, um festzustellen, ob der "Fehler"
nochmal auftritt, negativ.
@ComputerFee
sorry der "Fehler" ließ sich nicht mehr provozieren!
Bereitet die Anzeige ein Problem??
Habe leider keine Erklärung, habe dazu auch nichts gefunden.
gruss
pelle
Ps.: Wäre für eine Rückmeldung dankbar!!!
wenn ich ComputerFee richtig verstanden habe,
ist sein Problem die Minus-Darstellung,
wie in Antwort 2 Beispiel 1.
Es ist schon eine seltsame Begebenheit, für die ich leider auch keine Erklärung habe.
Selbst die sonst zu 99 % Trefferquote von Aliba hilft nicht weiter.
Das Format habe ich ausprobiert/ eingesetzt!
Habe die Formel von ComputerFee probiert.
Gleiche Ergebnis Minus-Zeichen bei Wert 00:00,
seltsam.
Habe dann mal die Beginn und Ende Zeiten beginnend bei 00.00 bzw. 08:21 um eine Minute erhöht, um festzustellen, ob der "Fehler"
nochmal auftritt, negativ.
@ComputerFee
sorry der "Fehler" ließ sich nicht mehr provozieren!
Bereitet die Anzeige ein Problem??
Habe leider keine Erklärung, habe dazu auch nichts gefunden.
gruss
pelle
Ps.: Wäre für eine Rückmeldung dankbar!!!
Antwort 16 von Aliba
Hi CompurterFee, hi Pelle,
@Pelle,
hab das nun auch noch mal geprüft, Du hast recht, es gibt bei negativen Ergebnissen Probleme bei der Rundung.
Bei dieser Formel konnte ich keine Fehler feststellen:
=WENN(C17="";0;WENN(ABS(C17-B17-$B$1)<0,000000000001;0;C17-B17-$B$1))
Wobei hier die Soll-Az in B1 steht.
Zu dem eigentlichen Problem:
Excel speichert alle Zahlen als Binärzahlen, was jedoch bei Werten nahe 0 zu Fehlern ab ca. der 14. Nachkommastelle führen kann.
Ich habe mal in B1 die Sollaz eingetragen und dann ab B17 und C17 beginnend mit den Beispielzeiten von ComputerFee diese nach unten mal jeweils beide um eine Minute erhöht. So ca 50 Werte. In D17 habe ich die Formel =C17-B17-$B$1
und in E 17 die Formel =WENN(C17="";0:C17-B17-$B$1) geschrieben und entsprechend weit nach unten kopiert. Das Zellformat für diese Zellen :
Zahl, 25 Dezimalstellen.
Dann seht ihr mal , was da so rauskommt. Obwohl in C17 was steht, erhaltet ihr für beide Formeln
(mit und ohne WENN-Abfrage) unterschiedliche Ergebnisse und exakt 0,0000000000000000000000000
kommt in den seltensten Fällen vor.
Hier gibt es zu dieser Problematik ein paar Erläuterungen:
http://support.microsoft.com/default.aspx?scid=kb;de;78113
http://support.microsoft.com/default.aspx?scid=kb;de;42980
Nachdem man nun ja weiss, dass eine Minute für Excel 1/1440tel ist und das halt über die 14. Dezimalstelle hinausgeht, erklärt dies vielleicht die Differenzen in Bezug auf den erwarteten Wert 0.
Warum nun jedoch die eigentliche Subtraktion ein anderes Ergebnis bringt als die Subtraktion nach einer wahren WENN-Abfrage, das weiss dan wiederum keiner.
CU Aliba
@Pelle,
hab das nun auch noch mal geprüft, Du hast recht, es gibt bei negativen Ergebnissen Probleme bei der Rundung.
Bei dieser Formel konnte ich keine Fehler feststellen:
=WENN(C17="";0;WENN(ABS(C17-B17-$B$1)<0,000000000001;0;C17-B17-$B$1))
Wobei hier die Soll-Az in B1 steht.
Zu dem eigentlichen Problem:
Excel speichert alle Zahlen als Binärzahlen, was jedoch bei Werten nahe 0 zu Fehlern ab ca. der 14. Nachkommastelle führen kann.
Ich habe mal in B1 die Sollaz eingetragen und dann ab B17 und C17 beginnend mit den Beispielzeiten von ComputerFee diese nach unten mal jeweils beide um eine Minute erhöht. So ca 50 Werte. In D17 habe ich die Formel =C17-B17-$B$1
und in E 17 die Formel =WENN(C17="";0:C17-B17-$B$1) geschrieben und entsprechend weit nach unten kopiert. Das Zellformat für diese Zellen :
Zahl, 25 Dezimalstellen.
Dann seht ihr mal , was da so rauskommt. Obwohl in C17 was steht, erhaltet ihr für beide Formeln
(mit und ohne WENN-Abfrage) unterschiedliche Ergebnisse und exakt 0,0000000000000000000000000
kommt in den seltensten Fällen vor.
Hier gibt es zu dieser Problematik ein paar Erläuterungen:
http://support.microsoft.com/default.aspx?scid=kb;de;78113
http://support.microsoft.com/default.aspx?scid=kb;de;42980
Nachdem man nun ja weiss, dass eine Minute für Excel 1/1440tel ist und das halt über die 14. Dezimalstelle hinausgeht, erklärt dies vielleicht die Differenzen in Bezug auf den erwarteten Wert 0.
Warum nun jedoch die eigentliche Subtraktion ein anderes Ergebnis bringt als die Subtraktion nach einer wahren WENN-Abfrage, das weiss dan wiederum keiner.
CU Aliba
Antwort 17 von Aliba
Hi, nochmal ich.
Habe gerade bemerkt, daß sich bei meinem 1. Beitrag ein Schreibfehler eingeschlichen hat.
Bei der RUNDEN-Formel natürlich nicht -10, sondern 10.
CU Aliba
Habe gerade bemerkt, daß sich bei meinem 1. Beitrag ein Schreibfehler eingeschlichen hat.
Bei der RUNDEN-Formel natürlich nicht -10, sondern 10.
CU Aliba
Antwort 18 von ComputerFee
Hallo nighty,
vielen Dank für Deine Mühe.
Ich hab das mittlerweile so gelöst, daß ich in einem Makro abfrage, ob die tatsächliche Arbeitszeit = der Sollzeit ist. Und dann wird der Saldo auf 0 gesetzt.
Damit ist der Fehler dann auch behoben.
Liebe Grüße
ComputerFee
vielen Dank für Deine Mühe.
Ich hab das mittlerweile so gelöst, daß ich in einem Makro abfrage, ob die tatsächliche Arbeitszeit = der Sollzeit ist. Und dann wird der Saldo auf 0 gesetzt.
Damit ist der Fehler dann auch behoben.
Liebe Grüße
ComputerFee