dates formating

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
86
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
86
Office Version
  1. 365
  2. 2016
Dear Alex

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

Forum statistics

Threads
1,141,062
Messages
5,704,055
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top