dates formating

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
55
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
842
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
842
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
55
Office Version
  1. 365
  2. 2016
Dear Alex

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

Watch MrExcel Video

Forum statistics

Threads
1,133,622
Messages
5,659,926
Members
418,536
Latest member
Tezzies

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