Supportnet / Forum / Tabellenkalkulation

Hallo und guten Morgen, meine lieben Kollegen sollen eine Liste fuellen, in der unter anderem jeweils auch ein Datum pro Zeile eingegeben werden soll. Natuerlich jeweils ein anderes und meist nicht das aktuelle. Nun ist der Nummernblock ja recht griffig, aber Excel will partout einen Punkt (oder ein Minus-Zeichen) als Trenner zw. Tag, Monat und Jahr, sodass eine "schnelle" Eingabe der Daten kaum oder zumindest nicht besonders benutzerfreundlich gegeben ist. Wie also krieg ich es hin, meinen Kollegen eine Arbeitserleichterung einzubauen, die aus der Eingabe einer sechsstelligen Zahl (120806) automagisch das gewuenschte Datum (12.08.2006) zaubert, am Besten auch in der gleichen Zelle, in der die Eingabe erfolgte? Leise weinend, Der Thomas, im Vorab dankbar fuer jeden Tippppp

Hallo,

das geht mit VBA...

dazu aber noch eine Frage: wird das Datum immer in einer bestimmten Spalte eingegeben?

Wenn ich was weiß, poste ich...

Gruß
cmkatz

Hallo erneut,

Jepp, das Datum (bzw. als Vorstufe die sechs Ziffern en block -> 030406) werden immer in der gleichen Spalte eingegeben, genaugenommen sogar in drei Spalten:

____A________B________C________D____

Kunde A01 040406 Kunde B01 020306
Kunde A02 030106 Kunde B02 120805

Analog dann auch fuer Kundengruppe C (Spalten E und F).

Besten Dank. ;o)

LG,
Der Thomas

*die Ingrid machend*

Hallo nochmal,

sollte es die Struktur (drei Kundengruppen nebeneinander) zu umstaendlich machen, ein Script dafuer zu stricken, reicht auch eine Kundengruppe pro Zeile (also nur eine Spalte pro Zeile fuer die Datumsumwandlung). Hauptsache, meine Leute muessen nicht dauernd "umgreifen", um den Punkt oder das unsinnige Minuszeichen einzufuegen, wenn sie das Datum reinklopfen.

Besten Dank allen Interessierten,
Der Thomas

Hallo,

erster Versuch:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim col As Integer
    Dim tmpDat As Long
    Dim tag As Integer
    Dim monat As Integer
    Dim jahr As Integer

    col = ActiveCell.Column

    ´col = 2 => Spalte B
    ´col = 4 => Spalte D
    ´col = 6 => Spalte F
    If col = 2 Or col = 4 Or col = 6 Then
        If Len(ActiveCell.Value) > 0 Then
            tmpDat = ActiveCell.Value
            tag = Left(tmpDat, 2)
            monat = Mid(tmpDat, 3, 2)
            jahr = Right(tmpDat, 2)

            If jahr > 50 Then
                jahr = "19" & jahr
            Else
                If jahr < 10 Then
                    jahr = "200" & jahr
                Else
                    jahr = "20" & jahr
                End If
            End If

            ActiveCell.Value = tag & "." & monat & "." & jahr
        End If
    End If
    
End Sub

Diesen Code markieren und in Excel im VBA-Editor in Tabelle1(Tabelle1) rein kopieren...

Gruß
cmkatz

Hi cmkatz,

cooler Ansatz, funktionert leider nur ansatzweise.

Habe die zugehoerigen Spalten als "Datum" (tt.mm.jjjj) formatiert.

Gebe ich das Datum 020202 ein und tippe auf <Tab>, um in die naechste Spalte zu huepfen, steht ein errechnetes "Excel-Datum" drin (23.04.1955). Klicke ich mit dem Cursor auf die Zelle oder springe per Tastatur zurueck in die Zelle, dann bastelt das Script ein 20.20.2002.

Ich fuerchte, das isses noch nicht 100pro. Muss ich wohl weitergruebeln. Danke dennoch ganz doll fuer Dein Engagement!!!

LG,
Der Thomas

Hallo,

wie gesagt, war auch der erste Versuch! Hier der zweite:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Integer
    Dim row As Integer
    Dim tmpDat As Long
    Dim tag As Integer
    Dim monat As Integer
    Dim jahr As Integer
    
    col = Target.Column
    
    ´col = 2 => Spalte B
    ´col = 4 => Spalte D
    ´col = 6 => Spalte F
    If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Then
        Application.EnableEvents = False
        row = Target.row

        ´datum muß als 6stellige Zahl (ttmmjj) eingegeben werden!
        If Len(Cells(row, col).Value) = 6 Then
            tmpDat = Cells(row, col).Value
            tag = Left(tmpDat, 2)
            monat = Mid(tmpDat, 3, 2)
            jahr = Right(tmpDat, 2)

            If jahr > 50 Then
                jahr = "19" & jahr
            Else
                If jahr < 10 Then
                    jahr = "200" & jahr
                Else
                    jahr = "20" & jahr
                End If
            End If

            Cells(row, col).Value = tag & "." & monat & "." & jahr
        End If
        Application.EnableEvents = True
    End If
End Sub

Gruß
cmkatz

dritter Versuch...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Integer
    Dim row As Integer
    Dim tmpDat As Long
    Dim tag As Integer
    Dim monat As Integer
    Dim jahr As Integer
    
    col = Target.Column
    
    ´col = 2 => Spalte B
    ´col = 4 => Spalte D
    ´col = 6 => Spalte F
    If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Then
        Application.EnableEvents = False
        row = Target.row

        ´datum muß als 6stellige Zahl (ttmmjj) eingegeben werden!
        If Len(Cells(row, col).Value) = 6 Then
            tmpDat = Cells(row, col).Value
            tag = Left(tmpDat, 2)
            monat = Mid(tmpDat, 3, 2)
            jahr = Right(tmpDat, 2)
            
            If monat < 10 Then
                monat = "0" & monat
            End If
            
            If jahr > 50 Then
                jahr = "19" & jahr
            Else
                If jahr < 10 Then
                    jahr = "200" & jahr
                Else
                    jahr = "20" & jahr
                End If
            End If

            Cells(row, col).Value = tag & "." & monat & "." & jahr
        End If
        Application.EnableEvents = True
    End If
End Sub

Gruß
cmkatz

sorry, gleich noch eine Verbesserung hinterher:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Integer
    Dim row As Integer
    Dim tmpDat As Long
    Dim tag As String
    Dim monat As String
    Dim jahr As String
    
    col = Target.Column
    
    ´col = 2 => Spalte B
    ´col = 4 => Spalte D
    ´col = 6 => Spalte F
    If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Then
        Application.EnableEvents = False
        row = Target.row

        ´datum muß als 6stellige Zahl (ttmmjj) eingegeben werden!
        If Len(Cells(row, col).Value) = 6 Then
            tmpDat = Cells(row, col).Value
            tag = Left(tmpDat, 2)
            monat = Mid(tmpDat, 3, 2)
            jahr = Right(tmpDat, 2)
            
            If jahr > 50 Then
                jahr = "19" & jahr
            Else
                jahr = "20" & jahr
            End If

            Cells(row, col).Value = tag & "." & monat & "." & jahr
        End If
        Application.EnableEvents = True
    End If
End Sub

Also wenn nicht weiter damit gerechnet wird, ist es mehr als einfach, die Datumssplaten Benutzerdefiniert:
00"."00"."00
und schon geht es

Hui,

danke. Das Script Nr. 3 funktioniert in allen Faellen, in denen keine "fuehrende" Null bei Tag, Monat oder Jahr vorkommt, blendend. Falls bei Monat aber eine Null fuehrt, veraendert sich das erscriptete Datum, indem der Monat einfach durch einen unsinnigen anderen Monat ersetzt wird. Fuehrt bei Tag eine Null, so stimmt das ganze Datum nicht.

Wie kann man das abfangen?

LG,
Der Thomas

Hi Ultraerpel,

so geht´s natuerlich auch... besten Dank. ;o)

Der *kleinlaut erroetende* Thomas, die Scriptvariante von cmkatz dennoch irgendwie liebgewonnen habend

und mich läßt der Ehrgeiz nicht los... ;-))

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Integer
    Dim row As Integer
    Dim tmpDat As String
    Dim tag As String
    Dim monat As String
    Dim jahr As String
    Dim laenge As Integer
    
    col = Target.Column
    
    ´col = 2 => Spalte B
    ´col = 4 => Spalte D
    ´col = 6 => Spalte F
    If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Then
        Application.EnableEvents = False
        row = Target.row
        
        If Len(Cells(row, col).Value) > 3 Then
            tmpDat = Cells(row, col).Value
            laenge = Len(Cells(row, col))
            
            Select Case laenge
            Case 4
                tag = "0" & Left(tmpDat, 1)
                monat = "0" & Mid(tmpDat, 2, 1)
            Case 5
                tag = "0" & Left(tmpDat, 1)
                monat = Mid(tmpDat, 2, 2)
            Case 6
                tag = Left(tmpDat, 2)
                monat = Mid(tmpDat, 3, 2)
            End Select
            
            jahr = Right(tmpDat, 2)
            
            If jahr > 50 Then
                jahr = "19" & jahr
            Else
                jahr = "20" & jahr
            End If

            Cells(row, col).Value = tag & "." & monat & "." & jahr
        End If
        Application.EnableEvents = True
    End If
End Sub

Hi cmkatz,

funktioniert richtig gut. ;o) Reeeeiiiiicht!!!
Fuer die Plausibilitaet eines eingegebenen Datums (a la "33.13.99") sollten die lieben Kollegen dann bitte schon selbst sorgen. ;o)

Vielen Dank fuer die Hilfe, ich moechte Eure Zeit nicht laenger stehlen. Ihr habt mir sehr geholfen, meinen inneren Frieden (und vor allem den vor den bohrenden Fragen der Kollegen) wiederzufinden.

LG,
Der Thomas

Nur um den Besserwisser raushängen zu lassen (Mit deinem Wert kann man nicht Rechnen cmkatz), habe ich einige kleine Veränderungen an deinem Script vorgenommen (hoffe das war gestattet):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Integer
Dim row As Integer
Dim lngval As Variant
Dim tmpDat As String
Dim tag As String
Dim monat As String
Dim jahr As String

col = Target.Column

´col = 2 => Spalte B
´col = 4 => Spalte D
´col = 6 => Spalte

If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Then
Application.EnableEvents = False
row = Target.row

´datum muß als 6stellige Zahl (ttmmjj) eingegeben werden!
lngval = CLng(Cells(row, col).Value)
If Len(lngval) = 5 Then tmpDat = "0" & lngval Else tmpDat = lngval
If Len(tmpDat) = 6 Then
tag = Left(tmpDat, 2)
monat = Mid(tmpDat, 3, 2)
jahr = Right(tmpDat, 2)

If jahr > 50 Then
jahr = "19" & jahr
Else
jahr = "20" & jahr
End If

Cells(row, col).Value = CLng(DateSerial(jahr, monat, tag))
End If
Application.EnableEvents = True
End If
End Sub

Man muss die Wertanzeige dieser Spalte auf Datum stellen. Fehlerhafte Werte wandelt er um (zB den 33.03.06 in den 02.04.06)... Und man kann das Datum so aussehen lassen wie man will.

Mal ganz am Rande:
Ich finde, es ist ein Armutszeugnis (und ärgert mich immer wieder), dass EXCEL - und meines Wissens alle Office-Programme - diese eingabefreundliche Trennung von Eingabe- und Ausgabeformat nicht von selbst beherrscht!!!

Hi nochmal,

Das Ganze ist immer mehr als nur die Summe seiner Teile!

Ich muss neidlos anerkennen, dass dieses Forum KLASSE ist - auch und vor allem durch die Zusammenarbeit seiner Mitglieder und Leser - und der manchmal sicherlich recht unbedarften Fragen von Laien wie mir. ;o)

Vielen Dank fuer alle Hilfe. Weiter so!!!1

LG,
Der Thomas

@Nostalgiker: Da stimme ich dir fast voll und ganz zu. Mir fehlt das auch immer mal wieder, weil ich meine Daten eben nicht so eingeben will, wies das sch...excel von mir verlangt. Aber ich glaube man kann das bei Access irgendwie einstellen... Bin da aber nicht sicher..

@Risatara2:

für Verbesserungen bin ich jederzeit offen! ;-))

Gruß
cmkatz

hallo, wieso reicht als eingabe nicht datum.monat return?

Das könnte man auch ähnlich in VBA schreiben, wenn du sowas brauchst. Es könnte dann bei 4stelligen Zahlen die aktuelle Jahreszahl ergänzen. Da wäre dann aber kein Punkt dazwischen. Wenn du sowas brauchst, kann ich das auch machen ;)

Das dumme wäre nur, wenn das nicht in bestimmten Spalten so sein muss, dann muss man nämlich doch den Punkt einführen, und dann wirds kompliziert.

Hut ab vor allen,

die in so kurzer Zeit so viel zusammengetragen haben! Da ich mich oft für die einfachen Dinge am meisten begeistern kann, möchte ich den Vorschlag von Ultraerpel (Antwort 9) aufgreifen und ergänzen. Es ist ja meist nicht viel, was der Mensch braucht, um glücklich zu sein. In diesem Fall könnte man glatt mit 2 Spalten auskommen, eine zum Eingeben (1), Anzeigen und Ausdrucken und eine zum Rechnen (2) und für manchen ist vielleicht eine Variante ohne VBA lieber oder leichter zu erweitern.

(1) Siehe oben: Spalte mit benutzerdefiniertem Format 00"."00"."00 (Copyright Ultraerpel).

(2) lässt sich mit einfach mit Textfunktionen und DATUM machen:

B1: =DATUM(RECHTS(A1;2);LINKS(RECHTS(A9;4);2);LINKS(RECHTS("0" & A9;6);2))

Da die Eingabe "020202" (in A1) als Zahl behandelt wird und der Tag schnell mal seine führende Null einbüßt, muss man sie ihm wiedergeben, ganz klar. Die Parameter der
DATUM-Funktion sind also:
Jahr: = RECHTS(A1;2)
Monat: = LINKS(RECHTS(A1;4);2
Tag: = LINKS(RECHTS("0" & A1;6);2)

Falls man diese Spalte zur Anzeige benutzt, kann man sie noch als Datum "TT.MM.JJ" formatieren, aber Datumsrechnung geht auch ohne. - DerThomas hat nun die Wahl.

Gruß,
CaroS

• Excel: Auswertung Ausgaben 2006-Suche Formel **Dringend**
• 2 Excel Tabellen abgleichen
• Summe von Verweisen
• [Profifrage] Monate zählen in Excel oder Calc
• Nettoarbeitstage zwischen 2 Daten