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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
Changing the number format won't do anything unless it is already recognised as being a date and that is not the case here.
CDate should work on the 10 character version.

VBA Code:
Range("Q" & r) = CDate(Range("Q" & r))

On the original version include the Left 10 function
VBA Code:
Range("Q" & r) = CDate(left(Range("Q" & r),10))
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
By the way if you are only using VBA because you couldn't do it with a formula then where you are currently doing the Left 10, try this replacing my Q5 with the appropriate cell reference.

Excel Formula:
=DATEVALUE(LEFT(Q5,10))

PS: for both the VBA and Formula options, once its converted to a date value you can format the column to whatever date numberformat you need to see it in.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
I have a monthly report which gives me the dates with this format
2021-03-01T00:00:00.000000
Without any helper column : first replace the 'T' by a space … Then use TextToColumns to convert text to true Excel dates if necessary …​
Once the column contains dates rather than text just format the column as the expected date format, the same you can do manually …​
 

excel01noob

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

ADVERTISEMENT

I don't want to change the data in the original column but want that data to be converted into the date format as I want
 

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
86
Office Version
  1. 365
  2. 2016
Range("Q" & r) = CDate(left(Range("Q" & r),10))

this seems to work fine but I could not follow what you wanted to say with this
=DATEVALUE(LEFT(Q5,10))
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Range("Q" & r) = CDate(left(Range("Q" & r),10))

this seems to work fine but I could not follow what you wanted to say with this
=DATEVALUE(LEFT(Q5,10))

It was just trying to say that if you didn't want to use VBA and you already had a column that said =Left(A5,10), you could replace your
=LEFT(A5,10)
with
=DATEVALUE(LEFT(A5,10))
So if I put the formula in Q5 then A5 would be the column that contains 2021-03-01T00:00:00.000000.
 

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
86
Office Version
  1. 365
  2. 2016
I work with colleagues around the world.
Isn't this Cdate function picking the system locale setting? Meaning if I run the macro on an European using French version of excel it might work but someone using it in the UK might not work.

Is there a way to override the system setting for the dates?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
What do you want the result to be ?
CDate will convert the yyyy-mm-dd to a Date Value. This is just a numeric representation of the date.

How it presents in the Immediate window will depend on your systems setting.
You are going to display it in Excel and there it will depend on how you format the cell.
If you format the cell as general or select the international date option, it will adjust to locale setting.
If you select a non-international date option or a custom option it will not change regardless of the locale.
 

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
86
Office Version
  1. 365
  2. 2016
I managed to do what I want by adding three columns for the year, month and day.
it does the trick, especially converting the month number into text but I feel that the macro seems to take a little longer to finalise.

Do you see if the below could be improved somehow?

Sub Get_DAYS_MONTH_YEAR()

Dim LastRow As Long

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

For r = 2 To LastRow

Range("Q" & r).Value = Mid(Range("G" & r), 9, 2)
Range("R" & r).Value = MonthName(Mid(Range("G" & r), 6, 2))
Range("S" & r).Value = Left(Range("G" & r), 4)

Next r

End Sub
 

Forum statistics

Threads
1,141,063
Messages
5,704,064
Members
421,326
Latest member
pfaustino

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