Date English to French
Gemstar
-
Mike-31 Posted messages 18405 Registration date Status Contributeur Last intervention -
Mike-31 Posted messages 18405 Registration date Status Contributeur 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
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 réponses
Hello,
try it like this for a date in English format Feb-1-2017 entered in A2
or with SEARCH
on the other hand if your format is 01-DEC-2011
then format your cells in the desired format
A+
Mike-31
I am responsible for what I say, not for what you understand...
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...
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
it works but it's not optimal.
Thank you for your help
Remy