AW: Daten umwandeln
28.07.2020 12:37:14
Herbert_Grom
Hallo Christian,
so hat es bei mir jetzt funktioniert:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iMonth%, iDay%, iFound1%, iFound2%, sMonth$, iYear%
On Error GoTo ende
If Target.Count > 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value "" Then
Application.EnableEvents = False
Cells(Target.Row, Target.Column + 1).NumberFormat = "dd/mm/yyyy"
Cells(Target.Row, Target.Column + 1) = Target.Value
Application.EnableEvents = True
End If
End If
If Target.Column = 2 Then
iFound1 = InStr(Target.Text, " ") - 1
sMonth = Left(Target.Text, iFound1)
Select Case sMonth
Case Is = "January": iMonth = 1
Case Is = "February": iMonth = 2
Case Is = "March": iMonth = 3
Case Is = "April": iMonth = 4
Case Is = "May": iMonth = 5
Case Is = "June": iMonth = 6
Case Is = "July": iMonth = 7
Case Is = "August": iMonth = 8
Case Is = "September": iMonth = 9
Case Is = "October": iMonth = 10
Case Is = "November": iMonth = 11
Case Is = "December": iMonth = 12
End Select
iFound2 = InStr(Target, ",") - 1
iDay = Mid(Target, iFound1 + 1, iFound2 - iFound1 - 2)
iYear = Right(Target, 4)
Application.EnableEvents = False
Cells(Target.Row, Target.Column + 1) = CDate(iDay & "." & iMonth & "." & iYear)
End If
ende:
Application.EnableEvents = True
End Sub
Servus