TEXT function international dates


Alberto Comino


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




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:




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 =

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.


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
| 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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads