Working with formats for dates and time

[Mickael]

Hi

I work in an international environment where we are using Excel in different languages. Normally this works fine but when I use the TEXT function to format strings I run in to problems.

On a computer with English Excel I can use YYYY-MM-DD to display the year, month and day (2009-01-16) but on a Swedish one I have to use ÅÅÅÅ-MM-DD. The same goes for time (hh:mm vs tt:mm).


Anywhere I can change a setting to have the same codes for all countries or any other smart workaround?


/Micke

 

[Derek Brown]

You could create a User Defined Function (UDF) as follows:

Function myDateFormat() As String
Dim strDayCode As String
Dim strMonthCode As String
Dim strYearCode As String
Dim strSeparator As String
    strDayCode = Application.International(xlDayCode)
    strMonthCode = Application.International(xlMonthCode)
    strYearCode = Application.International(xlYearCode)
    strSeparator = Application.International(xlDateSeparator)
    myDateFormat = strYearCode & strYearCode & strYearCode & strSeparator & _
        strMonthCode & strMonthCode & strSeparator & strDayCode & strDayCode
End Function

and then call it in the TEXT function:
=TEXT(whatever you want here,MyDateFormat())
There is also xlHourCode, xlMinuteCode and xlSecondCode that you can use.
I only have access to a system with UK date settings (and I don't want to change it) so I have not been able to test it fully.

 

[Mickael]

Hi Derek and thanks.

Never used UDF so I have to look in to that. Any hint of where to start?


/Micke

 

[Stormseed]

Hi Mickael

Open your workbook, worksheet. Right click on the Sheet tab / Sheet name and select the option "View Code". The VB Editor will open. Now click on "Insert" and then "Module". Copy and paste the code which Derek posted for you in this module (white area on the right side of the window). Save your workbook.

Now, you can use the user defined function alongith the =TEXT() formula in excel. You can use it like the way Derek has shown you in the above post.

Hope this helps

 

[Derek Brown]

Looking at my code example, I think that I have missed something out.
"myDateFormat = strYearCode & strYearCode & strYearCode & strSeparator & _"
should be
"myDateFormat = strYearCode & strYearCode & strYearCode & strYearCode & strSeparator & _".
I guess that it must have worked in my 'test' because Excel was clever enough to "assume" the century.

 

[Stormseed]

Whoa ! I never saw your code...just made the OP aware of the walkthrough for placing the code

 

[Mickael]

Hi Derek

Have not had the time to check it yet. Will try during next week.

Thanks for your help

 

[fairwinds]

Hej Micke!

I would suggest to use formulas that are not dependant on regional settings.

E.g:

=TEXT(ÅR(A1);"0000-")&TEXT(MÅNAD(A1);"00-")&TEXT(DAG(A1);"00")
and
=TEXT(TIMME(B1);"00:")&TEXT(MINUT(B1);"00")

would automaticly translate to:

=TEXT(YEAR(A1),"0000-")&TEXT(MONTH(A1),"00-")&TEXT(DAY(A1),"00")
and
=TEXT(HOUR(B1),"00:")&TEXT(MINUTE(B1),"00")

when file is opened in english version and thus show correct result.

 

[Mickael]

Great!

Simple and works well.


Thanks!