dates formating

[excel01noob]

I have a monthly report which gives me the dates with this format
2021-03-01T00:00:00.000000

I have another column which is filling with with the 10 first left characters as yyyy-mm-dd

I want now to have this date showing in the European format as 01 March 2021
how do I do that?

This one does not work well


Sub dates()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "Q").End(xlUp).Row

For r = 2 To LastRow

Range("Q" & r).NumberFormat = "mmmm dd yyyy"

Next r

End Sub

 

[Alex Blakenburg]

Can you restate your requirement please.
Is it

I want now to have this date showing in the European format as 01 March 2021

or is it to have 3 TEXT columns, containing
Q - Day, R - MonthName, S - Year

And do you really want the Date as TEXT - you can't sort dates as text nor can you do any calculations with it nor can you reformat it to a different format ?

 

[Alex Blakenburg]

Based on your original requirement to convert the date to the European Format 01 March 2021.

The below will be much faster than what you are doing and is a variation of what @Marc L was suggesting.

Sub ConvertToDate()

    Dim FirstRow As Long
    Dim LastRow As Long
    Dim inRng As Range
    Dim outRng As Range
    
    FirstRow = 2
    LastRow = Cells(Rows.Count, "G").End(xlUp).Row
      
    Set inRng = Range("G" & FirstRow & ":G" & LastRow)
    Set outRng = Range("Q" & FirstRow & ":Q" & LastRow)
    
    With outRng
        .Value2 = inRng.Value2
        .NumberFormat = "dd mmmm yyyy"
        inRng.TextToColumns Destination:=outRng, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="T", FieldInfo:=Array(Array(1, 5), Array(2, 9)), TrailingMinusNumbers:=True
        .EntireColumn.AutoFit
    End With
    
End Sub

 

[excel01noob]

Dear Alex

Your last code does exactly what I wanted, thank you very much for your help!!!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.