Results 1 to 5 of 5

French to English dates

This is a discussion on French to English dates within the Excel Questions forums, part of the Question Forums category; I have the function: =DATEVALUE("01-"&R9&"-"&"2004") in a cell. R9 contains the string "Apr". This function is copied into 12 other ...

  1. #1
    New Member
    Join Date
    Feb 2003
    Posts
    21

    Default French to English dates

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: French to English dates

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

  3. #3
    New Member
    Join Date
    Feb 2003
    Posts
    21

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: French to English dates

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

    =DATE(2004,R9,1)

    ...where R9 contains the month number.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: French to English dates

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com