French to English dates

[MartinCregg]

I have the function: =DATEVALUE("01-"&R9&"-"&"2004") in a cell. R9 contains the string "Apr". This function is copied into 12 other cells such that R9 becomes S9, T9 and so on up to AC9. Cells R9 to AC9 contain the first three characters for the 12 months of the year. Everything works great when the operating system language is English. I have a colleague running this in France and because the months are spelled differently the DATEVALUE function fails. Does anyone know how to get around this?

Martin

 

[Mark W.]

Use the month value, 4, instead of the month abbreviation.

 

[MartinCregg]

That doesn't work due to the fact that the US and European date formats are different. i.e. in US: mm-dd-yy, Eur: dd-mm-yy.

 

[Mark W.]

Why does that matter if you use the DATE worksheet function...

=DATE(2004,R9,1)

...where R9 contains the month number.

 

[Mark W.]

BTW, you can convert your English month abbreviations into a month number using....

=MATCH(ref,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)

...regardless of the default language.