TEXT function international dates

[Alberto Comino]

Hi

I have a workbook that uses the formula TEXT(mydate, "mmm-yy") to convert
dates into text strings.

In my English version of Excel it is working fine. The problem is that I
share this spreadsheets with users that have a French version of Excel and
for them it's not working right. The referenced cell say '01/03/09' displays
'Mar-09' for me, whereas for them it will be 'mar-aa'. --French format for
dates is 'jj/mm/aa' instead of 'dd/mm/yy'.

Is there any solution? I do need to keep the text conversion as I use the
result to concatenate the output with other text strings.

Thanks in advance

Alberto

 

[T. Valko]

I remember this being discussed in a post a while back. Can't think of a
good keyword to search for it.

If the French version returns the month OK but fubars the year, maybe use
something like this:

=TEXT(A1,"mmm-")&RIGHT(YEAR(A1),2)

Or:

=TEXT(A1,"mmm")&"-"&RIGHT(YEAR(A1),2)

 

[Bob Phillips]

I don't have a non-English Excel on this machine to try it with but this
might work

Public Function TextDate(InDate As Variant, Optional DateFormat As String =
"dd/mm/yyyy")

TextDate = Format(InDate, DateFormat)
End Function

And use in a cell like =TextDate(TODAY(),"dd mmm yyy")

Because VBA is full English, I am wondering if it might return the tex
string for you. Might not, but worth a try.

If this doesn't work, I have another, less elegant way, but I would
appreciate you trying this first.

 

[Alberto Comino]

Thanks Biff

I did a shearch and found indeed a 2006 post -below- which seems to me a
pretty good solution. I'll try as well your sugggestion.

Alberto




Use this UDF:

Function FormatString(a As Range) As String
FormatString = a.NumberFormatLocal
End Function


It returns the formatstring from a cell in the local format. You can use
that instead of a textstring in functions like TEXT()


I have a multilingual version of Excel which has a few flaws so I couldn'
test it.
Please let us know if it works.


--
Kind regards,


Niek Otten



"(e-mail address removed)" <[email protected]>
wrote in message


| Hi,
|
| We are using English Excel, and others users using French or German Excel.
| When fomulates which are unsing date format are created in Excel File,
| others users using French or German Excel can't use the Formulate, because
| the format of the date are note the same.
|
| Exemple : In English "mmyyyy" in French "mmaaaa" or German "ttjjjj"
|
| How can we avoid this kind of problem, or how can we automate the
conversion
| of the formulates...
| May be it is a system parameters...
|

 

[Shane Devenshire]

What result does it return for the French version? I can't test this but if
the French version returns an error because the "mm-yyy" is not recognized,
why not try something like this

=IF(ISERR(MONTH(TEXT(A1,"mmm-yy"))),TEXT(A1,"mmm-aa"),TEXT(A1,"mmm-yy"))