Geek Answers Handbook

How to make English date format work in French Excel?

I have an Excel file that should work on an English and French computer.

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

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

This works, but not in French Excel, where do I need to do

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

How can I print the same date for both my users regardless of their locale Excel langage?

English function TEXT vs French function TEXTE .

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

+4
source share
3 answers

To get the date in the right format, use:

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

I found this link that seems to be relevant to your problem:

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

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

Where you can specify the text to display in accordance with the desired language.

0
source

I dealt with this problem, following the assumptions that you are on an MSExcel server:

 1. I create a named formula (returns true when it 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") )

What is it. Hope this helps.

0
source

More articles:


All Articles