CODEWITHSUNDEEP
exceldatelocalization
Cyril Gandon
Cyril Gandon

Reputation: 17058

How to make an english date format work in a french Excel?

I have an Excel file that must work for english & french computer.

I need to concatenate dates in the format yyyy-mm-dd, so for my english user, I need to do:

="your date = "&TEXT(A1,"yyyy-mm-dd")

That works, but not in french Excel, where I need to do

="your date = "&TEXT(A1,"aaaa-mm-jj")

How can I print the same date to both my users, independently of theirs Excel langage locale?

English TEXT function vs French TEXTE function.

And yes, the date format string are localized...

Upvotes: 5

Views: 11420

Answers (4)

Christian Bordère
Christian Bordère

Reputation: 1

I use this vba function in my formulas: Function DateToText(MyDate As Date) DateToText = Format(MyDate, "DD/MM/YYYY") End Function

Upvotes: 0

Hadi
Hadi

Reputation: 184

I used to cope with this issue as follow assuming you're on a eng MSExcel:

1. I create a named formula (returns true when it's a fr region MSExcel)
   name: xlFR
   refers to: = text(Today(),"d") = "d"
   click OK
2. in your formula you decide on xlFR

ex: = Text(today(), if(xlFR,"jj-mm-aaaa","dd-mm-yyyy") )

That's it. Hope this helps.

Upvotes: 1

BrakNicku
BrakNicku

Reputation: 5991

To get the date in right format use:

=YEAR(A1) & "-" & RIGHT("0" & MONTH(A1),2) & "-" & RIGHT("0" & DAY(A1),2)

Upvotes: 3

Peut22
Peut22

Reputation: 304

I found this link which seems to treat your issue :

If you are using the TEXT worksheet function because it is part of a larger formula, then you can instruct the function itself to use a different language for its output. You do this by including a language code (formally called an LCID) within brackets, in this manner:

=TEXT(A1,"[$-409]mmmm, yyyy")

Where you could specify the text to display according to the language you want.

Upvotes: 0

Related Questions