Récemment, un client m’a approchée car il avait du mal à calculer des écarts de temps entre deux plages de données, dont les dates et les heures étaient fournies dans des colonnes différentes dans Excel 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
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.
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.
1.Mettre ses données sous forme de tableau (format as table) et donner un nom à ce tableau.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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou info@lecfomasque.com