Tool with users using UK and Dutch date format settings

Mokskie

New Member
Joined
Sep 15, 2015
Messages
6
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
Try this:

Excel 2010
AB
1
201/10/2015 14:3201/10/2015 14:32
Sheet1
Cell Formulas
RangeFormula
B2=TEXT(A2,GetRegionFormat())


Code:
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
 
Upvote 0
No idea if this works but try:

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

Edit: This doesn't work. Ignore.
 
Last edited:
Upvote 0
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 :)


Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top