Dans ce blog, je vais aborder la création de dates.
Il ne sera pas question ici de la création qui peut être paramétrée via la cellule, mais bien du format qu’il faut introduire lorsqu’on utilise la fonction TEXTE Le format utilisé ne fonctionne que si l’utilisateur final utilise la même langue que la langue que vous avez paramétrée.
Analysons d’abord la situation où le problème se pose.
Partons d’un utilisateur anglophone qui affiche une date en formatant une cellule :
« Mon 12 jan 2019 »
L’utilisateur devra introduire le code suivant pour formater la cellule :
« ddd dd mmm yyyy »
Un utilisateur francophone choisira « jjjj jj mmm aaaa ». Un utilisateur néerlandophone optera quant à lui pour « ddd dd mmm jjjj ». Lorsqu’ils échangent leurs fichiers, Excel adaptera automatiquement la traduction des codes utilisés à la langue du destinataire.
Il s’agit là du fonctionnement standard d’Excel.
Ce principe n’est plus valable lorsque la cellule a été créée avec une formule TEXTE. C’est le sujet de ce blog.
Si vous souhaitez créer une date à partir d’une formule et non à partir du format standard, voici comment vous y prendre.
Introduisez une date dans la cellule A1.
Astuce: CTRL; introduit automatiquement la date d’aujourd’hui !
Introduisez la formule suivante dans la cellule A2 (les formules sont toujours en anglais dans ce blog)
= TEXT (A1 ; “ddd dd mmm yyyy”)
Dans ce cas, vous êtes immédiatement confronté au problème qui fait l’objet de ce blog. La formule fonctionne uniquement si votre appareil est paramétré en anglais. Excel ne traduit pas de Y (pour EN) à J (pour NL) ou à A (pour FR). Vous avez d’ailleurs introduit littéralement le code en tant que texte dans la formule.
Si votre ordinateur est paramétré en néerlandais, vous introduisez = TEXT (A1 ; “ddd dd mmm jjjj”) et si votre ordinateur est paramétré en français, vous introduisez = TEXT (A1 ; “jjj jj mmm aaaa”).
Lorsque l’utilisateur anglophone envoie son fichier à un collègue francophone, la date s’affiche comme suit « Tue 01 jan yyyy ». Le paramétrage en français ne capte pas le code « Y » et l’affiche comme si l’objectif était de placer ce texte après la date.
Ce problème peut être résolu en créant un petit tableau de référence. Nous allons tâcher de reconnaître chaque format connu, ou plutôt chaque langue utilisateur connue. Nous lions le format souhaité à la solution requise.
Créez un tableau tel que celui-ci :
Introduisez une date fixe. Dans l’exemple, c’est ce qui a été fait à la cellule D2. Le format de cette cellule n’est pas si important, quoique personnellement je préfère un nombre sans décimales.
Dans ce tableau, nous allons découvrir si l’ordinateur de l’utilisateur est paramétré en anglais, néerlandais ou français.
- Introduisez la formule suivante dans D4 : =TEXT($D$2; »dd-mm-jjjj »)
- Introduisez la formule suivante dans D5 : =TEXT($D$2; »jj-mm-aaaa »)
- Introduisez la formule suivante dans D6 : =TEXT($D$2; »dd-mm-yyyy »)
Pour l’instant, vous voyez la solution suivante :
La formule TEXTE mentionne la date dans le format souhaité, mais le résultat est considéré comme du texte et non comme une date. Dans l’exemple ci-dessus, on peut conclure que la région paramétrée sur l’ordinateur de l’utilisateur est la Belgique ou les Pays-Bas.
Ces formules donnent donc un résultat textuel et le résultat de la formule ne peut pas être évalué au moyen d’une autre formule.
C’est pourquoi nous allons multiplier la date obtenue (ou les 2 autres mauvaises cellules) par la valeur 1. Excel affichera alors la solution obtenue de la formule TEXTE comme valeur numérique. Il est préférable de paramétrer désormais le format de la cellule en tant que « nombre ».
=TEXT($D$2; »dd-mm-jjjj »)*1
Vous remarquez que les 2 cellules qui ne satisfont pas au format de date utilisé ne peuvent pas être converties en chiffr
En testant, il s’est avéré qu’à l’ouverture de ce fichier, le format correct de langue n’était pas immédiatement reconnu parce qu’Excel n’effectue pas les calculs à l’ouverture du fichier. Nous allons donc modifier cela en remplaçant le chiffre 1 par « RANDBETWEEN(1,1) ». À l’ouverture d’un fichier, Excel sera obligé de calculer ces cellules.
Voici à quoi ressemble la formule à présent.
=TEXT(D2; »dd-mm-jj »)*RANDBETWEEN(1;1)
Dans la dernière colonne, nous choisissons le format texte souhaité pour chaque langue.
La cellule E2 en haut du tableau aura désormais une formule.
La formule INDEX analyse la zone bleue à l’aide des différents formats souhaités. Pour déterminer la ligne nécessaire à cet égard, nous utilisons la formule MATCH. Nous contrôlons où se trouve la date introduite « D2 » dans la zone mauve. La formule INDEX sait maintenant à quelle ligne se trouve la solution souhaitée. La formule INDEX réfère par ailleurs à la colonne 1 puisque nous n’avons plus utilisé 1 colonne comme base.
Dans la cellule « A2 », vous pouvez maintenant introduire la formule suivante :
= TEXT( A1 ; E2)
La cellule E2 peut être nommée Pour ma part, j’ai utilisé « nFormat ». La formule se présente comme suit :
= TEXT( A1 ; nFormat)
Pour pouvoir faire un test, vous devez fermer Excel et modifier les paramètres de votre ordinateur en une autre langue ou région standard. Et si vous avez de la chance, un collègue dont la langue maternelle est différente de la vôtre se trouve à proximité.
