Change date format using VBA Code

Question

I have this code to show the date in the following format like (Date: March 22, 2015) but it shows it as (Date: 3/22/2015) so please assist.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim dtmTest As Date
dtmTest = DateValue(Now)
Range("A1") = Format(dtmTest, "mmmm dd, yyyy")
Range("A1") = "Date : " & dtmTest
End Sub

Amen Jlili · Accepted Answer · 2015-03-22 14:12:38Z

1

You need to change the cell formatting to text to obtain the desired effect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sets cell formatting to string
Range("A4").NumberFormat = "@"
'fills range with formatted date
Range("A4").Value = Format(Now, "mmm, dd yyyy")
End Sub

answered Mar 22, 2015 at 14:12

Amen Jlili

1,9444 gold badges28 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GSerg Over a year ago

At which point, it is easier to assign the date format to the cell in the first place and then just set it to Now. At which point you don't need a macro, only the format on the cell and the formula, =NOW().

Nabil Amer Over a year ago

Many thanks Guys, it works as I wished. really appropriate it.

Amen Jlili Over a year ago

@NabilAmer, Gserg's comment says a method that uses no code. I think it's pretty good if you don't want to use VBA.

hpf · Accepted Answer · 2015-03-23 01:51:03Z

0

The solution posted works, but your original code was almost correct. You reformatted the date with the format function, but then overwrote it with the dtmtest variable. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim dtmTest As Date
dtmTest = DateValue(Now)
Range("A1") = "Date : " & Format(dtmTest, "mmmm dd, yyyy")
End Sub

answered Mar 23, 2015 at 1:51

hpf

4282 silver badges9 bronze badges

Collectives™ on Stack Overflow

Change date format using VBA Code

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related