Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Probleme mit Uhrzeiten in Excel





Frage

Ein frohes neues Jahr!!! Vielleicht kann mir jemand bei zwei Problemchen helfen: 1) Ich habe eine Datenbank mit Uhrzeiten, die durch die Konvertierung in dem Format :04:07angezeigt werden. Wie bekomme ich ein vollständiges Uhrzeitformat Std:Min:Sek angezeigt. Ansonsten müssen die ersten beiden Nullstellen mit der Hand nachgetragen werden, damit Excel rechnen kann. 2) Ich möchte in einer Spalte L2:L30000 die Anzahl der Posten zählen die z.B. im Juni vorgekommen sind. Bislang bekomme ich bei folgender Formel kein Ergebnis: =SUMMENPRODUKT((´00I´!L2:L30000;>=01.07.2005)*(´00I´!L2:L30000;<=31.07.2005)) Alternative über Zählenwenn? Vielen Dank Gruß Clarissa

Antwort 1 von M.O.

Hallo Clarissa,

1.) Hier könnte ich dir eine Lösung mit Hilfsspalte anbieten:
Angenommen deine Zeit steht in der Spalte A dann nimm folgende Formel und ziehe sie nach unten. Formatiere die Hilfsspalte im Zeitformat
= ZEITWERT("00"&A1)

2.) Ändere die Formel wie folgt ab:

=SUMMENPRODUKT((´00I´!L2:L30000>=DATWERT(“01.07.2005“))*(´00I´!L2:L30000<=DATWERT(“31.07.2005“)))

Gruß

M.O.

Antwort 2 von Cenzo

Super vielen vielen Dank.

Bei Frage 2 hatte ich eben schon die Lösung selber herausgefunden...konnte nicht aufgeben :-)

Bei der Frage 1 funktioniert dein Lösungsvorschlag super. Problem kommt nur auf, (hatte ich vergessen zu erwähnen) da einige Uhrzeiten richtig konvertiert sind (1:05:03). Die Formel ergibt natürlich bei diesem Format "Wert" an.

Vielleicht hast du ja auch hier eine Idee?

Antwort 3 von M.O.

Hallo,

dann fang das mit einer Wenn-Abfrage wie folgt ab:

=WENN(LINKS(D1;1)=":";ZEITWERT("00"&D1);D1)

Hier werden nur die Nullen ergänzt, wenn das erste Zeichen ein Doppelpunkt ist.

Gruß

M.O.

Antwort 4 von Cenzo

Danke, das probiere ich morgen aus.

Darf ich denn noch mal stören?

Bei folgender Formel zu Frage 2:
Ich frage hier insgesamt 7 Tabellenblätter ab.
Antwort ist hier Null?? Das stimmt definitiv nicht!
Die Abfrage bei einem Tabellenblatt funktioniert.

=SUMMENPRODUKT((´42I´!$J$2:$J$30000="07")*(´42I´!$K$2:$K$30000="2005"))*((´60I´!$J$2:$J$30000="07")*(´60I´!$K$2:$K$30000="2005"))*((´60D´!$J$2:$J$30000="07")*(´60D´!$K$2:$K$30000="2005"))*((´60R´!$J$2:$J$30000="07")*(´60R´!$K$2:$K$30000="2005"))*((´60T´!$J$2:$J$30000="07")*(´60T´!$K$2:$K$30000="2005"))*((´61I´!$J$2:$J$30000="07")*(´61I´!$K$2:$K$30000="2005"))*((´61D´!$J$2:$J$30000="07")*(´61D´!$K$2:$K$30000="2005"))

Antwort 5 von M.O.

Hallo Clarissa,

natürlich kannst du weitere Fragen stellen. Dafür ist dieses Forum ja schließlich da und wir helfen ja auch gern, wenn wir können :-).

Deine Summenproduktformel gibt schon das richtige Ergebnis aus, denn es gibt keine Zelle, die in allen Arbeitsblättern gleichzeitig alle Bedingungen erfüllt.

Du musst pro Blatt eine Summenproduktformel nehmen und diese dann addieren. Also so:
=SUMMENPRODUKT((´42I´!$J$2:$J$30000="07")*(´42I´!$K$2:$K$30000="2005"))+SUMMENPRODUKT"))*((´60I´!$J$2:$J$30000="07")*(´60I´!$K$2:$K$30000="2005"))+usw.

Gruß

M.O.

Antwort 6 von Cenzo

....Genial..........DANKE!!!

Hat alles funktioniert. Machst du diese Dinge beruflich?
Ich versuche zur Zeit Excel etwas zu vertiefen.

Antwort 7 von M.O.

Hallo,

ich arbeite beruflich viel mit Excel und habe dank des Supportnets noch eine Menge dazugelernt und lerne immer noch dazu :-).

Gruß

M.O.

Antwort 8 von Cenzo

Hi,

ein weiterer Denkanstoß wäre zu folgendem Problem sehr nett:-)...

Ist es möglich eine Formel zu erstellen, die z.B. in einer Spalte (B) mehrere Kontonummern hat und in Spalte (c) stehen die Beträge. Die Formel sollte so lauten:

Wenn Kontonummer 001, dann summiere die Beträge in Spalte C(natürlich nur auf das Konto bezogen); Wenn Kontonummer 002, dann summiere usw.............

=wenn(B2*B20000;"=001");summe(C2:C20000)
??????????????????????????????????????

LG
Clarissa

Antwort 9 von M.O.

Hallo Clarissa,

versuch es mal mit SUMMEWENN

Für Kontonummer 001:
=SUMMEWENN(B2:B20000;"001";C2:C2000)

Für Kontonummer 002 ensprechend:
=SUMMEWENN(B2:B20000;"002";C2:C2000)

Die Kontonummern sind hier als Text formatiert. Sind sie als Zahl formatiert musst du die Hochkommas weglassen.

Gruß

M.O.

Antwort 10 von Cenzo

Hi,

das war klar:-))) Hat natürlich alles funktioniert:-)))
Ich würde dir gerne anbieten, dir ebenfalls bei Fragen zu helfen.....
...ich glaube nur, dass ich in diese Lage nie kommen werde:-)

Vielen vielen Dank.

LG
Clarissa

Antwort 11 von M.O.

Hi,

Sag niemals nie! :-)))

Gruß

M.O.

Antwort 12 von Cenzo

...Hallo,

mit mir wird´s nicht langweilig :-)

folgendes Problem:

=SUMMENPRODUKT((GES!$K$2:$K$30000>="01.01.2005"<=31.12.2005")*($C$2:$C$30000="A")

Eigentlich möchte ich nur das Datum eingrenzen...z.B.01.01.2005<=31.12.2005 mit der Kennung "A"
Das Datum ist schon als Format "Datum" konvertiert.
Leider scheitere ich hierbei schon....fehlt bei der Eingrenzung ein (; oder "?)

Danke
LG
Clarissa

Antwort 13 von Cenzo

...und ich habe folgendes probiert:

=SUMMENPRODUKT(JAHR(GES!$K$2:$K$30000)=2005)*(GES!$C$2:$C$30000="A")

Antwort=0????????????

Antwort 14 von Cenzo

.....weitere Alternative mit dem Ergebnis Null!!!????

=SUMMENPRODUKT((GES!$K$2:$K$30000>=DATWERT("01.01.2006"))*((GES!$K$2:$K$30000<=DATWERT("31.12.2006"))*(GES!$C$2:$C$30000="I")))

Antwort 15 von M.O.

Hallo Clarissa,

ich bin es mal wieder :-))).

Wenn ich dich richtig verstehe, willst du mit der Summenproduktformel einer Spalte einen Wert zuweisen. Das geht aber nicht.
Dazu brauchst du eine WENN-Formel:
=WENN(JAHR(K1)=2006;"A";"")
Die Formel dann so weit wie benötigt nach unten ziehen.

Wenn du mit einer Summenproduktformel die Werte innerhalb eines Zeitraums ermitteln willst, würde ich dir raten das Anfangs- und das Enddatum in eine Zelle zu schreiben – so bist zu flexibel.
B1: Anfangsdatum
B2: Enddatum

=SUMMENPRODUKT((GES!$K$1:$K$30000>=B1)*( GES!$K$1:$K$30000<=B2)*( GES!$C$1:$C$30000))

Gruß

M.O.

Antwort 16 von Cenzo

Der eigentliche Sinn dieser Formel soll sein:

Zähle mir alle Posten, die im Januar (Bereich K2:K20000 = Angebe hier TT.MM.JJJJ) sind und noch die Kennung "A" (Spalte C2:C20000)...das gleiche für Feb, März usw.

Wenn das nicht funktioniert dann reicht es auch,
wenn ich nur frage im Jahr 2005 und A; 2005 und I;
2006 und A; 2006 und I

Das geht doch nur über Summenprodukt...oder vielleicht:


=WENN(JAHR(K2:K20000)=2006 und "A"dann soll er zählen....??????????????????????????

Antwort 17 von coros

Hallo Cenzo,

die Formel

=SUMMENPRODUKT((K2:K20000=2006)*(C2:C20000="A"))

sollte Dir das passende Ergebnis liefern.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 18 von M.O.

Hallo Clarissa,

auf den Monat bezogen könnte die Formel so aussehen:

=SUMMENPRODUKT((K2:K20000>=DATWERT("01.01.2007"))*(K2:K20000<=DATWERT("31.01.2007"))*(C2:C20000="A"))

Gruß

M.O.

Antwort 19 von Cenzo

Hallo M.O.,
Hallo Coros

M.O.:Deine Formel habe ich schon ausprobiert (Bericht 01.08.07 17:27Uhr) und der Wert ist immer Null???Ich verstehe es auch nicht.

Die Zelle mit dem Datum ist auch als Datum formatiert. Die Kennungen A & I habe ich als Text formatiert.

Coros: Bei deiner Formel kommt auch Null als Anwtort.

Es sind aber definitiv Posten enthalten:-)

Antwort 20 von Cenzo

.....Wenn ich folgendes ändere

=SUMMENPRODUKT((GES!$K$2:$K$20000>=DATWERT("01.01.2006"))*(GES!$K$2:$K$20000<=DATWERT("31.01.2006"))+(GES!$C$2:$C$20000="I"))

Letzten Teil addiere dann bekomme ich die Gesamten Posten angezeigt....dh...der Fehler muss in dem ersten Teil der Formel liegen.

LG
Clarissa

Antwort 21 von coros

Hallo Clarissa,

also die Formel von M.O. habe ich nicht probiert, aber meine zählt alle Zellen, bei denen in Spalte C eine A und in der gleichen Zeile in Spalte K der Wert 2006 steht.

Was ich nicht ganz verstehe, Du schreibst, dass in Spalte C als Kriterium ein A stehen muss, aber in der Formel, die Du in Deiner letzten AW geschrieben hast, wird als Kriterium ein I angegeben. Was ist denn nun richtig?

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 22 von Cenzo

...Hi,

die Kennungen können A oder I sein.
Ich möchte danach auswerten und nach Monaten bzw. alles vor 2005 nach Jahren. Das Datum steht aber TT.MM.JJJJ. Nicht nur 2006 oder 2005.

Ziel ist es: Die Anzahl der Posten für den Bereich "I" im Monat X und das Gleiche für "A"

Ich bekomme nur immer den Wert 0

Danke:-)))

Antwort 23 von coros

Hallo Clarissa,

also die Formel von M.O. funktioniert. Wenn in Spalte K ein Datumswert zwischen 01.01.2007 und 31.01.2007 steht und in der gleichen Zeile in Spalte C ein A steht, ist das Kriterium erfüllt und es wird gezählt. Der Fehler liegt also bei Dir in Deiner Tabelle. Stimmt das Datum, steht in Spalte C irgendwo ein A? Kontrolliere das noch mal.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 24 von Cenzo

Oliver...ich verzweifle gerade.....!!!!!
es funktioniert hier noch nicht mal mehr das....

=SUMMENPRODUKT(GES!$N$2:$N$20000>5000)*(GES!$C$2:$C$2000="A")

Er soll zählen wenn der Betrag >als 5000 & ein A

Da sind insgesamt 46000 Posten vorhanden....

Das muss etwas mit dem Format zu tun haben. Denn er sagt ja Wert ist Null....ich konvertiere jetzt die TXT Datei erneut.

Antwort 25 von coros

Hallo Clarissa,

in der Formel muss für den Bereich in Spalte C ebenfalls 20000 (zwanzigtausend) und nicht 2000 (zweitausend) angegeben werden, dann funktioniert die Formel auch.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 26 von Cenzo

...so erneut konvertiert....

das eine funktionierte jetzt wieder (Grund: ich musste erst einige Nullstellen suchen und ersetzten)

Mit der einen Formel (Posten nach Monaten errechnen) keine Chancse...
Das liegt 100% an der Formatierung des Datum...

Frage ich nur folgendes ab:

=SUMMENPRODUKT(($K$2:$K$20000>=DATWERT("01.01.2006"))*(($K$2:$K$20000<=DATWERT("31.01.2006"))))

Ist das Ergebnis auch Null....

Antwort 27 von Cenzo

...So ich gebe es auf.

Alternative: Wie kann ich ein Datum z.B. 26.05.2006 umändern, dass es nur 2006 ist? Vielleicht kann ich dann die Anzahl ermitteln?

Über:
das stimmt aber nicht so ganz...
=ERSETZEN(K2;2;6;"")

schönen Abend noch:-))

Antwort 28 von coros

Hallo Clarissa,

sind den die Felder, in denen das Datum steht als Datum formatiert oder eventuell als Text?

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 29 von M.O.

Hallo Clarissa,

vielleicht kannst du ja auch mal eine Beispieltabelle einstellen. z.B. hier: www.netupload.de
Stelle den Link dann hier ein.

Gruß

M.O.

Antwort 30 von M.O.

Hallo Clarissa,

noch ein kleiner Nachtrag:

Das Jahr kannst du z.B. in einer Hilfsspalte mit
=Jahr(K2)
ermitteln.

Gruß

M.O.

Antwort 31 von Cenzo

Ihr seid einfach super,

das mit der Hilfsspalt hat funktioniert!!! DANKE

Coros: Ja das Datum ist auch als Datum formatiert.
dh. Ich kann doch in der Formel auch nicht mit DATWERT arbeiten oder ?

M.O.: Ich schau mal am Wochenende, dass ich mal ein Beispiel hochlade.

Das lässt mir auf die Dauer sowieso keine Ruhe :-)))

LG
Clarissa

Antwort 32 von coros

Hallo Clarissa,

doch, Du kannst mit Datwert arbeiten. Meine Frage war so gemeint, dass die Zellen, die den Datumswert enthalten in Spalte K auch als Datum formatiert sein müssen, damit die Formel funktioniert. Welcher Datumstyp dabei verwendet wird ist unwesentlich, hauptsache die Zellen sind im Datumsformat vorhanden.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 33 von Cenzo

Hallo Jungs,

bin nach einer längeren Sinusitis wieder unter den Lebenden...und brauche schon wieder eure Hilfe.

Die letzte Sache hat super funktioniert. Das lag nur an der Feldformatierung.

Die neue Frage, die ich habe:

wie kann ich aus solchen Zahlen vernünftige Werte bekommen?

z.B.: 00001085+; 00058604-;

Ergebnis wäre: 1.085,00€ ; -58.604,00€

Die Forderungen sollte er auch als negativ beibehalten.

LG
Clarissa

Antwort 34 von M.O.

Hallo Clarissa,

schön, dass es doch noch geklappt hat.

Zu deinem neuen Problem habe ich noch einige Fragen.
Stehen die Zahlen so in einer Zelle?
Sind es immer nur zwei Zahlen?
Haben die Zahlen immer die selbe Länge?

Gruß

M.O.

Antwort 35 von Cenzo

Hi,

die Zahlen stehen untereinander. Es kommt darauf an, ob es eine Forderung oder ein Guthaben ist. So-mit setzt er ein + oder - hinter dem Betrag (in der gleichen Zelle) mit unterschiedlich vielen Nullstellen vorne. Insgesamt hat die Datei über 26000 Zahlen (die als Text konvertiert wurden) untereinander stehen.
0000158+
0000025-
0012589+ usw.

Ich hatte mal angefangen mit Suchen und ersetzten. Oder mit =wenn(links(A1;1)<>“0“…….aber das ging nicht.

Er sollte hieraus eine brauchbare Zahl formatieren und dabei nicht + und – weglassen (ist für das Ergeb-nis ja nicht unwichtig)

LG
Clarissa

Antwort 36 von M.O.

Hallo Clarissa,

dann probier mal die folgende Formel:

=WENN(RECHTS(A1;1)="-";WERT(LINKS(A1;LÄNGE(A1)-1))*-1;WERT(LINKS(A1;LÄNGE(A1)-1)))
In A1 stehen dabei die Zahlenwerte.

Die Zellen kannst du dann einem Zahlenformat formatieren.

Gruß

M.O.

Antwort 37 von Cenzo

....Ich kann nichts mehr sagen.....der Hammer...Ja es geht !!!!

Kannst du mir versuchen die Formel mit Worten zu erklären (damit ich sie auch verstehen kann:-)) mit Links und Rechts sowie mit Länge habe ich seltens gearbeitet.

DANKE

Antwort 38 von M.O.

Hallo Clarissa,

gern doch :-)).

WENN(RECHTS(A1;1)="-";: hier wird geprüft, ob das 1. Zeichen von Rechts (also das letzte Zeichen) der Zelle A1 ein Minus-Zeichen ist
Falls ja:
WERT(LINKS(A1;LÄNGE(A1)-1))*-1
WERT: diese Funktion wandelt eine als Text vorliegende Zeichenkette in eine Zahl um.
Mit LÄNGE(A1) wird die Länge des Textes ermittelt und da das letzte Zeichen das Vorzeichen ist, aber nur die Zahl gebraucht wird, wird 1 subtrahiert. So bekommt man die Anzahl der Zeichen, die die Zahl bilden. Mit LINKS wird diese Zahl aus dem Text "herausgelöst". Die nun umgewandelte Zahl wird mit -1 multipliziert, da der Wert ja negativ ist.
Im SONST-Teil der Wenn-Formel geschieht genau das Gleiche, nur wird hier nicht mit -1 multipliziert, da das Vorzeichen hier ja Plus ist.

Ich hoffe die Erklärung ist einigermaßen verständlich.

Bis bald :-)).

Gruß

M.O.

Antwort 39 von Cenzo

Danke Danke:-)))

Ich werde jetzt mal damit spielen und üben.

LG

Clara

Antwort 40 von Cenzo

Hallo Zusammen,

ich habe eine kleine Rückfrage zu folgendem Problem:
Ich benötige eine Formel, mit der ich mehrere Kriterien erfülle und der Rest soll dann auch noch summiert werden.

Beispiel:

'=summenprodukt(DTA!$C$2:$C$29999;"=137546")*(DTA!$E$2:$C$29999;"01")*(DTA!$A$2:$A$29999;"GB")*(DTA!$L$2:$L$29999)

Wenn die ersten 3 Bedingungen erfüllt sind, dann soll er die Summe bilden!

Für jede Hilfe bin ich dankbar.
(Wann benutze ich eigentlich Summewenn bzw. Summenprodukt?)

Gruß
Clarissa

Antwort 41 von rainberg

Hallo Clarissa,

probier's mal so

=SUMMENPRODUKT((DTA!$C$2:$C$29999="137546")*(DTA!$E$2:$E$29999="01")*(DTA!$A$2:$A$29999="GB")*DTA!$L$2:$L$29999)

Gruß
Rainer

Antwort 42 von Cenzo

Morgen Rainer,

als Ergebnis erhalte ich immer Null. Das kann nicht stimmen, da wirklich Werte vorhanden sind.

Es sollen nur die Werte in der Spalte "L" summiert werden, wenn das Konto (Spalte C), der Tag 01 (Spalte E) ist und das Kennzeichen in A "GB" ist. Die Zellen sind auch richtig formatiert.

Wo liegt der Unterschied bei SUMMEWENN?

Gruß und DANKE

Clarissa

Antwort 43 von rainberg

Hallo Clarissa,

das liegt wahrscheinlich an den Zellformaten, welche den Angaben in der Formel entsprechen müssen.
Du gibst in der Formel das Konto und den Tag als String (Textwert) an,
liegen diese Formate in den entsprechenden Spalten auch so vor oder sind es etwa Zahlenwerte???
Es muss Übereinstimmung herrschen, dann funktioniert auch die Formel.

Vielleicht kannst Du eine Musterdatei hochladen, über
http://www.netupload.de

Gruß
Rainer

Antwort 44 von Cenzo

Hi,

Die Zellen sind richtig formatiert. Konto, Kennziffer und den Tag als Text. Die Summe als Zahl. Leider kann ich die Datei nicht hochladen, da dies Firmendaten sind.

Aber hier ein Beispiel:

Konto Kennziffer Tag Monat Jahr Summe
111 gb 01 05 2007 10€
111 gb 01 05 2007 20€
112 gl 02 05 2007 30€


=SUMMENPRODUKT(A5:A7="111")*(B5:B7="gb")*(C5:C7="01")*(F5:F7)

erwartetes Ergebnis wären 30EUR
Gruß

Clarissa

Antwort 45 von rainberg

Hallo Clarissa,

auf das Beispiel bezogen gilt folgende Formel

=SUMMENPRODUKT((A5:A7="111")*(B5:B7="gb")*(C5:C7="01");F5:F7)

Gruß
Rainer

Antwort 46 von Cenzo

Sorry,

muss weiter nerven. Bekommst du denn hier ein Ergebnis raus? Bei mir ist das ganze "Null". Ich verzweifel.

Ich versuche aber mal meine Testdatei hochzuladen.

Gruß

Clarissa

Antwort 47 von Cenzo

So...hoffe es hat funktioniert:

http://www.netupload.de/detail.php?img=d20601563e3bb4046cd6348752faea2e.xls

Gruß

Antwort 48 von rainberg

Hallo Clarissa,

verwende meine Formel, so wie ich sie gepostet habe und korrigiere die Kontonummern in Spalte A, dann klappts auch.
Ich hatte schon darauf hingewiesen, dass Übereinstimmung herrschen muss.

Gruß
Rainer

Antwort 49 von Cenzo

Supi,

danke,du hast Rech (natürlich):-) Was ich aber an der ganzen Sache nicht verstehe...reicht es nicht aus die Spalte mit den Kontonummern zu markieren und über "Format- Zellen- Text" zu gehen? Schreiben ich die Zahlen (Kontonummern) erneut in die Zellen rechnet er.

Ich kann ja schlecht in der Originaldatei 30.000 Kontonummern abtippen....vielleicht habe ich hier auch einen Denkfehler...

...aber nochmal vielen Dank...

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: