CODEWITHSUNDEEP
excelvba
Sukanya
Sukanya

Reputation: 43

Change Date format MM-DD-YYYY to MM/DD/YYYY in macro VBA

I tried using format function as below

let the startdate = "06-05-2017"
End Date = format (startdate," MM/DD/YYYY"), but it is not getting changed. End Date is still showing as 06-05-2017. Please help me know what is wrong here.

Thanks !!

Upvotes: 1

Views: 15351

Answers (2)

olhptr
olhptr

Reputation: 87

You should escape your format with '\', if it contains '/'.

Debug.Print Format(Now, "MM\/dd\/yyyy")

Output:

03/25/2021

Upvotes: 1

teylyn
teylyn

Reputation: 35990

Excel probably does not recognise the start date as a date, but as text. You need to work out what your system's default date format is. To do that, you can type this in the immediate window:

?date

The returned result shows in the date format you need to use to assign a value to your start date variable. On my computer I need to use dd/mm/yyyy, so for me, this code works:

Sub test()
Dim startdate As Date
Dim enddate As String
startdate = "24/12/2017"
enddate = Format(startdate, "mm/dd/yyy")
Debug.Print enddate ' will show as 12/24/2017
End Sub

Upvotes: 1

Related Questions