Default date format setting in VBA

Question

I recently changed my regional setting on Win 10 and this affected how my VBA macros interpret dates and my Range.Find() stopped working correctly. Below is example of simple macro that is used to return row of a date.


Sub a()
Dim dt_start As Date
dt_start = #11/6/2021# ' 11 Jun 2021
Dim rDateColumn As Range
Set rDateColumn = ThisWorkbook.Worksheets("2").Range("A:A")
rDateColumn.NumberFormat = "dd/mm/yyyy"

w = rDateColumn.Find(What:=Format(dt_start, "dd/mm/yyyy"), _
    LookIn:=xlFormulas, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows).Row

End Sub

When i try to run it it returns Run-time error '91':. I also tried this code with lookIn:=xlValues

Even though I set date format to "dd/mm/yyyy" VBA still reads it as "mm/dd/yyyy". See below screenshot.

I tried to convert the dt_start to string (Format(dt_start, "dd/mm/yyyy") and then back to date DateValue(str_date), however I am getting back I am getting "06/11/2021" which is 6 Novemeber 2021.

Below are my system regional settings:

and Excel date settings.

Any info much appreciated. Thanks!

What:=Format(dt_start, "dd/mm/yyyy") searches for a string but if your dates are real numeric dates that does not work, then you need to search for the numeric value. Convert your date into a double and serach for that What:=CDbl(dt_start). Numeric dates are saved in cells as double. Where the number is the amount of days since 1900-01-01 this way you can calculate with dates and format them with number format into any look you want. — Pᴇʜ
– Pᴇʜ, Commented Jun 11, 2021 at 6:04
Use What:=DateSerial(2021,6,11) to create your search date from year, month, day: this is unambiguous, no matter what your regional settings are. Also, are you sure you don’t want to search LookIn:=xlValues? — DS_London
– DS_London, Commented Jun 11, 2021 at 6:49
This bears repeating because it often causes problems: how Excel stores a cell value internally and what you see on screen are two different things. As @Pᴇʜ says, Excel stores Date information internally as a number: a double where the integer part represents the date and the decimal part represents the time of day). If you want to see the ‘raw’ format of the value in a cell hit Ctrl-Shift-#, and this will remove any formatting. The .NumberFormat setting only changes how the data appears, not how it is stored. Do this on cell A4 in your picture to check whether you have a ‘real’ Excel date. — DS_London
– DS_London, Commented Jun 11, 2021 at 7:03

Elbert Villarreal · Accepted Answer · 2021-06-11 06:23:22Z

This could help:

Sub a()
    'First get the date order (format) from the system.
    Dim myFormatDate As String
    Dim i: i = Application.International(xlDateOrder)
    'xlDateOrder Long
    'Order of date elements:
    '0 = month-day-year,
    '1 = day-month-year,
    '2 = year-month-day
    
    If i = 0 Then
        myFormatDate = "m/d/yyyy" 'NOT mm/dd/yyyy! will give you 00/00/000 not the same in strings.
    ElseIf i = 1 Then
        myFormatDate = "d/m/yyyy"
    ElseIf i = 2 Then
        myFormatDate = "yyyy/m/d"
    End If
    
    Dim dt_start As Date
        'dt_start = #11/6/2021# ' 11 Jun 2021
        'Never trust this... Now you know why!
    
    'Let us built!
    Dim DD: DD = 6
    Dim MM: MM = 11
    Dim YYYY: YYYY = 2021
    Dim w
    dt_start = DateSerial(YYYY, MM, DD)
    'Better to built the var-date using DateSerial
    'Now you have a date and you have the control!
    
    'Dim strDate As String: strDate = Format(dt_start, myFormatDate)
    'the line above is optional, if you want to use instead
    'of: Format(dt_start, myFormatDate)
    'This way: What:=strDate.
    
    Dim rDateColumn As Range
    Set rDateColumn = ThisWorkbook.Worksheets("2").Range("A:A")
    rDateColumn.NumberFormat = "dd/mm/yyyy"
    w = rDateColumn.Find(What:=Format(dt_start, myFormatDate), _
        LookIn:=xlFormulas, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows).Row
End Sub

Application.International property (Excel)

Returns information about the current country/region and international settings. Read-only Variant.

xlDateOrder: Order of date elements: 0 = month-day-year, 1 = day-month-year, 2 = year-month-day

Gustav · Accepted Answer · 2021-06-11 06:17:28Z

0

In VBA, expressions for date and time must follow either the ISO sequence or the "reverse" US format. So:

Dim dt_start As Date
' dt_start = #11/6/2021# ' 06 Nov 2021
dt_start = #6/11/2021#   ' 11 Jun 2021

answered Jun 11, 2021 at 6:17

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

Default date format setting in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related