Récemment, un client m’a approchée car il avait du mal à calculer des écarts de temps dans Excel, entre deux plages de données, dont les dates et les heures étaient fournies dans des colonnes différentes et dont le format de date différait également. Cet article vise à présenter deux approches pour calculer les écarts de temps à partir de transformations appliquées sur ces données. La première approche utilise des fonctions d’Excel et la deuxième approche utilise Power Query.
Problématique d’écarts de temps dans Excel
Les données à analyser sont présentées dans l’image ci-dessous. On note que la portion date et la portion temps sont fournies dans des plages de données séparées, pour les deux échantillons de temps. De plus, le format de Date 2 est différent du format de Date 1. Et finalement, parfois la date 2 est supérieure à la date 1 et parfois, c’est l’inverse.
Résoudre la problématique avec Excel (sans Power Query)
Mon client utilise une version d’Excel qui ne permet pas l’usage de Power Query. Ce faisant, je lui ai proposé une approche, directement dans Excel.
Calcul d’un écart de temps dans Excel
1.Utiliser la fonction Text (Texte) pour recomposer une date dans un format reconnu par la version d’Excel de mon client.
2. Utiliser la fonction Text (Texte) pour recomposer une portion de temps dans un format reconnu par la version d’Excel de mon client.
3.Concatener les deux plages de données (dates et temps), en prenant soin d’insérer un espace entre les deux.
4.Répéter la même procédure pour la deuxième plage de données.
5. Utiliser les fonctions If (Si), Or (Ou), And (Et) et Value (Valeur) pour déterminer dans quel sens doit se calculer l’écart.
6.Utiliser une fonction If (Si) pour calculer l’écart dans le bon sens, selon le résultat obtenu dans la colonne M. S’assurer également que le format des données de la colonne N soit dd hh:mm.
Résoudre la problématique avec Power Query
Si mon client avait eu accès à Power Query, il aurait pu procéder comme suit.
Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel. |
|---|
Calcul d’un écart de temps dans Excel avec Power Query
2.Utiliser la fonctionnalité From Table (À partir du tableau) pour importer le tableau dans Power Query.
Voici le résultat de l’importation du tableau de données dans Power Query.
3.Changer les types de données (Date pour la colonne Date 1 et Time pour la colonne Time 1).
4. Utiliser la fonction Merge Columns (Fusionner des colonnes) pour fusionner Date 1 et Time 1.
5.Changer le type de données pour un type Date/Time.
Malheureusement, nous ne pouvons pas procéder de la même façon avec la deuxième date. Autrement, nous obtenons des erreurs. Il faut donc procéder autrement.
6.Séparer la colonne Date 2 par délimiteur (-).
7.Sélectionner ensuite la colonne défusionnée représentant l’année, celle représentant le mois et celle représentant le jour et utiliser l’option Merge Columns (Fusionner des columns) pour reconstituer une date. Appliquer ensuite le type Date au résultat.
Voici ce que nous obtenons.
8. Fusionner les deux colonnes pour reconstituer le temps 2 et accorder le type Date/Time au résultat.
9.Ajouter une colonne pour calculer l’écart entre les deux plages de temps.
10.Accorder le type de données Duration au résultat.
Quelle méthode préférez-vous?
Vous pouvez utiliser la zone de commentaires à la suite de cet article, pour répondre à cette question.
Formation complémentaire
Développez vos compétences en analyse de données et exploitez les fonctions d’Excel qui permettent de manipuler, gérer et transformer des données qui proviennent de diverses sources et différents formats, sans faire intervenir Power Query ou le VBA, en suivant la formation Excel – Traitement, manipulation et analyse de données.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Les deux méthodes je les trouve intéressantes. Cependant, dans la première j’aurais calculé le sens après avoir fait la concaténation. On aurait pas eu besoin des AND et des OR, la formule serait plus courte.
Bonjour
Même si cet article est publié depuis longtemps, il doit encore servir à beaucoup de gens.
Je peux proposer deux solutions assez simples pour remplacer les étapes 5 et 6 de la solution sans PowerQuery.
Solution 1: On fait faire à Excel simplement la soustraction entre la plus grande valeur des deux et la plus petite valeur des deux. Excel se chargeant de trouver la bonne dans chacun des cas.
=MAX(E2;K2)-MIN(E2;K2)
Solution 2 : On prends la valeur absolue (c’est à dire le nombre sans le signe “moins” éventuel) de la soustraction d’une valeur mois l’autre. Quand le résultat est positif, cela ne change rien. Quand le résultat est négatif, Excel le “transforme” en positif.
=ABS(K2-E2)
Dans les deux cas, il faut bien s’assurer comme l’a dit Sophie “que le format des données de la colonne soit dd hh:mm” (ou jj hh:mm pour Excel en français).
Pierre