Date in English format to French

Question

Hello,

I can't seem to change the format of a date from English to French display in Excel 2010.

A1 = 01/08/2010 and Excel interprets it as August 1, 2008 when it is actually January 8, 2008. I have many like this; how could I indicate the French format?

I've tried displaying it in custom mode with #-409, also attempted to change the date format in Windows 7, but I can't manage to do it. Does anyone have the solution?

Thanks
Florew

Frenchie83 · Answer

Hello  Did you select the date format *14/01/2001 (the first one in the date format list)? This applies the regional settings due to the presence of the asterisk  Best regards

Mike-31 · Answer

Hello,  Look at this formula to see, date format English in A1  =DATEVALUE(TEXT(A1,"mm/dd/yyyy"))  -- Best, Mike-31  A period of failure is a perfect time to sow the seeds of knowledge.

PHILOU10120 · Answer

Hello

You can break down the English date if you have a column to process

column A1 08/01/2008
column B1 =MONTH(A1)
column C1=DAY(A1)
column D1=YEAR(A1)
column E1 =NUM(SI(AND(LEN(B1)=1;LEN(C1)=1);0&B1&"/"&0&C1&"/"&D1;B1&"/"&C1&"/"&D1))

Patrice33740 · Answer

Hello,

The problem probably comes from the way this "English" date got into the file!!

For Excel, there is neither an English date nor a French date. Indeed, dates are stored as the number of days that have passed since what it considers to be the origin of time (the day before 01/01/1900).

It is during entry or import that the date is converted into a number of days based on the chosen format and stored.

Once the date is stored, changing the format does not change the date (the number of days) but simply how it is presented.

To change the date, you must first set the desired format and then re-enter or re-import the date.

PS: Or you can also use the Mike formula.

Best regards,
Patrice

Mike-31 · Answer

Re,

to summarize the possibilities there are also

=(MONTH(A1)"/"DAY(A1)"/"YEAR(A1))*1

=IF(ISTEXT(A1);DATE(RIGHT(A1;LEN(A1)-FIND("/";A1;FIND("/";A1)+1));LEFT(A1;FIND("/";A1)-1);MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)-FIND("/";A1)-1));DATE(YEAR(A1);DAY(A1);MONTH(A1)))

by searching we should find other possibilities
--
A+
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.

eriiic · Answer

Hello,

by the way, as I feel it hasn't been mentioned, during an import you can specify the date format D/M/Y for columns during step 3.
This way all the dates are correct.

eric

--
You should never respond to an unsolicited message...
Well, that's that.

Date in English format to French

6 réponses

Samsung j5 unable to answer

Scam of the suvedene highway

Minecraft restore the cursor and the bar.

My ps3 keeps shutting down on its own...

Samsung series 4 led tv logo display...

How to enable bluetooth on a samsung tv?

Ctrl key issue

[metin2] sura ...

Freebox tv locked step 2 or network

Error 46 (brother dcp-150c) cleaning impossible