Hallo,
bei uns in der Firma wird Excel und Calc benutzt.
Diese Excel Formel wird hergenommen um die Stunden bis zur nächsten Inspektion darzustellen.
Wenn die Maschine länger läuft, als die Stundenzahl wann sie zur Inspektion soll,
werden die minus Stunden in Rot "-2:00" dargestellt.
In Calc bekomme ich, wenn die Zeit noch positiv ist, eine ERR502 Meldung.
Die negative Darstellung funktioniert!! Es braucht auch nicht mit dem Ergebnis weiter gerechnet werden!
Wo ist der Fehler?
Oder gibt es einen anderen Ansatz, der die Aufgabe in beiden Systemen (Excel und Calc) löst?
=WENN(AI1206="";"";TEXT(ABS(AI1206-P1206);WENN(AI1206<P1206;"-";0)&"[hh]:mm"))
Für einen Tipp wäre ich echt dankbar!
Gruß
Mak
ERR502 bei einer Formel aus Excel
Moderator: Moderatoren
Re: ERR502 bei einer Formel aus Excel
Hallo,
ich würde die Formel etwas umstellen:
=WENN(AI1206-P1206<0;TEXT(-(AI1206-P1206);"-[hh]:mm");AI1206-P1206)
LG Holger
ich würde die Formel etwas umstellen:
=WENN(AI1206-P1206<0;TEXT(-(AI1206-P1206);"-[hh]:mm");AI1206-P1206)
LG Holger
Re: ERR502 bei einer Formel aus Excel
Hallo Mak,
ich selber habe kein Excel, aber ich saß jetzt mal vor nem Rechner mit Excel2010 und da kam mir was mit deiner Formel arg komisch vor.
Wenn ich deine Formel auf einen übersehbaren Bereich ändere,
dann muss ich sagen, dass sie nicht einwandfrei funktioniert.
Steht z.B. in A12 5:00 und in B12 3:00 drin, dann bekomm ich in Excel die Fehlermeldung #WERT!
Stehen aber die Zeiten vertauscht drin, also A12 3:00 und B12 5:00, dann wird -02:00 ausgegeben.
Sollte deine Formel in Excel wirklich fehlerfrei funktionieren, dann frag ich mich nur in welcher Excel-Version? Denn so kann sie nicht korrekt arbeiten! Falls Du aber meiner Aussage nicht glaubst, dann beweise das Gegenteil mit ner Beispieldatei.
Oder ist dir beim abtippen irgendwie ein Fehler unterlaufen?
Ich versuche das ganze mal mit meinen eigenen Worten zu erklären.
TEXT(ABS(A12-B12);WENN(A12<B12;"-";0)&"[hh]:mm"))
Der Grüne Teil ist in Ordnung.
Jedoch der Blaue Teil überhaupt nicht! Denn der ist einzig für das Format zuständig, und genau dort liegt der Hund begraben. Man kann wohl in Excel und in Calc zwei Zellformate miteinander verketten, was aber mit extremer Vorsicht zu genießen ist!
Aber was passiert dort im einzeln?
Wenn A12 kleiner als B12 ist, dann wird das erste Zellformat "-" genommen, welches eine untergeordnete Rolle spielt da es kein Zahlenformat ist, und das ergänzende beziehungweise erweiterte Zellformat "[hh]:mm" dran- oder dazugehängt. Okay! Das ist noch unproblematisch, da das erste Format einfach nur ein Textähnliches ist. Wobei das Hauptformat in diesem Falle das ergänzende "[hh]:mm" ist. Es besitzt eine höhere Priorität, da es ein Zahlformat ist.
Wenn aber A12 größer als B12 ist, dann knallt es! Und das aus folgendem Grund.
Denn nun wird als Zellformat die Null (0) genommen welches eine höhere Priorität besitzt da es ein Zahlenformat ist. Und nun kommt das ergänzende Format "[hh]:mm" dazu, welches aber auch eine hohe Priorität besitzt da es auch ein Zahlenformat ist.
Und nun müssen sich Excel und Calc die Frage stellen:
"Was nehme ich denn jetzt? Ein Standardzahlformat, oder ein Zeitformat? Aber beides kann ich doch so nicht anwenden!"
Und durch die Verkettung von Standardzahlformat und Zeitformat entsteht nun der Fehler.
Und zwar ganz easy
.
Wie unschwer zu erkennen ist, einfach die Null (0) durch Nichts "" ersetzen. Und schon sind Excel und Calc auf der selben Wellenlänge.
Gruß
balu
ich selber habe kein Excel, aber ich saß jetzt mal vor nem Rechner mit Excel2010 und da kam mir was mit deiner Formel arg komisch vor.
Wenn ich deine Formel auf einen übersehbaren Bereich ändere,
Code: Alles auswählen
=WENN(A12="";"";TEXT(ABS(A12-B12);WENN(A12<B12;"-";0)&"[hh]:mm"))
Steht z.B. in A12 5:00 und in B12 3:00 drin, dann bekomm ich in Excel die Fehlermeldung #WERT!
Stehen aber die Zeiten vertauscht drin, also A12 3:00 und B12 5:00, dann wird -02:00 ausgegeben.
Sollte deine Formel in Excel wirklich fehlerfrei funktionieren, dann frag ich mich nur in welcher Excel-Version? Denn so kann sie nicht korrekt arbeiten! Falls Du aber meiner Aussage nicht glaubst, dann beweise das Gegenteil mit ner Beispieldatei.
Oder ist dir beim abtippen irgendwie ein Fehler unterlaufen?
Der liegt offensichtlich in TEXT().Wo ist der Fehler?
Ich versuche das ganze mal mit meinen eigenen Worten zu erklären.
TEXT(ABS(A12-B12);WENN(A12<B12;"-";0)&"[hh]:mm"))
Der Grüne Teil ist in Ordnung.
Jedoch der Blaue Teil überhaupt nicht! Denn der ist einzig für das Format zuständig, und genau dort liegt der Hund begraben. Man kann wohl in Excel und in Calc zwei Zellformate miteinander verketten, was aber mit extremer Vorsicht zu genießen ist!
Aber was passiert dort im einzeln?
Wenn A12 kleiner als B12 ist, dann wird das erste Zellformat "-" genommen, welches eine untergeordnete Rolle spielt da es kein Zahlenformat ist, und das ergänzende beziehungweise erweiterte Zellformat "[hh]:mm" dran- oder dazugehängt. Okay! Das ist noch unproblematisch, da das erste Format einfach nur ein Textähnliches ist. Wobei das Hauptformat in diesem Falle das ergänzende "[hh]:mm" ist. Es besitzt eine höhere Priorität, da es ein Zahlformat ist.
Wenn aber A12 größer als B12 ist, dann knallt es! Und das aus folgendem Grund.
Denn nun wird als Zellformat die Null (0) genommen welches eine höhere Priorität besitzt da es ein Zahlenformat ist. Und nun kommt das ergänzende Format "[hh]:mm" dazu, welches aber auch eine hohe Priorität besitzt da es auch ein Zahlenformat ist.
Und nun müssen sich Excel und Calc die Frage stellen:
"Was nehme ich denn jetzt? Ein Standardzahlformat, oder ein Zeitformat? Aber beides kann ich doch so nicht anwenden!"
Und durch die Verkettung von Standardzahlformat und Zeitformat entsteht nun der Fehler.
Klar doch!Oder gibt es einen anderen Ansatz, der die Aufgabe in beiden Systemen (Excel und Calc) löst?
Und zwar ganz easy
.
Code: Alles auswählen
=WENN(A12="";"";TEXT(ABS(A12-B12);WENN(A12<B12;"-";"")&"[hh]:mm"))
Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.
wehr rächtschraipfähler findet khan si behalden
wehr rächtschraipfähler findet khan si behalden
-
Gert Seler
- *******
- Beiträge: 1763
- Registriert: Di, 03.10.2006 18:05
Re: ERR502 bei einer Formel aus Excel
Hallo Mak,
MS-Excel kann standardmäßig keine "MinusZeiten" (z.B. - 2:00 ) darstellen, es erscheint der sogenannte
"Jägerzaun" (########) bei mir "Excel_2003".
Deshalb sollten die "Zeiten" vorher in "DezimalZahlen" umgewandelt werden (A2*24 - B2*24) eine gültige
Formatvorlage aus den Vorlagen "Zahlen" (- 2,50 in rot auswählen) gleich (- 2:30).
Mit "Division" "geteilt durch 24 ( /24 ) wird ins "Zeitformat" zurückgewandelt.
Das akzeptiert auch "OOo_Calc".
mfg
Gert
MS-Excel kann standardmäßig keine "MinusZeiten" (z.B. - 2:00 ) darstellen, es erscheint der sogenannte
"Jägerzaun" (########) bei mir "Excel_2003".
Deshalb sollten die "Zeiten" vorher in "DezimalZahlen" umgewandelt werden (A2*24 - B2*24) eine gültige
Formatvorlage aus den Vorlagen "Zahlen" (- 2,50 in rot auswählen) gleich (- 2:30).
Mit "Division" "geteilt durch 24 ( /24 ) wird ins "Zeitformat" zurückgewandelt.
Das akzeptiert auch "OOo_Calc".
mfg
Gert
Es gibt nichts gutes, außer man tut es.
Win7_64 / LO_4.4.5.2
Win7_64 / LO_4.4.5.2
OT Jägerzaun bei negativen Uhrzeiten
Hallo GertMS-Excel kann standardmäßig keine "MinusZeiten" (z.B. - 2:00 ) darstellen, es erscheint der sogenannte
"Jägerzaun" (########) bei mir "Excel_2003".
ja ja, das ist wohl ehr ein allgemeines Problem einer Tabellenkalkulation das es kein unabhängiges Zeit-Format gibt sondern Datum und Uhrzeiten zusammen gehören.
Die Eingabe von 12:00 Uhr erzeugt ja eigentlich [bei OpenOffice] 31.12.1899 12:00 Uhr
Es können halt keine Datum/Uhrzeiten vor dem 31.12.1899 00:00 Uhr angezeigt werden, daher keine negativen Zeiten
Daher auch die Möglichkeit aber sehr problematische Variante, das Datum in den Optionen auf 1.1.1904 zu stellen.
Dann funktioniert es zwar mit den negativen Werten aber es gibt sofort Probleme wenn die Datei auf einem Rechner geöffnet wird wo es diese Einstellung nicht gibt.
http://www.online-excel.de/excel/singsel.php?f=25
Gruß Holger
Re: ERR502 bei einer Formel aus Excel
Hallo Holger,
In Excel mag wohl deine Aussage zutreffen, aber nicht in Calc. Oder versteh ich dich falsch?
Gruß
balu
Von welchem Programm sprichst Du denn, von Excel oder von Calc?Es können halt keine Datum/Uhrzeiten vor dem 31.12.1899 00:00 Uhr angezeigt werden, daher keine negativen Zeiten
In Excel mag wohl deine Aussage zutreffen, aber nicht in Calc. Oder versteh ich dich falsch?
Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.
wehr rächtschraipfähler findet khan si behalden
wehr rächtschraipfähler findet khan si behalden
Re: ERR502 bei einer Formel aus Excel
Hallo balu,
ich müsste mich schon schwer täuschen wenn das bei Calc anders ist, ich werde mal versuchen zu erklären was ich meine:
Zum Verständnis:
Ich trage in eine Zelle 12:00 ein und ändere das Zellenformat auf "TT.MM.JJ HH:MM"
Anzeige: 30.12.1899 12:00:00
Man kann hier deutlich sehen, das der Dezimalwert 12:00=0,5 auch als Datum interpretiert wird.
Daher auch das Problem mit den Negativen Zeiten:
Bekomme ich aus einer Berechnung einen negativen Wert so können werde Calc noch Excel dieses Ergebnis Anzeigen weil keine Anzeige vor dem 30.12.1899 möglich ist auch wenn das Zellenformat ohne Datum auf "HH:MM" eingestellt ist.
Sehwohl wird der Wert berechnet, nur die Anzeige geht nicht.
Wird in den Optionen jetzt das Datum auf 1.1.1904 eingestellt, so wird der Wert Null um 4Jahre in die Zukunft verlegt. Aus der Eingabe 12:00 wird dann der 1.1.1904 12:00
Negative Uhrzeiten kann man zwar jetzt ohne irgend welche Probleme anzeigen,
Der große Nachteil liegt aber beim Datum da hier leider auch eine Differenz von von 4Jahre entsteht, fatal bei Dateien die auf verscheidenden Rechnern laufen sollen.
Ich hoffe das ist einigermaßen verständlich ausgedrückt
Gruß Holger
ich müsste mich schon schwer täuschen wenn das bei Calc anders ist, ich werde mal versuchen zu erklären was ich meine:
Zum Verständnis:
Ich trage in eine Zelle 12:00 ein und ändere das Zellenformat auf "TT.MM.JJ HH:MM"
Anzeige: 30.12.1899 12:00:00
Man kann hier deutlich sehen, das der Dezimalwert 12:00=0,5 auch als Datum interpretiert wird.
Daher auch das Problem mit den Negativen Zeiten:
Bekomme ich aus einer Berechnung einen negativen Wert so können werde Calc noch Excel dieses Ergebnis Anzeigen weil keine Anzeige vor dem 30.12.1899 möglich ist auch wenn das Zellenformat ohne Datum auf "HH:MM" eingestellt ist.
Sehwohl wird der Wert berechnet, nur die Anzeige geht nicht.
Wird in den Optionen jetzt das Datum auf 1.1.1904 eingestellt, so wird der Wert Null um 4Jahre in die Zukunft verlegt. Aus der Eingabe 12:00 wird dann der 1.1.1904 12:00
Negative Uhrzeiten kann man zwar jetzt ohne irgend welche Probleme anzeigen,
Der große Nachteil liegt aber beim Datum da hier leider auch eine Differenz von von 4Jahre entsteht, fatal bei Dateien die auf verscheidenden Rechnern laufen sollen.
Ich hoffe das ist einigermaßen verständlich ausgedrückt
Gruß Holger
Re: ERR502 bei einer Formel aus Excel
Hallo Holger,
Ich möchte darauf hinweisen, dass es mir darum geht Missverständnisse auszuräumen. Und deshalb hoffe ich das durch meine Beispieldatei wir jetzt nicht aneinander vorbei reden.
Zu dem Vorschlag von Gert sei gesagt, dass er wohl nicht verkehrt ist und wohl auch funktionieren mag. Wenn aber für Mak die nun von mir angepasste Formel reicht, dann is ja gut. Zumal ich mir keine großen Gedanken darum gemacht hatte ob man sie verbessern/optimieren kann, sondern nur versucht hatte den Fehler zu beseitigen damit sie in Excel und in Calc funktioniert. In Calc wäre natürlich so ein "Formelaufwand" wohl nicht zwingend nötig, aber darum gings ja auch nicht.
Gruß
balu
Stimmt! Du täuscht dich sehr!ich müsste mich schon schwer täuschen wenn das bei Calc anders ist
Ich will dir jetzt nichts böses unterstellen, Aber! Hast Du das in Calc auch mal vorher getestet? Denn ich muss dich nämlich leider eines besseren belehren, dass Du mit deiner Aussage falsch liegst. Calc kann nämlich das, was Du nicht für möglich hälst. Beweis ist im Anhang.Bekomme ich aus einer Berechnung einen negativen Wert so können werde Calc noch Excel dieses Ergebnis Anzeigen weil keine Anzeige vor dem 30.12.1899 möglich ist auch wenn das Zellenformat ohne Datum auf "HH:MM" eingestellt ist.
Sehwohl wird der Wert berechnet, nur die Anzeige geht nicht.
Ich möchte darauf hinweisen, dass es mir darum geht Missverständnisse auszuräumen. Und deshalb hoffe ich das durch meine Beispieldatei wir jetzt nicht aneinander vorbei reden.
Zu dem Vorschlag von Gert sei gesagt, dass er wohl nicht verkehrt ist und wohl auch funktionieren mag. Wenn aber für Mak die nun von mir angepasste Formel reicht, dann is ja gut. Zumal ich mir keine großen Gedanken darum gemacht hatte ob man sie verbessern/optimieren kann, sondern nur versucht hatte den Fehler zu beseitigen damit sie in Excel und in Calc funktioniert. In Calc wäre natürlich so ein "Formelaufwand" wohl nicht zwingend nötig, aber darum gings ja auch nicht.
Gruß
balu
- Dateianhänge
-
- negative-zeiten.ods
- (8.49 KiB) 44-mal heruntergeladen
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.
wehr rächtschraipfähler findet khan si behalden
wehr rächtschraipfähler findet khan si behalden
Re: ERR502 bei einer Formel aus Excel
Hallo balu du hasst recht, Hut ab,
das war mir wirklich neu weil ich immer glaubte das calc und Excel hier gleich ticken.
ich habe es auch nicht weiter in frage gestellt weil ja dann so eine Formel-Akrobatik wie oben diskutiert in Calc gar nicht notwendig ist.
=WENN(A12="";"";TEXT(ABS(A12-B12);WENN(A12<B12;"-";"")&"[hh]:mm"))
Zu allem Überfluss wird die Uhrzeit noch in Text umwandelt.
Das macht dann doch wirklich nur Sinn, wenn entweder die Datei auch mit Excel angezeigt werden soll oder
negative Uhrzeiten wie von mir vermutet nicht angezeigt werden können.
=WENN(A12="";"";A12-B12) -zellenformat "[hh]:mm"
ist doch viel einfacher und man kann sogar damit weiter rechnen..
OK, da muss ich mal eine Nacht über schlafen. Habe je eine Stunde länger..
gute Nacht
Holger
PS:
Excel macht aus der Eingabe 27.10.1895 einen Texteintrag
das war mir wirklich neu weil ich immer glaubte das calc und Excel hier gleich ticken.
ich habe es auch nicht weiter in frage gestellt weil ja dann so eine Formel-Akrobatik wie oben diskutiert in Calc gar nicht notwendig ist.
=WENN(A12="";"";TEXT(ABS(A12-B12);WENN(A12<B12;"-";"")&"[hh]:mm"))
Zu allem Überfluss wird die Uhrzeit noch in Text umwandelt.
Das macht dann doch wirklich nur Sinn, wenn entweder die Datei auch mit Excel angezeigt werden soll oder
negative Uhrzeiten wie von mir vermutet nicht angezeigt werden können.
=WENN(A12="";"";A12-B12) -zellenformat "[hh]:mm"
ist doch viel einfacher und man kann sogar damit weiter rechnen..
OK, da muss ich mal eine Nacht über schlafen. Habe je eine Stunde länger..
gute Nacht
Holger
PS:
Excel macht aus der Eingabe 27.10.1895 einen Texteintrag
Re: ERR502 bei einer Formel aus Excel
Hallo,
hat etwas länger gedauert.
Danke euch, für die Hilfe und den Tipps.
Mittlerweile haben die drei Leute, die bei uns Calc benutzen, auch Excel bekommen!
P.S.
Da ist mir wohl beim abtippen echt ein kleiner Fehler unterlaufen!!
Ich hab noch mal meine Formel abgeschrieben und sie funktioniert in Excel 2003 ohne Probleme.
=WENN(AI210="";"";TEXT(ABS(AI210-P210);WENN(AI210<P210;"-";"")&"[hh]:mm"))
Auch wenn sie nicht die Eleganteste Lösung war.
Bin darin leider auch kein Fachmann!
Nochmals Vielen Dank
hat etwas länger gedauert.
Danke euch, für die Hilfe und den Tipps.
Mittlerweile haben die drei Leute, die bei uns Calc benutzen, auch Excel bekommen!
P.S.
Da ist mir wohl beim abtippen echt ein kleiner Fehler unterlaufen!!
Ich hab noch mal meine Formel abgeschrieben und sie funktioniert in Excel 2003 ohne Probleme.
=WENN(AI210="";"";TEXT(ABS(AI210-P210);WENN(AI210<P210;"-";"")&"[hh]:mm"))
Auch wenn sie nicht die Eleganteste Lösung war.
Bin darin leider auch kein Fachmann!
Nochmals Vielen Dank
Re: ERR502 bei einer Formel aus Excel
Hallo
Ich kann einer Software nichts abgewinnen in der man solche ↓ Verrenkungen braucht um negative Zeiten darzustellen.
in Calc geht das schlicht und einfach:
Karo
Und diese Tatsache freut dichmak hat geschrieben:Hallo,
hat etwas länger gedauert.
Danke euch, für die Hilfe und den Tipps.
Mittlerweile haben die drei Leute, die bei uns Calc benutzen, auch Excel bekommen!
Ich kann einer Software nichts abgewinnen in der man solche ↓ Verrenkungen braucht um negative Zeiten darzustellen.
Code: Alles auswählen
=WENN(AI210="";"";TEXT(ABS(AI210-P210);WENN(AI210<P210;"-";"")&"[hh]:mm"))in Calc geht das schlicht und einfach:
Code: Alles auswählen
=WENN(AI210="";"";AI210-P210) LO7.4.7.2 debian 12(bookworm) auf Raspberry5 8GB (ARM64)
LO25.2.3.2 flatpak debian 12(bookworm) auf Raspberry5 8GB (ARM64)
LO25.2.3.2 flatpak debian 12(bookworm) auf Raspberry5 8GB (ARM64)
-
Gert Seler
- *******
- Beiträge: 1763
- Registriert: Di, 03.10.2006 18:05
Re: ERR502 bei einer Formel aus Excel
Hallo,
in "Excel" wird es wohl so bleiben. Minuszeiten gibt es in den "USA" nicht, die machen alle nur unbezahlte Überstunden ! Sehr Arbeitgeberfreundlich.
"OpenOffice_Calc" ist schon auf der Überholspur.
Mein Vorschlag die "Zeiten" in "Dezimalzahlen" umzuwandeln (*24 oder /24 ) ist wohl die einfachste Lösung für beide "Excel" & "OOo_Calc".
Als Formel nehme ich folgende Lösung :
Sehr gut geeignet für "Schichtarbeiter". Die funktioniert, egal ob die "A1Zelle" oder die "B1Zelle" den größeren Zeitwert enthält.
mfg
Gert
in "Excel" wird es wohl so bleiben. Minuszeiten gibt es in den "USA" nicht, die machen alle nur unbezahlte Überstunden ! Sehr Arbeitgeberfreundlich.
"OpenOffice_Calc" ist schon auf der Überholspur.
Mein Vorschlag die "Zeiten" in "Dezimalzahlen" umzuwandeln (*24 oder /24 ) ist wohl die einfachste Lösung für beide "Excel" & "OOo_Calc".
Als Formel nehme ich folgende Lösung :
Code: Alles auswählen
=WENN(A1="";"";(A1>B1)+(B1-A1))mfg
Gert
Es gibt nichts gutes, außer man tut es.
Win7_64 / LO_4.4.5.2
Win7_64 / LO_4.4.5.2
Re: ERR502 bei einer Formel aus Excel
mak hat geschrieben:Hallo,
bei uns in der Firma wird Excel und Calc benutzt.
Diese Excel Formel wird hergenommen um die Stunden bis zur nächsten Inspektion darzustellen.
Wenn die Maschine länger läuft, als die Stundenzahl wann sie zur Inspektion soll,
werden die minus Stunden in Rot "-2:00" dargestellt.
In Calc bekomme ich, wenn die Zeit noch positiv ist, eine ERR502 Meldung.
Die negative Darstellung funktioniert!! Es braucht auch nicht mit dem Ergebnis weiter gerechnet werden!
Wo ist der Fehler?
Oder gibt es einen anderen Ansatz, der die Aufgabe in beiden Systemen (Excel und Calc) löst?
=WENN(AI1206="";"";TEXT(ABS(AI1206-P1206);WENN(AI1206<P1206;"-";0)&"[hh]:mm"))
Für einen Tipp wäre ich echt dankbar!
Gruß
Mak
Vorschlag, der bei mir (LibreOffice 3.4.5 und Excel 97) ohne "Klimmzüge" funktioniert: Ergebnis (C1) = Insp-fällig (B1) - Laufzeit (A1) , alle Felder im Format > HH:MM <.
in C1 > =ABS(B1-A1) und bedingte Formatierung B1<A1, dann Schrift = ROT <.
Gruß jojo1056