Date Format not working in excel vba

I'm having some difficulties with my datepicker date. StartDate and EndDate are values from DatePicker and Debug.Print will give me "mm/dd/yyyy". I am trying to change the date format to "dd/mmm/yyyy" so that I will be able to compare it with my data from MS access. But it's not working.

Sub cmdSubmit_Click()
    Dim StartDate As Date, EndDate As Date

    StartDate = DTPickStart.Value    ' 6/11/2018
    EndDate = DTPickEnd.Value        ' 6/24/2018

    StartDate = Format(StartDate, "dd/mmm/yyyy")
    EndDate = Format(EndDate, "dd/mmm/yyyy")

    Debug.Print StartDate           ' gave me 6/11/2018 instead of 11/Jun/2018
    Debug.Print EndDate
End Sub

Any advice on what I am doing wrong here?

Thanks in advance!

Solution

As per the MSDN Application.International(Index) method returns information about the current country/region and international settings.

When you apply index as xlDateOrder

0 = month-day-year
1 = day-month-year
2 = year-month-day

Hence, we will be using this property to override the system behavior. This is especially useful when the Excel-VBA project is used across geographies with various individual regional settings.

Below, I am re-writing your code with a minor tweak. Format function returns the string but you are assigning it to a date which is causing this issue.

Sub cmdSubmit_Click()
    Dim StartDate As Date, EndDate As Date
    Dim strStartDate as String, strEndDate as String

    StartDate = DTPickStart.Value    ' 6/11/2018
    EndDate = DTPickEnd.Value        ' 6/24/2018

    strStartDate = Format(StartDate, "dd/mmm/yyyy")
    strEndDate = Format(EndDate, "dd/mmm/yyyy")

    Debug.Print StartDate           ' It will give 11/Jun/2018
    Debug.Print EndDate
End Sub