converting range to date format not working

[GlennW81]

Hi,

I have a line of code that is supposed to convert range (that is currently in dd-mmm-yyyy format) to dd-mm-yyyy. However, the format does not seem to change. When I click on the format cells (from the number section on the Home tab) to check the format it indicates that it is in dd/mm/yyyy format, though it clearly is not and the sample is in the dd/mmm/yyyy format.
The conversion code is:

wsRData.Range(wsRData.Cells(2, 6), wsRData.Cells(lastrow, 6)).NumberFormat = "dd/mm/yyyy"

Below is a screenshot of displaying the date in the cells and the format selected from the format cells screen.

I need the code to correctly change the date to dd/mm/yyyy.
Are you able to help

Cheers
Glenn

 

[arthurbr]

If you have not applied manual alignment to col F your dates are not real dates but text looking like dates.
To convert them to real dates I usually select the column - Click Data - Text to columns - Finish.
If you can code this, you're good ( I'm not VBA savvy)

 

[Alex Blakenburg]

As arthur said the date column is most likely text. If this is likely to happen again then you can handle it in the code. This should work, given that your date format is pretty standard.

    ' Replace this
    wsRData.Range(wsRData.Cells(2, 6), wsRData.Cells(lastrow, 6)).NumberFormat = "dd/mm/yyyy"
   
    ' With this
    With wsRData.Range(wsRData.Cells(2, 6), wsRData.Cells(lastrow, 6))
        .NumberFormat = "dd/mm/yyyy"
        .Value = .Value
    End With

 

[MARK858]

Just an adaption on what @arthurbr has posted

Click Data - Text to columns - Next - make sure all the delimiter boxes are cleared - Next - Click the Date option - select the DMY option and click Finish.

or

Select the column and try running the code below

Sub ConvertDate()

    Selection.TextToColumns Selection.Cells(1, 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
End Sub

Edit: please try the code by @Alex Blakenburg first as with your data format the

.Value = .Value

should work

 

[GlennW81]

I was looking to avoid the clicking option of text to columns.
Thanks @Alex Blakenburg and @MARK858, the .value = .value worked.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.