Tool with users using UK and Dutch date format settings

[Mokskie]

Dear all,


I have a question about the date format in a tool I made.

Some users use the Dutch format: dd-mm-jjjj uu:mm, and others the UK format dd/mm/yyyy hh:mm.

For the output of this tool I transform the data to a text format =text(E6;dd/mm/jjjj uu:mm) because the upload doesn't pick up the excel date format.

When I use the tool, everything works fine but when my colleague is running the tool(vba) the data is displayed as for example: 09/10/jjjj uu:10.

Is there an easy solution to let the tool work for everybody who uses to tool, whatever the date format is he or she uses and the date format is displayed als an text format.

VBA or just worksheet function can both be used.

Hope someone understands the issue and can help me fix it

Regards,
Mokskie

 

[mole999]

I have no idea if this would work, but considering that date and time are purely numbers. If the workbook_open event could detect the language of the user and place a marker somewhere, then as a page is called, use an if statement of sorts to apply the local format of dutch / uk (based on the storedto the appropriate cells for either the sheet or workbook as appropriate.

Macros off by default would make it fail to switch

 

[Comfy]

Try this:

Excel 2010
	A	B
1
2	01/10/2015 14:32	01/10/2015 14:32
Sheet1

Cell Formulas
Range	Formula
B2	=TEXT(A2,GetRegionFormat())

Option Explicit
Function GetRegionFormat() As String
Select Case Application.International(xlCountryCode)
    Case 31: GetRegionFormat = "dd-mm-jjjj uu:mm" ' Dutch
    Case Else: GetRegionFormat = "dd/mm/yyyy hh:mm" 'UK & Other
    End Select
End Function

Here are the country codes: https://support.microsoft.com/en-us/kb/213833

 

[Comfy]

No idea if this works but try:

=text(E6,"*dd/mm/yyyy hh:mm")

Edit: This doesn't work. Ignore.

 

[Mokskie]

Thanks for your replies!

I meant hours

Using Application.International is the solution I think. xlCountrycode gives you the language of office I think. For me and my colleague it both was 1. I am using Dutch formatting and English office.

With xlHourCode it is working

Function GetRegionFormat() As String

MsgBox Application.International(xlHourCode)

Select Case Application.International(xlHourCode)


    Case "u": GetRegionFormat = "dd-mm-jjjj uu:mm" ' Dutch
    Case Else: GetRegionFormat = "dd/mm/yyyy hh:mm" 'UK & Other
    
    
    End Select
End Function

 

[Mokskie]

Thanks!

 

[Comfy]

Thanks for your replies!

Using Application.International is the solution I think. xlCountrycode gives you the language of office I think. For me and my colleague it both was 1. I am using Dutch formatting and English office.

With xlMonthcode it is working

Thanks!

Cool, glad it works.