Fonctions de dates et de texte du tableur : échéances, ancienneté et nettoyage de données

02Les fonctions de texte

2.1 Mesurer et extraire

TROUVE fonctionne comme CHERCHE mais distingue majuscules et minuscules et n'accepte pas les jokers.

Chaque caractère compte, espaces compris. Le résultat de GAUCHE, DROITE ou STXT est toujours du texte, même s'il ressemble à un nombre.

2.2 Nettoyer et mettre en forme

2.3 Assembler : la concaténation

L'opérateur & colle des textes, des nombres ou des références : =A2&" "&B2 donne le contenu de A2, un espace, le contenu de B2. Les textes littéraux s'écrivent entre guillemets.

Piège : concaténer une date ou un nombre formaté donne son numéro de série ou sa valeur brute, pas l'affichage. ="Facture du "&B2 affiche un nombre à cinq chiffres à la place de la date.

2.4 Convertir : TEXTE et CNUM

Les codes de format de TEXTE dépendent de la langue du tableur (jj, mm, aaaa en français) ; ils sont à reprendre tels que l'énoncé les fournit. Les nombres importés comme du texte se convertissent avec CNUM, en tenant compte du séparateur décimal de la configuration (la virgule en français).

Exemples corrigés

Cas 1 : échéancier clients avec report au premier jour ouvré

La feuille Echeancier contient quatre factures. La cellule nommée Date_ref contient la date d'arrêté du suivi : 01/10/2026. L'énoncé précise : le délai s'ajoute à la date de facture, puis, si la condition comporte « fin de mois », on se place en fin de mois ; une échéance qui tombe un samedi ou un dimanche est reportée au lundi.

Formules de la ligne 5, à recopier :

Résultats attendus (recalculés hors tableur) :

Détail pour F-102 : 12/03/2026 + 45 jours = 26/04/2026 ; FIN.MOIS(26/04/2026;0) = 30/04/2026 ; JOURSEM(30/04/2026;2) = 4 (jeudi), donc pas de report ; retard : 12/05/2026 - 30/04/2026 = 12 jours. Pour F-101 : 12/03/2026 + 30 = 11/04/2026, JOURSEM(...;2) = 6 (samedi) donc +2 = 13/04/2026.

Pour F-104 : l'échéance (04/11/2026) est postérieure à Date_ref (01/10/2026) : MAX(0;01/10/2026-04/11/2026) donne 0 et le statut est « À échoir ».

À retenir

Conclusion à destination du responsable du recouvrement : sur les trois factures déjà réglées, deux l'ont été en retard (7 et 12 jours), soit un retard moyen de 6,3 jours sur les trois. La facture F-104 n'est pas échue. Le modèle étant alimenté par la date de référence, il se met à jour dès qu'on change cette date ; en cas de remplacement de Date_ref par AUJOURDHUI(), le suivi devient dynamique mais les états imprimés ne sont plus reproductibles.

Cas 2 : nettoyer une liste de clients importée

Trois noms arrivent d'un import avec des espaces superflus et des majuscules disparates :

Formules de la ligne 2, à recopier :

Résultats attendus :

Détail pour la ligne 2 : SUPPRESPACE donne DUPONT jean ; NOMPROPRE donne Dupont Jean (11 caractères). CHERCHE(" ";B2) vaut 7. Nom : GAUCHE(B2;7-1) = Dupont. Prénom : DROITE(B2;11-7) = les 4 derniers caractères = Jean. Courriel : GAUCHE("Jean";1) = J, puis J.Dupont@exemple.test, que MINUSCULE ramène à j.dupont@exemple.test.

Sans SUPPRESPACE, les espaces de tête et le double espace décalent CHERCHE et font renvoyer des morceaux faux : l'ordre d'application des fonctions compte.

À retenir

Conclusion à destination du responsable de la base clients : la colonne B est la seule à utiliser pour la suite ; les colonnes brutes sont conservées pour l'audit. Les formules étant recopiables, le nettoyage se refait en quelques secondes à chaque nouvel import. Les adresses générées sont une proposition : elles ne remplacent pas une vérification auprès des intéressés.

Cas 3 : découper une référence de facture et contrôler sa cohérence

Les références de factures se composent des lettres FA, de l'année sur quatre chiffres, d'un tiret et d'un numéro d'ordre sur cinq chiffres : FA2026-00347. La date de la facture est en B2.

Vérification : STXT("FA2026-00347";3;4) renvoie les caractères 3 à 6, soit 2026 ; DROITE("FA2026-00347";5) renvoie 00347, que CNUM convertit en 347 (les zéros de tête disparaissent). Dans la ligne 3, l'année de la référence (2025) diffère de l'année de la date (2026) : la facture est signalée.

Sans TEXTE, la formule ="Facture "&A2&" du "&B2 afficherait un nombre à cinq chiffres à la place de la date.

À retenir

Conclusion à destination de la comptabilité clients : une facture sur deux est incohérente. Il faut vérifier si la date ou la référence est fausse avant tout règlement. Le contrôle est automatique et utilise des fonctions de base : pas besoin de saisie manuelle pour le détecter.

Vocabulaire essentiel

Points clés à retenir

1. Une date est un nombre : on peut l'additionner à des jours et soustraire deux dates pour obtenir un écart en jours.
2. FIN.MOIS(date;0) donne le dernier jour du mois ; MOIS.DECALER(date;n) conserve le jour (ou prend le dernier jour du mois si besoin).
3. JOURSEM(date;2) numérote de 1 (lundi) à 7 (dimanche) ; 6 et 7 désignent le week-end.
4. « 45 jours fin de mois » et « fin de mois puis 45 jours » donnent des dates différentes : suivre l'ordre de l'énoncé.
5. L'ancienneté en années complètes retire 1 quand l'anniversaire de l'année en cours n'est pas encore passé.
6. AUJOURDHUI() rend le modèle non reproductible : pour un arrêté, on préfère une date de référence en paramètre.
7. SUPPRESPACE nettoie, NOMPROPRE met en forme, CHERCHE localise, GAUCHE, DROITE et STXT extraient.
8. Les extractions donnent du texte : CNUM le convertit en nombre ; TEXTE convertit une date ou un nombre en texte mis en forme.

Pièges fréquents

1. Saisir une date sous une forme non reconnue (12.03.2026) : elle reste un texte et ne se calcule pas.
2. Inverser l'ordre des opérations dans « fin de mois » : le résultat diffère de plusieurs jours.
3. Oublier MAX(0;...) : un retard négatif apparaît quand le règlement précède l'échéance.
4. Concaténer une date sans TEXTE : on obtient son numéro de série.
5. Comparer un nombre et un texte : "2026" n'est pas égal à 2026 ; il faut CNUM.
6. Compter les espaces dans NBCAR, CHERCHE ou GAUCHE : un espace en tête décale tout.
7. Utiliser AUJOURDHUI() dans un état destiné à être archivé : il ne sera plus reproductible.
8. Confondre CHERCHE et TROUVE : CHERCHE ignore la casse et accepte les jokers, TROUVE les distingue.

Q&R pour le tuteur IA

Q : Comment calculer l'échéance « 45 jours fin de mois » d'une facture datée en B5 ? R : =FIN.MOIS(B5+45;0). On ajoute les 45 jours, puis on se place à la fin du mois obtenu. Pour une facture du 12/03/2026, on obtient 30/04/2026.

Q : Comment obtenir un nombre de jours de retard sans valeur négative ? R : =MAX(0;date_de_règlement-échéance). Si le règlement est antérieur à l'échéance, le résultat est 0.

Q : Comment calculer l'ancienneté en années complètes d'un salarié entré en A2, à la date de référence en B2 ? R : =ANNEE(B2)-ANNEE(A2)-SI(DATE(ANNEE(B2);MOIS(A2);JOUR(A2))>B2;1;0). Pour une entrée le 02/09/2019 et une référence au 01/10/2026, on obtient 7 ; pour une entrée le 15/12/2021, on obtient 4.

Q : Quelle fonction supprime les espaces superflus d'un nom importé ? R : SUPPRESPACE. Elle retire les espaces au début et à la fin et ramène les espaces multiples à un seul. On l'enchaîne ensuite avec NOMPROPRE pour la casse.

Q : Pourquoi ="Du "&B2 affiche-t-il un nombre et non la date ? R : Parce que & colle la valeur stockée, c'est-à-dire le numéro de série. Il faut écrire ="Du "&TEXTE(B2;"jj/mm/aaaa").

Q : Quelle différence entre FIN.MOIS et MOIS.DECALER ? R : FIN.MOIS(date;n) renvoie toujours le dernier jour du mois décalé de n mois. MOIS.DECALER(date;n) conserve le jour du mois (par exemple le 12), sauf s'il n'existe pas dans le mois d'arrivée, auquel cas il renvoie le dernier jour.