Date Format not working in excel vba

date format not changing in excel
excel vba date format dd/mm/yyyy
how to change date format in excel
excel date displays incorrectly
excel convert text to date dd/mm/yyyy
format date variable vba
excel datevalue not working
excel vba local date format

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!

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

Date Formatting in VBA code not working properly, Excel Facts. Excel Can Read to You. Click here to reveal answer. Guys I have searched for an Answer and I seem to be doing what everyone says I should do But I still get american format date in cell instead of UK From VB I populate cell "I" + whatever line im working on with a date from a textbox in userform one.

Format probably returns the date in the format you want, but it is then transformed into a date-value which is then printed in the default format for your system. To get them stored in the format you want, you could try something like:

Option Explicit

Sub cmdSubmit_Click()
    Dim StartDate As Date, EndDate As Date
    Dim s1 As String, s2 As String

    StartDate = #6/11/2018#
    EndDate = #6/24/2018#

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

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

    Debug.Print StartDate & " - " & s1          ' gave me 6/11/2018 instead of 11/Jun/2018
    Debug.Print EndDate & " - " & s2
End Sub

Alternately if you want them in a cell as dates, you could set the NumberFormat of the range you write them to, to be in your wanted format:

Sheet1.Range("A1") = StartDate
Sheet1.Range("A2") = EndDate
Sheet1.Range("A1:A2").NumberFormat = "dd.mmm.yyyy"

Excel Userform: UK (dd/mm/yyyy) format not working when written to , Formatting cells/columns to dd/mm/yyyy still switches the date to the US date format. VBA coding attempts: I have tried formatting the code of the  Changing the red date format to match the blue date format, then selecting all of them, right click, Format Cells, Category "Date", select the Type desired. The Red dates can be changed manually, or use some other excel magic to swap the day and month.

If you need to format the date in VBA you have 2 options:

  1. Change the regional settings;
  2. Write to Excel, format there and bring the .Text:

Option Explicit

Sub TestMe()

    Dim StartDate As Date

    StartDate = #6/11/2018#
    Cells(1, 1) = StartDate
    Cells(1, 1).NumberFormat = "DD/MMM/YYYY"
    Debug.Print Cells(1, 1).Text '11.Jun.2018

End Sub

6 Ways to Fix Dates Formatted as Text in Excel • My Online Training , Unregistered Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data  Dave Hawley - VBA Date Filter - Still Not Working. Posted by JAF on March 01, 2001 1:55 AM. Dave. Thanks for your suggestion as to a solution for my VBA Date Filter problem, but I've tried what you suggested and Excel is still not running the filter correctly.

Thanks everyone. I managed to figure it out, but I'm giving my vote to jainashish as it's closer to how I could use the result to query my access db.

VBA Date Format not Working, Problem with Excel VBA Date format. Hi .. I'm trying to handle dates in Excel Mac 2011 but it seems that whenever I access a date like 01/04/15  In VBA Format Date function you have the option to either define your own format or use some of the MS Excel predefined format. Firstdayofweek specifies the first day of the week. If it is not declared, the Format function assumes that Sunday is the first day of the week. This parameter is optional.

Problem with Excel VBA Date format, Re: VBA excel Date Problem. right click on cell > format cells > choose category as date and choose type as u need, u can select multiple cells  The format is as 2/06/2015 and Excel does not recognize as a date format to use with filters, even if I setup the cell as a date format . Text to Columns works great to convert that to DD-MM-YYYY, but I am wondering if I could have a formula to automatically make this work as soon as I open this spreadsheet.

[RESOLVED] VBA excel Date Problem-VBForums, Even though the process of manually applying a date format isn't very complicated, When working in Visual Basic for Applications, there are a few different properties NumberFormat Property And How To Format An Excel Date Using VBA. If you need to format the date in VBA you have 2 options: Change the regional settings; Write to Excel, format there and bring the .Text:

Excel VBA Date Format: 25 Macro Examples To Easily Set Date , The problem: Excel does not want to recognize dates as dates, even though through "Format cells - Number - Custom" you are explicitly trying to tell it these are  Hi I have to convert a serial date into this specific format "m/d/yyyy" for an entire column. When I do it manually through Excel Format Cells / Custom and create "m/d/yyyy" is works. When I write VBA code to convert it, it doesn't work? Columns("L:L").Select Selection.NumberFormat =

Comments
  • It's a known issue in Excel. Generally, it relates to regional settings of your system. By default, Excel picks the date from there. Check your regional settings, date format there would be mm/dd/yyyy.
  • @jainashish, my regional settings for short date is indeed M/d/yyyy but it won't work for me to change my regional settings because the file has to be shared across to multiple users. Thanks!
  • Have you tried Application.International(xlDateOrder) to fetch the end user date format and then manipulating the date accordingly?