Regional Settings change vba date formatting

[greegan]

I'm hoping this is something simple to do.

We are in Canada, so the date format standard is different from the US, its dd/mm/yyyy or dd/mm/yy (the day is first rather than month at least). My supervisor's station is set to Canadian formatting for the regional settings, and he won't change this.
The rest of the team is set for US settings. To clarifiy, we understand this to be mm/dd/yyyy or mm/dd/yy.

I have an application which has code which accepts the date format as m/d/yyyy format. This works no matter the regional settings, with a bit of a hitch...
When my supervisor enters the date, the date is switched around for his regional settings when the file saves with date as part of the filename.

I will add the opening code as well as the file save code here if needed. I'm hoping there is something I can use to check on regional settings and adjust the file save date formatting based on these settings.

-- g

 

[p45cal]

regional setting of windows can be checked with:
Application.International(xlCountrySetting)

see also International Property [Excel 2007 Developer Reference]
http://msdn.microsoft.com/en-us/library/bb177675(v=office.12).aspx

especially:
Application.International(xlMDY)
True if the date order is month-day-year for dates displayed in the long form; False if the date order is day-month-year.

 

[hippiehacker]

I use sometimes

Sub reg_set()
Dim dtTest As Date
 
dtTest = CDate("01/10/12")
 
Select Case Day(dtTest)
Case 1
    MsgBox "dd/mm/yy"
Case 2
    MsgBox "mm/dd/yy"
End Select
End Sub

to read regional setting or the following should work as well

Sub RegionalDateFormat()
    Dim DateOrder As String
    Dim DateSeparator As String
   
With Application
    Select Case .International(xlDateOrder)
        Case Is = 0
            DateOrder = "month-day-year"
        Case Is = 1
            DateOrder = "day-month-year"
        Case Is = 2
            DateOrder = "year-month-day"
        Case Else
            DateOrder = "Error"
    End Select
   
    DateSeparator = .International(xlDateSeparator)
End With

Debug.Print "Date Order: ", DateOrder
Debug.Print "Date Separator: ", DateSeparator
End Sub

 

[pgc01]

Hi

Not a solution, just a remark: can't you have the date input in international format: yyyy-mm-dd?

If you use the international date format as input, instead of some regional format, it should read the date without problem and should adjust automatically to whatever default regional date format a specific PC has.

This way you'd not worry if the program runs in US or Canada or Germany or wherever.

 

[greegan]

Hi

Not a solution, just a remark: can't you have the date input in international format: yyyy-mm-dd?

If you use the international date format as input, instead of some regional format, it should read the date without problem and should adjust automatically to whatever default regional date format a specific PC has.

This way you'd not worry if the program runs in US or Canada or Germany or wherever.

I would never had thought of this. Thank you
And thank you to all of you for the other responses. I will check out the link and the code this week. I will be happy to let you know how it turns out.

-- g