Excel: Conversion des dates en Trimestres
Excel a des fonctions intégrées pour renvoyer l’année, le numéro du mois, le nom du mois, le numéro de la semaine, le jour de la semaine ou le jour à partir de n’importe quelle cellule contenant une date. Mais il n’y a pas de moyen intégré de renvoyer un numéro de quart. Les trimestres sont plus difficiles parce que la définition d’un trimestre varie en fonction de la date de fin de l’exercice.
POUR LES ANNÉES CIVILES
Si votre exercice se termine le 31 décembre, vous pouvez utiliser une formule courte. (La figure 1 montre les fonctions Excel que vous pouvez utiliser pour convertir une date en années, mois, semaines ou jours de la semaine.) Tout d’abord, utilisez la fonction =MONTH() pour convertir la date en un numéro de mois. Divisez ce résultat par 3. Les dates en janvier reviendront à 0,33, 0,67 février, 1,00 mars, 1,33 avril, etc. En utilisant la fonction =ROUNDUP(), vous aurez 1 pour Janvier, Février et Mars; 2 pour Avril à juin; et ainsi de suite.
Pour afficher le trimestre comme « Q1 » ou « Qtr 1″, concaténez le texte approprié avant le résultat. Dans la figure 2, la cellule A7 utilise une formule de = »Q » & ROUNDUP(MONTH(A2)/3,0)& »-« & ANNÉE (A2) à retourner « T1-2017. »
POUR LES AUTRES EXERCICES
Les exercices se terminant le 31 janvier sont courants dans les entreprises de détail. Les entrepreneurs gouvernementaux pourraient utiliser un exercice financier se terminant le 30 septembre. Pour calculer un trimestre fiscal, convertissez d’abord la date en un numéro de mois en utilisant =MONTH(). Ce résultat est limité à 12 entiers de 1 à 12.
Vous pouvez utiliser une formule VLOOKUP pour convertir le numéro de mois en numéro de trimestre. Mais la formule = VLOOKUP(MONTH(A2), {1,4;2,1;5,2;8,3;11,4},2) est difficile à retenir et à taper. La constante de tableau à l’intérieur des accolades contient une table de recherche de 5 lignes par 2 colonnes.
Au lieu de cela, la fonction =CHOOSE() vous permettrait de spécifier 12 numéros de trimestre correspondant aux mois 1 à 12. Si votre exercice se termine en janvier, vous utiliserez =CHOISIR (MOIS(A2),4,1,1,1,2,2,2,3,3,3,4,4). Pour un exercice se terminant en mars, utilisez = CHOISISSEZ (MOIS (A2),4,4,4,1,1,1,2,2,2,3,3,3).
Si vous préférez afficher le quart comme Q1 au lieu de 1, vous pouvez utiliser l’esperluette pour concaténer un « Q » avant la fonction CHOISIR. Les résultats sont présentés dans les cellules A13 et A20 de la figure 2, avec les formules indiquées dans D13 et D20.
ANNEXANT UN EXERCICE AU TRIMESTRE
Dans certaines entreprises, l’exercice est déterminé par l’année civile au cours de laquelle se produit le 12e mois de l’année. Par exemple, un exercice allant de février 2017 à janvier 2018 comporterait des trimestres appelés T1-2018 à T4-2018. Dans d’autres entreprises, l’exercice financier pourrait être appelé quelque chose comme « 17-18. »
Dans le cas où l’exercice est l’année du mois de fin, utilisez une fonction IF() pour tester si le numéro du mois est inférieur au mois de début. Par exemple, avec un exercice se terminant en janvier, l’exercice sera = ANNÉE (A2) si la date est antérieure à février et = ANNÉE (A2) +1 en février et plus tard. Ainsi, pour inclure l’exercice avec le trimestre, vous pouvez utiliser = IF(MOIS (A2) < 2,0,1) + ANNÉE (A2).
Pour une année se terminant en mars, vous changeriez le test <2 pour être < 4: = IF(MONTH(A2) < 4,0,1) + YEAR(A2). Pour une année se terminant en septembre, vous testeriez pour < 10: = IF(MOIS(A2) < 10,0,1) + ANNÉE (A2).
La cellule A21 de la figure 2 montre comment concaténer le trimestre et l’année d’exercice en utilisant cette formule longue: = »Q »&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)& »-« &IF(MONTH(A17)<2,0,1)+YEAR(A17). Cela rendrait le trimestre dans un format de « Q4-2017. »
Si, à la place, vous en avez besoin pour dire 17-18, la formule longue devient encore plus longue. Juste pour obtenir la partie année de la formule, vous utiliseriez: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)& »-« &TEXT(A2, »YY »),TEXT(A2, »YY »)& »-« &MOD(YEAR(A2)+1,100)).
RETOUR D’UNE PÉRIODE AU LIEU D’UN MOIS
J’ai récemment organisé un séminaire pour IMA® à Nashville. Parce que le gouvernement de l’État du Tennessee a un exercice se terminant en juin, ils ont préféré déclarer en utilisant un numéro de période au lieu d’un mois. Juillet est la période 1, Décembre est la période 6, janvier est la période 7, etc. Dans ce cas, de nombreux collègues ont utilisé une table VLOOKUP située à droite des données. Mais ils pourraient aussi utiliser =CHOOSE(MONTH(A2),7,8,9,10,11,12,1,2,3,4,5,6) pour obtenir le même résultat.
LES DATES NE PEUVENT PAS ÊTRE DU TEXTE
La mise en garde pour toutes ces formules est que vous stockez vos dates en tant que dates réelles et non en tant que texte. Un moyen rapide de vérifier: Appuyez sur Ctrl et la touche d’accent grave (`) pour basculer en mode Afficher les formules. Les dates réelles seront converties en un numéro de série tel que 42783. Si vous voyez toujours une date de texte, les formules ne fonctionneront pas. Appuyez à nouveau sur Ctrl+ ` pour quitter Afficher les formules.
J’ai récemment croisé une feuille de calcul dans laquelle le gestionnaire souhaitait que les dates s’affichent comme 1er février 2017, 2 février 2017, 3 février 2017, 4 février 2017. Les suffixes après la date (« st », « nd », « rd » et « th ») convertissent un nombre cardinal en un nombre ordinal. Alors que la poignée de remplissage Excel vous permettra de créer facilement une colonne de nombres cardinaux, les formules de cet article deviennent beaucoup plus complexes. Une formule de 41 caractères est devenue 2 438 caractères afin de gérer les dates de texte (voir Figure 3).
