French to english problem

[YounesB3]

I have a formula as follow: =OFFSET(BA26,3,MATCH(TEXT(TODAY(),"dd-mmm-yy"),BA26:OG26,0)-1)

It works on my computer because I have a full english version of excel. There's another person who have a french version of excel, but that person switched the formulas to english version (setting in Excel).

The problem is, "TEXT(TODAY(),"dd-mmm-yy")", puts the date in a french format instead of english. Is there a setting somewhere to put everything in english?

Cross-post: http://www.mrexcel.com/forum/excel-q...ml#post3496507

[popipipo]

'TEXT(today();"[$-809]dd-mmm-yy")


Try this one

In file code for many more countries

[YounesB3]

'TEXT(today();"[$-809]dd-mmm-yy")


Try this one

In file code for many more countries

I tried 809, 1009 & 409 and they don't work. Is there a specific french code (french canadian)?

[popipipo]

Is there a specific french code

See the file I attached

[YounesB3]

See the file I attached

There is no french code.

[popipipo]

http://www.science.co.il/language/lo...asp?s=codepage
take a look at this site

[popipipo]

[$-C0C]
I found this one for french

[YounesB3]

[$-C0C]
I found this one for french

I tried the others (french code) and this one, but it only change from "Juin" to "Jun" (french month to enlgish month). The rest stays the same. So I have:

"dd-Jun-yy"

[Tony Valko]

Kind of a kludge but you can avoid the TEXT function like this...

Create these defined names...

Goto the Formulas tab>Define name

Name: Months
Refers to: ="JanFebMarAprMayJunJulAugSepOctNovDec"

Name: ToDay
Refers to:

=IF(DAY(NOW())<10,0&DAY(NOW()),DAY(NOW()))&"-"&MID(Months,MONTH(NOW())*3-2,3)&"-"&RIGHT(YEAR(NOW()),2)

Then, your formula becomes:

=OFFSET(BA26,3,MATCH(ToDay,BA26:OG26,0)-1)

I'm assuming you are aware that in your original formula the TEXT function is returning a TEXT string that looks like a date.

[popipipo]

Look at these 2 examples
Not sure if you need 'jjjj' or 'YYYY' for the year
Or maybe for the french edition of excel you have to change it

[YounesB3]

Kind of a kludge but you can avoid the TEXT function like this...

Create these defined names...

Goto the Formulas tab>Define name

Name: Months
Refers to: ="JanFebMarAprMayJunJulAugSepOctNovDec"

Name: ToDay
Refers to:

=IF(DAY(NOW())<10,0&DAY(NOW()),DAY(NOW()))&"-"&MID(Months,MONTH(NOW())*3-2,3)&"-"&RIGHT(YEAR(NOW()),2)

Then, your formula becomes:

=OFFSET(BA26,3,MATCH(ToDay,BA26:OG26,0)-1)

I'm assuming you are aware that in your original formula the TEXT function is returning a TEXT string that looks like a date.

I didn't try it (I've been trying for too long now), I think I'll just give the person my comp & password...

Thanks anyways!

Look at these 2 examples
Not sure if you need 'jjjj' or 'YYYY' for the year
Or maybe for the french edition of excel you have to change it

jj = dd
yy = aa

The thing is I can't really change it, because BA26:OG26 are all english format text dates.

[Tony Valko]

I didn't try it (I've been trying for too long now)... BA26:OG26 are all english format text dates.

OH well! It should work.

[YounesB3]

OH well! It should work.

I had the time to check it out and it does seem to work indeed

I will be checking with a french computer soon..!

Edit: It works on a french version! Thanks a lot!

[Tony Valko]

You're welcome. Thanks for the feedback!