Excel: Formelprobleme

[Wuschel]

Hab ein Problem...

Ich habe das Internet danach durchsucht, und auch die Excel-Frau unserer Firma gefragt, aber keiner kann mir helfen. Vielleicht ist ja ein Genius unter euch:

Soll                  Ist                 Differenz

Spalte H         Spalte I              Spalte J

12:15:00    13:15:00                 01:00

10:00:00     1:05:00                -08:55

12:15:00     0:00:00                -12:15

#WERT!

Wie ihr seht geht es um geleistete Zeiten... meine Ganze aus 13 Blättern bestehende Tabelle funktioniert einwandfrei, es kann nur eben diese Differenz der negativen und positiven Zeiten errechnen (Spalte J).
Folgende Formel steht in diesem Feld der Tabelle:

=TEXT(ABS(SUMME(WENN(LINKS(J6:J27)="-";TEIL(J12:J27;2;99)*-1;J12:J27*1)));WENN(SUMME(WENN(LINKS(J12:J27)="-";TEIL(J12:J27;2;99)*-1;J12:J27*1))<0;"-"; )&"[hh]:mm:ss")

Ich habe absolut keine ahnung wie sie funktioniert, oder wo der fehler sein könnte. Die Daten gehen von Zeile 6 bis einschließlich 27.

 

[Vomo]

So, so! Du hast also Minus-Stunden! 

Ich hab irgendwo so eine Arbeitszeitentabelle in Exel rumfliegen und muss mal schauen, wie das da gelöst wurde. Im Zweifelsfall schicke ich dir die einfach zu. Aber erst mal muss ich suchen.

[Wuschel]

Neeeneee ich hab Überstunden

Kann ja schlecht die unter Datenschutz fallenden Daten hier reinstellen die Formel aus der AZK hat mir leider nicht geholfen...

Ja ganz wichtig wäre, daß es einfach eine Spalte von oben nach unten zusammenrechnet, bei allem andern müsste ich das komplatte Ding umstellen.
Wenn das klappen würde wäre es echt meine Rettung, ich muß den Kram im moment per Hand berechnen.

[Aphiel]

also so wie ich das sehe, gibt es doch nur dann ein Problem, wenn die Endzeit numerisch kleiner ist als die Anfangszeit, weil dann mathematisch ein negativer Wert ermittelt wird, den man nicht mit einfach so den positiven Werten summieren kann, korrekt?

versuch doch mal, das Problem anders herum zu lösen, dass alle Differenzen gleich in positiven Zahlen in der Tabelle stehen

sowas wie

Code:

=WENN(I8>H8; I8-H8; (I8+24)-H8)

Dann musst du nur noch am Ende der Spalte J eine einfache Summenfunktion ausführen, da die Stundenanzahl nun immer positiv ausfällt.
Die Sache hat einen winzigen Haken, der aber in Deutschland nicht weiter ins Gewicht fallen sollte: die Zeiten müssen im 24h-Format eingetragen werden. Also nicht 3:00 schreiben, wenn damit nachmittags gemeint sein soll, sondern 15:00


mfg
Aphiel

[Wuschel]

Die Idee an sich ist gut, nur das Problem ist es muß jederzeit sichtbar sein, ob die Differenz positiv oder negativ ist, es ist eine Statistik.

Es handelt sich ja nicht um Uhrzeiten sondern um Stundenanzahlen.  (Die ich meist in Minuten bekomme und die umrechenen muß aber dafür hab ich schon ne Formel gefunden, die funktioniert.)

Das sind Fachleistungsstunden von Betreuern an betreuten Leuten. Es geht ja eben genau darum zu sehen, ob die Betreuer ihr Soll erfüllt haben. D.h, daß die Stunden im Feld "#Wert" auch negativ sein können. Am besten wäre es natürlich, wenn am ende 0 rauskommt, aber das sind eben Praxis und Theorie...

Oder verstehe ich deine Lösung nur einfach nicht? 

[Silent]

Was genau steht bei dir in den Zellen in Spalte J drin? Ist das selbst errechnet und dann als Text formatiert oder steckt da eine Formel dahinter? Excel tut sich soweit ich mich entsinne ein bisschen schwer mit Zeit x - Zeit y = Zeit z

[edit]es wäre auch gut wenn du vielleicht j12-j27 darstellen könntest, weil das anscheinend nur ein teil der liste ist, die gesamte dürfte j6-j27 sein oder?

mal zur erklärung der formel von oben:
text = formatiert eine zahl und gibt sie als text aus
abs = gibt den absolutwert einer zahl zurück
wenn = prüft ob ein argument zutrifft oder nicht und "reagiert" dann darauf
links = gibt nur den ersten teil einer zeichenkette zurück
teil = gibt eine bestimmte anzahl zeichen einer zeichenabfolge ab der bestimmten stelle zurück

[LoCo]

Müssen die Daten in Spalte J als Stunden/Minuten ausgegeben werden, oder reicht eine Zahl (zB 03:30h = 3,5h)?

Wenn nein, ist dies zwar vielleicht nicht die eleganteste Lösung, aber vielleicht hilft sie dir ja weiter:

Spalte H (Soll):    die Zellen haben folgende Formatierung hh:mm:ss
Spalte I (IST):    die Zellen haben folgende Formatierung hh:mm:ss
Spalte J:   die Zellen haben folgende Formatierung Zahl
      Formel:  =(B1-A1)*24

Damit lässt sich ohne Probleme eine Summe der Überstunden/Minusstunden errechnen.

[Wuschel]

Hmm... als ich versuche das mal zu erklären.

Zeile 6,
-Spalte A-F: Textinhalt
-Spalte G: Jahres-Soll (keine Formel)
-Spalte H: Monats-Soll [Formel: =G6/12]
-Spalte I: Monats-Ist (keine Formel, variiert in jeder Zeile)
-Spalte J: Differenz Montas-Soll und -Ist [Formel (=TEXT(ABS(I6-H6);WENN(H6>I6;"-"; ) &"hh:mm"))]

Funktioniert alles supi von Spalte G-J sind alle Formatierungen in Benutzerdefiniert [h]:mm:ss

Zeile 1-5: Überschriften ect.  pp
Zeile 6-27: sind die Daten drin
Zeile 28: Summenzeile:
-- Spalte G: =SUMME(G6:G27)
-- Spalte H: =SUMME(H6:H27)
-- Spalte I: =SUMME(I6:I27)

Funktioniert auch alles bestens, weil nur positive Stundenangaben.

und meine Horrorzelle ist J28, denn Spalte J kann negativ sein.

[edit @ LoCo]
und was soll das bringen, wenn ichs mal 24 nehme?
Sorry ich bin total der Noob.

[LoCo]

Wenn du die Iststunden minus der Sollstunden rechnest und die Zelle als Zahl formatiert hast, erhältst eine Zahl <1 ZB: 0,14. Diese mal 24 mulitpliziert ergibt die Stundenanzahl ZB 0,14 = 3 Stunden.

Du erhältst positive und negative "Stunden", die du ohne Probleme summieren kannst.

ABER: Anstatt 03:30 steht da dann aber 3,5 - oder anstatt -03:45 steht dann -3,75

[Wuschel]

ok...

ich hab ne formel, die aus minuten zeitstunden macht:

=ZEIT(0;KÜRZEN(C19);RUNDEN((C19-KÜRZEN(C19))*60;))

die Zelle c19 ist natürlich nicht richtig....

Wie mache ich jetzt daraus ein ganzes, daß mir das sicher und richtig zusammenrechnet? oder hilft das überhaupt?
Argh ich krieg noch alpträume...

[Silent]

Okay ich hab eine Lösung aber die ist extrem "schmutzig" funkt aber prima:

Füge eine Spalte zwischen I und J ein => Formel =I6-H6 => Formatierung als Zahl zweistellig
In die Zeile 28 dieser Spalte folgendes: =SUMME(J6:J27)*24
In die Zeile 29 dieser Spalte das hier: =AUFRUNDEN((TEIL(J28;4;3)*60);0)

Danach in Spalte K (deine Spalte J) => =TEXT(ABS(I6-H6);WENN(H6>I6;"-"; ) &"hh:mm")
in Zeile 28 der Spalte K: =TEXT(J28; "0")&":"&TEXT(J29; "00")

Spalte J nun ausblenden und keiner sieht wie brav im Hintergrund gerechnet wird un du in der Zelle J28 eigentlich nur umwandelst...

[Wuschel]

Ich probiere es gleich Morgen früh aus.

Wenn das klappt, wäre das echt der Burner.

Ich hoffe ich bekomme das auch bei dem 13. Blatt, der Zusammenfassung, hin. Das dürfte nicht so schwer sein, ist eigentlich fast genauso aufgebaut (da sind übrigens auch 12 Zeilen ausgeblendet, also passt schon zum Stil des Erschaffers dieses Monsters.)

[Silent]

Klar klappt das, habs ja anhand von deinem Beispiel selbst probiert   

Im Notfall postest du die Problematik von Blatt 13 auch hier, das kriegen wir schon hin 

[Wuschel]

DANKE!!!!

Ihr seid so Genies

*flüstert* ein kleines problem gibts noch.. DIe ausgabe in

K28 ist so: -265:312
In J29: -265,25
habs schon auf uhrzeit formatiert.

[Silent]

stimmt ist ein problem allerdings bekomm ich das auf die schnelle nicht so leicht hin, ausser:
- wenn deine stundenanzahl 3 stellig wird (j28) mußt du die formel in j29 anpassen und zwar auf => =AUFRUNDEN((TEIL(J28;5;3)*60);0)
danach funktioniert alles wieder so wie es sollte; die einzige änderung ist also statt der 4 eine 5; über 1.000 stunden bräuchtest du dann eine 6 etc.

generell ist die lösung nicht sauber; wenn du z.b. nur volle stunden hast gibts eine fehlermeldung; wenn das ergebnis positiv ist und zweistellig (also z.b. 80 stunden und 10 minuten) müßtest du die 4 auf 3 ändern etc.

momentan müßtest du das fallmässig einstellen, bin noch nicht schlauer wie man das recht einfach automatisieren kann