Trebor8484
Board Regular
- Joined
- Oct 27, 2018
- Messages
- 69
- Office Version
- 2013
- Platform
- Windows
Hi all,
I have an issue with the code below where I am attempting to amend incorrectly formatted dates so any instance of "." should change to "/".
The first piece of code seems to convert everything incorrectly:
[/IMG]
The second piece of code works slightly better but still has errors.
[/IMG]
Any suggestions please or a better way of dealing with this issue?
Thanks
I have an issue with the code below where I am attempting to amend incorrectly formatted dates so any instance of "." should change to "/".
The first piece of code seems to convert everything incorrectly:
Code:
Sub DateConv()
Dim LrDst As Long
Dim DstSht As Worksheet
Dim StrDate As String
Dim c As Range
Set DstSht = ActiveSheet
LrDst = DstSht.Cells(Rows.Count, "A").End(xlUp).Row
For Each c In DstSht.Range("A1:A" & LrDst)
If IsDate(c.Value) And c.Value <> "" Then
StrDate = Left(c, 2) & "/" & Mid(c, 4, 2) & "/" & Mid(c, 7, 100)
c.Value = Format(CDate(StrDate), "dd/mm/yyyy")
End If
Next c
End Sub
![bf1d9y.png](/board/proxy.php?image=http%3A%2F%2F%5BIMG%5Dhttp%3A%2F%2Fi64.tinypic.com%2Fbf1d9y.png&hash=2aba8f5566207c9975c94b8c2900381a)
The second piece of code works slightly better but still has errors.
Code:
Sub DateConv()
Dim LrDst As Long
Dim DstSht As Worksheet
Dim StrDate As String
Dim c As Range
Set DstSht = ActiveSheet
LrDst = DstSht.Cells(Rows.Count, "A").End(xlUp).Row
For Each c In DstSht.Range("A1:A" & LrDst)
If IsDate(c.Value) And c.Value <> "" Then
StrDate = Left(c, 2) & "/" & Mid(c, 4, 2) & "/" & Mid(c, 7, 100)
c = StrDate
c.Value = Format(CDate(c), "dd/mm/yyyy")
End If
Next c
End Sub
![2432eeo.jpg](/board/proxy.php?image=http%3A%2F%2F%5BIMG%5Dhttp%3A%2F%2Fi63.tinypic.com%2F2432eeo.jpg&hash=fa89dcedc3faeae7bd4676bda03d22e4)
Any suggestions please or a better way of dealing with this issue?
Thanks