dates formating

excel01noob

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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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))
 
Upvote 0
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.
 
Upvote 0
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 …​
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top