CODEWITHSUNDEEP
vba
Nabil Amer
Nabil Amer

Reputation: 45

Change date format using VBA Code

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

Upvotes: 0

Views: 2408

Answers (2)

hpf
hpf

Reputation: 428

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

Upvotes: 0

Amen Jlili
Amen Jlili

Reputation: 1944

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

Upvotes: 1

Related Questions