Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Konstanter Doppelpunkt in der Uhrzeitformatierung . EXEL 2000





Frage

Problem: Meine Frau ist Sachbearbeiterin in der Rechnungsabteilung einer Taxizentrale. In dieser Funktion rechnet sie zu Anfang eines Jeden Monats ca.3000 Fahraufträge des vorhergegangenen Monats mit großen Industrieunternehmen und diversen Krankenkassen ab. Hierzu ist es bei der Rechnungsstellung eines jeden einzeln ausgeführten Fahrauftrages notwendig, das jeweilige Datum und 2 Uhrzeiten (Fahrtanfang und Fahrtende) anzugeben. Also rund 6000 Uhrzeiten !Man soll es nicht glauben wie zeitaufwendig,fehleranfällig und umständlich es ist 6000 mal einen Doppelpunkt zwischen Stunden und Minuten über die Umschalttaste setzen zu müssen. Vieles habe ich schon versucht, doch bis dato ist es mir nicht gelungen einen konstanten Doppelpunkt zwischen Std.und Min. zu setzen so dass ich lediglich nur noch 1017 anstatt 10:17 eingeben müsste.- Auch unter Verwendung eines Makros hatte ich keinen Erfolg. Das Makro ist zwar erschienen, der Doppelpunkt war da, doch sobald ich eine Uhrzeit ohne Eingabe eines Doppelpunktes eintippte war das Makro verschwunden und alles war wie vorher. Wer kann meiner Frau helfen indem er mir mitteilt was ich tun muss um diese hilfreiche Vereinfachung der Uhrzeiteingabe zu realisieren ? In Anerkennung Ihrer Leistung wäre Ihnen unser beider Dank gewiss !! Mit Inter"netten" Grüßen Uli + Walter

Antwort 1 von cmkatz

Hallo,

Deiner Fraukann geholfen werden:

Zellen markieren - Format - Zellen - Reiter "Zahlen" - Benutzerdefiniert. Hier bei Typ "00\:00" (ohne Anführungszeichen!) eingeben.

Nachteil: Es kann damit nicht gerechnet
werden, weil keine Uhrzeit, sondern
ein Zahlenformat vorliegt.


Gruß
cmkatz

Antwort 2 von nighty

hi alle :)

hier meine makro loesung :)

reagiert auf zellenwechsel

es tastet automatisch die ganze tabelle ab ,prueft auf 4 zeichen und ergaenzt sie mit doppelpunkt

gruss nighty

hier einzufuegen

extras/makro/vbeditor/projektexplorer/dein-tabellennamen/doppelclick

Private Sub worksheet_Change(ByVal Target As Range)
With Worksheet
Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
alta = LastCell.Row
a = LastCell.Row
Do While Application.CountA(Rows(a)) = 0 And a <> 1
a = a - 1
Loop
alta = a
altb = LastCell.Column
b = LastCell.Column
Do While Application.CountA(Columns(b)) = 0 And b <> 1
b = b - 1
Loop
altb = b
lzeile = alta
lspalte = altb
For t% = 1 To lspalte
For t1% = 1 To lzeile
If Len(Cells(t1%, t%)) = 4 Then Cells(t1%, t%) = Mid$(Cells(t1%, t%), 1, 2) & ":" & Mid$(Cells(t1%, t%), 3, 2)
Next t1%
Next t%
End With
End Sub

Antwort 3 von nighty

hi alle :)

hier eine bessere variante,tastet tabellengroesse automatisch ab und sucht die zellen der vorgegebenen formatierung und ergaenzt die zelle mit einem doppelpunkt an dritter stelle.

gruss nighty

Rem extras/makro/vbeditor/projektexplorer/tabelle1/doppelclick
Private Sub worksheet_Change(ByVal Target As Range)
With Worksheet
Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
alta = LastCell.Row
a = LastCell.Row
Do While Application.CountA(Rows(a)) = 0 And a <> 1
a = a - 1
Loop
alta = a
altb = LastCell.Column
b = LastCell.Column
Do While Application.CountA(Columns(b)) = 0 And b <> 1
b = b - 1
Loop
altb = b
lzeile = alta
lspalte = altb
For t% = 1 To lspalte
For t1% = 1 To lzeile

rem hier deine zellenformatierung
rem bzw. anzupassen

If ActiveCell.NumberFormat = "h:mm" Then

Cells(t1%, t%) = Mid$(Cells(t1%, t%), 1, 2) & ":" & Mid$(Cells(t1%, t%), 3, 2)
End If
Next t1%
Next t%
End With
End Sub

Antwort 4 von Michael10

Hallo

@nighty,
die erste Version deines Makros funzt bei mir aber die zweite leider nicht und was auch noch sinnvoll wäre fals das geht, wenn das Makro bei vierstelligen Kommazahlen also zB. 20,50, nicht reagiert. Vielleicht fällt die dazu noch was ein dann könnte ich das Ding auch brachen.

Gruß Michael

Antwort 5 von nighty

hi alle :)

oder so,mit angabe eines begrenzten bereiches :)

duerfte sich am besten eignen :)

gruss nighty

Private Sub worksheet_Change(ByVal Target As Range)
With Worksheet

rem hier dein bereich,zur zeit
rem spalte2-10 und zeile 2-20

For t% = 2 To 10
For t1% = 2 To 20

If Len(Cells(t1%, t%)) = 4 Then
Cells(t1%, t%) = Mid$(Cells(t1%, t%), 1, 2) & ":" & Mid$(Cells(t1%, t%), 3, 2)
End If
Next t1%
Next t%
End With
End Sub

Antwort 6 von nighty

hi michael10 :)

die zweite version setzt diese formatierung der zelle "h:mm" oder eine selbst angegebene formatierung voraus und nur diese werden ergaenzt.

mit dem komma werd ich mal untersuchen :)

gruss nighty

Antwort 7 von nighty

hi alle :)

ups zweite loesung noch unbraucbar :(

gruss nighty

Antwort 8 von nighty

hi alle :)

nochmals eine ueberarbeitete fassung :)

@michael10
so ist besser oder :)
komma,punkt und alles was ober/unterhalb des ascicodecs der zahlen liegt ist ausgeschlossen :)

gruss nighty

With Worksheet
For t = 1 To Len(Target.Value)
If Mid$(Target.Value, t, 1) = "." Or Mid$(Target.Value, t, 1) = "," Then m = 1
If Asc(Mid$(Target.Value, t, 1)) < 48 Or Asc(Mid$(Target.Value, t, 1)) > 57 Then m = 1
Next t
If Target.Value <> "" And m = 0 And Len(Target.Value) = 4 Then
Target.Value = Mid$(Target.Value, 1, 2) & ":" & Mid$(Target.Value, 3, Len(Target.Value))
End If
End With
Application.EnableEvents = True
End Sub

Antwort 9 von nighty

hi alle :)

ups da fehlte ein stueck :)

Private Sub worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Worksheet
For t = 1 To Len(Target.Value)
If Mid$(Target.Value, t, 1) = "." Or Mid$(Target.Value, t, 1) = "," Then m = 1
If Asc(Mid$(Target.Value, t, 1)) < 48 Or Asc(Mid$(Target.Value, t, 1)) > 57 Then m = 1
Next t
If Target.Value <> "" And m = 0 And Len(Target.Value) = 4 Then
Target.Value = Mid$(Target.Value, 1, 2) & ":" & Mid$(Target.Value, 3, Len(Target.Value))
End If
End With
Application.EnableEvents = True
End Sub


gruss nighty

Antwort 10 von nighty

hi alle :)

hab noch ein wenig getueftelt und es noch mit der vorrausgesetzten formatierung geschafft :)

reagiert auf zellenwechsel und kontrolliert die zuletzt befuellte zelle ,kontrolliert dessen formatierung und wenn "h:mm" vorhanden ist so wird der inhalt ergaenzt.

gruss nighty

Private Sub worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Worksheet

Rem hier deine zellenformatierung
Rem bzw. anzupassen

If Range(Target.Address).NumberFormat = "h:mm" Then
Target.Value = Mid$(Target.Value, 1, 2) & ":" & Mid$(Target.Value, 3, 2)
End If
End With
Application.EnableEvents = True
End Sub

Antwort 11 von nighty

hi alle :)

da bin ich wieder :)

diesmal noch nit fehlerroutine das kopieren einfuegen weiter funktioniert :)

gruss nighty

Private Sub worksheet_Change(ByVal Target As Range)
On Error GoTo fehler
Application.EnableEvents = False
With Worksheet
Rem hier deine zellenformatierung
Rem bzw. anzupassen

If Range(Target.Address).NumberFormat = "h:mm" Then
Target.Value = Mid$(Target.Value, 1, 2) & ":" & Mid$(Target.Value, 3, 2)
End If
End With
Application.EnableEvents = True
End
fehler:
If Application.EnableEvents = False Then Application.EnableEvents = True
End Sub

Antwort 12 von Michael10

Hallo Nighty,

irgentwie hat dein Makro einen Fehler, wenn man z.B.7000 eingibt kommt 22:00 oder 6000 kommt 12:00 raus, es funzt nur wenn man z.B. 1520 eingibt dann steht es richtig da also 15:20.
Vieleicht kannst du ja nochmal schauen.

Gruß Michael

Antwort 13 von cmkatz

Hallo,

soll das nicht so sein?? Oder kennst du 70:00 Uhr?? Also, ich nicht...

Gruß
cmkatz

Antwort 14 von Michael10

Hallo

@cmkatz

ich denke du hast mich falsch verstanden auch wenn man 700 eingibt steht danach 22:00 da probier das Makro halt mal aus.

Gruß Michael

Antwort 15 von cmkatz

Hallo Michael,

ok, hsat ja recht. Nur "7000" oder "6000" hätte mich gewundert. ;-)

Hm... hab da selbst mal rumgebastelt, wenn man den Code verändert, also die Zeile

Target.Value = Mid$(Target.Value, 1, 2) & ":" & Mid$(Target.Value, 3, 2)

gegen

Target.Value = Mid$(Target.Value, 1, 1) & ":" & Mid$(Target.Value, 2, 2)

austauscht, funktionieren die Eingaben "000" bis "956", aber die 4stelligen nicht mehr...

@nighty
Man müßte in dem Code noch unterscheiden, was bei 3stelligen Zahlen und was bei 4stelligen Zahlen passieren soll.

Gruß
cmkatz

Antwort 16 von cmkatz

Nachtrag:

die Eingaben "000" bis "056" funktionieren doch nicht. Erst ab 100 geht's dann.

Gruß
cmkatz

Antwort 17 von cmkatz

So,

hab da auch mal was gefunden:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim s%, m%
If Range(Target.Address).NumberFormat = "h:mm" Then
With Cells(Target.Row, Target.Column)
    If .Value = "" Then Exit Sub
    If IsNumeric(.Value) And InStr(.Value, ":") = 0 And _
        InStr(.Value, ",") = 0 Then
        .NumberFormat = "h:mm"
        If Len(.Value) > 2 Then
            s = Left(.Value, Len(.Value) - 2)
            m = Right(.Value, 2)
        Else
            m = .Value
            s = 0
        End If
        .Value = s & ":" & m
    End If
End With
End If
End Sub


Gruß
cmkatz

Antwort 18 von nighty

hi micha10 :)

das ist kein fehler im programm :)

aufgabenstellung war einen doppelpunkt einzufuegen und im uhrzeitformat abzulegen,wie excel jetzt mit der uhrzeitformatierung umgeht weiss ich nicht,werd mich mal schlau machen bzw. hoffe auf hilfe von euch :)

gruss nighty

Antwort 19 von Lonestar

Einfacher:

Zellen markieren... Zellen formatieren.....

und anschliessend in dem gewünschten Uhrzeitformat einstellen!

Sollte ausreichen für 16:45Uhr einfach 1645 eingeben! Den Rest macht Excel....

Denk ich! Kanns momentan nicht kontrollieren, habe aber selber mit Uhrzeiten in Excel gearbeitet und die Daten ohne Doppelpunkt angegeben!

Antwort 20 von nighty

hi alle :)

testet mal einer :)

3 waere 3 min
30 waeren 30 min
333 waeren 3 stunden und dreiunddreissig min

25 waere 25 min
253 waere 2 stnden und 53 min

2525 ist eigentlich VERBOTEN da es ja nur bis 24 uhr geht,doch excel interpretiert dieses 24 stunden werden abgezogen 1,25 bleibt uebrig,also wird 1 stunde und 25 min dargestellt.

wow das nimmt ja formen an ,GRINS

gruss nighty

p.s.
testet schoen :) und berichtet dann :)

Private Sub worksheet_Change(ByVal Target As Range)
On Error GoTo fehler
Application.EnableEvents = False
With Worksheet
Rem hier deine zellenformatierung
Rem bzw. anzupassen

If Range(Target.Address).NumberFormat = "h:mm" Then
If Len(Target.Value) = 4 Then Target.Value = Mid$(Target.Value, 1, 2) & ":" & Mid$(Target.Value, 3, 2)
If Len(Target.Value) = 3 Then Target.Value = Mid$(Target.Value, 1, 1) & ":" & Mid$(Target.Value, 2, 2)
If Len(Target.Value) = 2 Then Target.Value = "00" & ":" & Mid$(Target.Value, 1, 2)
If Len(Target.Value) = 1 Then Target.Value = "00" & ":0" & Mid$(Target.Value, 1, 1)
End If
End With
Application.EnableEvents = True
End
fehler:
If Application.EnableEvents = False Then Application.EnableEvents = True
End Sub








Antwort 21 von cmkatz

Hi nighty,

das funktioniert. :-)

Gruß
cmkatz

Antwort 22 von cmkatz

Nochmal ich,

hat eigentlich schon jemand den von mir genanten Vorschlag ausprobiert?

Gruß
cmkatz

Antwort 23 von nighty

hi alle :)

@cmkatz
endlich ein mitstreiter :)

@all

jetzt muesste es aber funktioniere,hab 1 bis 4 stellig getestet(grrr ich hasse testen :))

die ersten zwei stellen sind die stunden also 1-23 bei der eingabe als korrekter wert

die dritte/vierte stelle sind die minuten,also 0-59 als korrekte eingabe.

sollten die grenzen ueberschritten werden so rechnet excel im 24 std./60 min takt die zahlen um.

hab ja noch nie mit zeiformaten gearbeitet oder eher selten ,daher wohl die ungereimtheiten :)

gruss nighty

Antwort 24 von nighty

hi cmkatz :)

daher liebe ich den wettbewerb,hab ihn schon vermisst :)

2 total verschiedene gut zu funktionierende loesungen :)

somit anfaenger gute ansaetzte geliefert bekommen(ups bin ja auch noch anfaenger :))

gruss nighty

Antwort 25 von cmkatz

Hi nighty,

bin ja ebenfalls noch Anfänger. Ich hol mir meine Anregungen hier und bei herber.de und bastele mir dann eine geeignete Lösung.Dabei lernt man aber auch ne Menge. Tolle Sache, so ein Forum! :-)

Gruß
cmkatz

Antwort 26 von nighty

hi cmkatz :)

deines hat jedenfalls gleich funktioniert tataa :)

meines schniff :( musste das sn fuer die testentwicklungphase herhalten doch wie sagt man doch gleich ,was lange waehrt waehrt gut :)

gruss nighty

Antwort 27 von Walter Kottmann

hi alle :)
Hallo Jung's ! Erinnert Euch! Ich war's der eure Köpfe rauchen ließ ! Und;Ich bin stolz auf Euch!

Meine besondere Bewunderung geht jedoch an NIGHTY der sich wie ein "hungriger Wolf" über das "Opfer EXEL" hermachte und mit der Inspiration eines LEITWOLFES letztendlich obsiegte.

Ihnen allen meinen herzlichen Dank!

mfg Walter



Antwort 28 von jola

Hallo Leute,

mit Makro ist gut und schön, geht aber auch so:
Spate A und B formatieren mit benutzerdefinertem Format 00":"00
Bei Rechnung, z.B.: Endzeit minus Anfangzeit in Spalte C
Formel =((B4-RECHTS(B4;2))/100+(RECHTS(B4;2)/60))-((A4-RECHTS(A4;2))/100+(RECHTS(A4;2)/60))
ergibt Stunden in Dezimalzeit.

Gruß Jola

Antwort 29 von cmkatz

Hallo,

danke für diese weitere Lösung des Problems, aber hast du mal aufs Datum geschaut?

;-)

Gruß
cmkatz