View original (French)

Date English to French

Gemstar -  
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   -
Hello,

First of all, sorry if this is a duplicate, but I couldn’t find an answer to my problem in other posts/topics.

So, I have a file where some dates are in English.
In the format “01-DEC-2011” for example.
However, when I change the format of my cells to a date format, some months are not converted because the months are in English.

FEB needs to become Févr
APR needs to become AvR
MAY needs to become MAi
AUG needs to become AUoût
DEC needs to become DéC

When I change them manually it works.

Do you have any tips or VBA code to convert them? Indeed, I have over 60 files each containing about 400 dates.

Thank you for your help.

Best regards,

Remy

2 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    try it like this for a date in English format Feb-1-2017 entered in A2 

    =(INDEX({1;2;3;4;5;6;7;8;9;10;11;12};MATCH(LEFT(A2,FIND("-",A2)-1);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0))"/"&RIGHT(A2,LEN(A2)-FIND("-",A2)))*1

    or with SEARCH 
    =(INDEX({1;2;3;4;5;6;7;8;9;10;11;12};MATCH(LEFT(A2,SEARCH("-",A2)-1);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0))"/"&RIGHT(A2,LEN(A2)-SEARCH("-",A2)))*1


    on the other hand if your format is 01-DEC-2011 

    =DATE(RIGHT(A2,LEN(A2)-FIND("-",A2,FIND("-",A2)+1));(INDEX({1;2;3;4;5;6;7;8;9;10;11;12};MATCH(MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)))*1;LEFT(A2,FIND("-",A2)-1))

    then format your cells in the desired format

    A+
    Mike-31

    I am responsible for what I say, not for what you understand...
    0
    1. Gemstar
       
      Hello,

      Thank you for your quick response.
      It works with your format which is FEB-1-2011; however, it doesn't work with the format "01-FEB-2011." Therefore, we now need to reverse the days and months.

      Thanks again


      Remy
      0
    2. Gemstar
       
      I created a macro with the recorder that uses control+H
      it works but it's not optimal.

      Thank you for your help

      Remy
      0