when I run find and replace through vba it changes my dates to us format. So I have a column of dates, but they are all prefixed with text that I want to remove (like so Invoice Date:dd/mm/yyyy). When I use Ctrl + F and replace manually, it's all great. Removes the text, the date remains in it's original format dd/mm/yyyy. However, when using vba to do this it changes the dates to mm/dd/yyyy if the the day is less than 12 (ie months in a year). I've tried a number of different methods to convert it but they all seem to have the same problem. Here is my latest failure...
Sub DateConvert()
Sheets("Sheet1").Select
Dim strValue As String
Dim RowCount As Integer
Dim x As Integer
Dim DateValue As Date
RowCount = WorksheetFunction.CountA(Range("C1", Range("C1").End(xlDown)))
For x = 2 To RowCount
'changes cell value to a string
strValue = Cells(x, 3).Value
'removes unwanted text
Cells(x, 3).Replace _
What:="Invoice Date:", Replacement:=""
'changes to string to desired date format
DateValue = Cells(x, 3).NumberFormat = "dd/mm/yyyy"
Next x
End Sub
Please, someone spare me this misery before either the laptop or me go out the window. Thanks in advance
Dates are extremely annoying to work with. Your best bet for making sure you're working with the correct date is to use the DateSerial function, and then format the output as desired:
Sub DateConvert()
Dim ws As Worksheet: Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim rData As Range: Set rData = ws.Range("C2", ws.Cells(ws.Rows.Count, "C").End(xlUp))
If rData.Row < 2 Then Exit Sub 'No data
'Load range data into an array
Dim aData() As Variant
If rData.Cells.Count = 1 Then
ReDim aData(1 To 1, 1 To 1)
aData(1, 1) = rData.Value
Else
aData = rData.Value
End If
'Loop over array and perform conversion
Dim aDateValues As Variant
Dim i As Long
For i = 1 To UBound(aData, 1)
aDateValues = Split(Replace(aData(i, 1), "Invoice Date:", vbNullString), "/") 'Remove the extra text and pull the date values
aData(i, 1) = DateSerial(aDateValues(2), aDateValues(1), aDateValues(0)) 'Use DateSerial to guarantee correct date
Next i
'Output results to sheet with desired date format
With rData
.Value = aData
.NumberFormat = "dd/mm/yyyy"
End With
End Sub