Problem beim Verbinden von Datum und Uhrzeit mit Leerzellen

[Parris Tables]

Hallo zusammen,

nachdem Eberhard mein erstes Problem gelöst hat, stehe ich jetzt vor
einem Neuen.

Ich habe 2 Spalten, eine mit dem Datum, die andere mit der Uhrzeit.
Das Verbinden beider Zellen geht ja z.B. mit =TEXT(E4;"TT.MM.JJJJ") &
" " & TEXT(F4;"hh:mm").
Problem 1: in der Datumsspalte sind eine oder bis zu 5 Leerzellen. Es
soll dann immer das nächste Datum oberhalb genommen werden.
Problem 2: in der Uhrzeitspalte kann es Zeichen hinter der Uhrzeit
geben (2 Leerzeichen und 1-3 Zeichen). Die Zeichen (nicht die
Leerzeichen) sollen dann in der Spalte neben der zusammengefassten
Datum/Uhrzeit stehen.
Ich weiß, dass man mit "LINKS" die Uhrzeit extrahieren kann. Aber wie
ich die nur manchmal auftauchenden Zeichen hinter der Uhrzeit in eine
Zelle bekomme, weiß ich nicht.
Zur Veranschaulichung die Beispieldatei:
http://www.grunty.bplaced.net/ttf/problem.xls

Für eine Hilfe bin ich dankbar.

Gruß Jörg

[Claus Busch]

Hallo Parris,

Post by Parris Tables
Problem 1: in der Datumsspalte sind eine oder bis zu 5 Leerzellen. Es
soll dann immer das nächste Datum oberhalb genommen werden.
Problem 2: in der Uhrzeitspalte kann es Zeichen hinter der Uhrzeit
geben (2 Leerzeichen und 1-3 Zeichen). Die Zeichen (nicht die
Leerzeichen) sollen dann in der Spalte neben der zusammengefassten
Datum/Uhrzeit stehen.

probiere es mal in deinem Beispiel in H5 mit:
=WENN(E5="";GANZZAHL(H4)+WENN(ISTZAHL(F5);F5;ZEIT(--LINKS(F5;2);--TEIL(F5;4;2);));E5+WENN(ISTZAHL(F5);F5;ZEIT(--LINKS(F5;2);--TEIL(F5;4;2);)))
und formatiere die Ergebniszelle mit TT.MM.JJJJ hh:mm


Mit freundlichen Grüssen
Claus Busch

[Peter Schleif]

Post by Parris Tables
http://www.grunty.bplaced.net/ttf/problem.xls

Da Claus ja schon die Spalte H besetzt hat, nehme ich mal J und K:

J5: =VERWEIS(9^9;$E$5:E5)+VERWEIS(9^9;--LINKS(F5;SPALTE(1:1)))
K5: =WENN(ISTZAHL(SUCHEN(" ";F5));TEIL(F5;SUCHEN(" ";F5);99);"")

Peter

[Peter Schleif]

Post by Peter Schleif
J5: =VERWEIS(9^9;$E$5:E5)+VERWEIS(9^9;--LINKS(F5;SPALTE(1:1)))

In meinem Newsreader erscheinen die drei Zeichen "9 <caret> 9" in
Potenzschreibweise, was die Formel kaputt macht.

Falls das bei Dir auch so sein sollte bitte die Formel aus dem Quelltext
der Nachricht entnehmen oder hier:

http://groups.google.de/group/microsoft.public.de.excel/msg/8d7deba4a7bca349


BTW: Kann man verhindern, dass Texte durch die gängigen Newsreader
"interpretiert" werden?

Peter

[Parris Tables]

Hallo Claus und Peter,

danke euch beiden.
Beide Formeln für das Datum funktionieren perfekt. Da kann ich mir ja
die "Beste" aussuchen. ;-)
Wie man sieht, führen immer noch viele Wege nach Rom.
Auch die Abtrennung der Zeichen hinter der Uhrzeit funktioniert. Wie
ist es noch möglich die beiden Leerzeichen in Spalte K vor den
Buchstaben zu entfernen? Das müsste, wenn ich das richtig verstehe,
mit "TEIL" passieren. Ich habs noch nicht hinbekommen.

Wenn es nicht zuviel verlangt ist, könnt ihr mir eure Formeln für das
Datum kurz erklären? Wenn will ja auch was lernen, um das nächste Mal
selbst in die Puschen zu kommen.

Gruß Jörg

[Peter Schleif]

Wie ist es noch möglich die beiden Leerzeichen in Spalte K vor den
Buchstaben zu entfernen?

Mit GLÄTTEN.

=WENN(ISTZAHL(SUCHEN(" ";F5));GLÄTTEN(TEIL(F5;SUCHEN(" ";F5);99));"")

Peter

[Claus Busch]

Hallo Parris,

Post by Parris Tables
Auch die Abtrennung der Zeichen hinter der Uhrzeit funktioniert. Wie
ist es noch möglich die beiden Leerzeichen in Spalte K vor den
Buchstaben zu entfernen? Das müsste, wenn ich das richtig verstehe,
mit "TEIL" passieren. Ich habs noch nicht hinbekommen.

probiere es in K5 mal so:
=WENN(ISTZAHL(F5);"";GLÄTTEN(TEIL(F5;FINDEN(" ";F5)+1;5)))

Zu meiner vorherigen Formel sei gesagt, dass Excel das Datum als
Ganzzahl ab dem 01.01.1900 speichert, heute ist also Tag 40421. Die
Uhrzeit wird als Teil eines Tages gespeichert. 12:00 ist also 0,5 Tag.
Somit ist
31.08.2010 12:00 = 40421,5.
Da deine Tabelle immer mit einem Datum anfangen muss, kann ich also die
Ganzzahl aus der vorherigen Zelle nehmen, falls in der Datumsspalte kein
Wert vorhanden ist. Wenn ein Leerzeichen und/oder ein Text zu einer Zahl
dazukommt, wird diese Zelle nicht mehr als Zahl erkannt. Wenn also
ISTZAHL(F5) falsch ist, berechne ich die Zeit mit der ZEIT-Funktion.
Dort gibt man an ZEIT(Stunden;Minuten;Sekunden), in deinem Fall also
=ZEIT(LINKS(F5;2);TEIL(F5;4;2);)
Ich hoffe, dir damit geholfen zu haben. Falls nicht, kannst du ja
nochmals nachfragen.


Mit freundlichen Grüssen
Claus Busch

[Peter Schleif]

Post by Claus Busch
=WENN(ISTZAHL(F5);"";GLÄTTEN(TEIL(F5;FINDEN(" ";F5)+1;5)))

clever und elegant! Wenn Text dranhängt ist es keine Zahl mehr.

Peter

[Parris Tables]

Danke für Eure Infos und Formeln, haben mir sehr geholfen.
Musste mich plötzlich einer Zahnbehandlung unterziehen und daher den
Kopf "voll".
Wenn wieder Platz im Kopf ist, werde ich mich euren Erklärungen
widmen.

Gruß Jörg

[Peter Schleif]

Post by Parris Tables
Wenn es nicht zuviel verlangt ist, könnt ihr mir eure Formeln für das
Datum kurz erklären?

Den Datums-Anteil der Formel habe ich kürzlich in folgendem Thread
aufgeschnappt:

http://social.answers.microsoft.com/Forums/en-US/excelform/thread/268fa453-5ca4-4130-ba6b-7cf7c2d81e41/#2a38901c-1651-4652-af70-534d06ec6922


Der Zeit-Anteil kommt von hier:

http://www.excelformeln.de/formeln.html?welcher=100


Das Abtrennen der Zeichen hinter der Zeit und das Entfernen der
Leerzeichen habe ich selbst tatsächlich gemacht. WOW! :-)

Peter