Contact: 514-605-7112 / info@lecfomasque.com

Excel: Combiner des données de jours et d’heures et calculer des écarts

Publié le : 3 mars 2016

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.

Traiter dates et temps dans Excel

 

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.

Traiter dates et temps dans Excel

 

2. Utiliser la fonction Text (Texte) pour recomposer une portion de temps dans un format reconnu par la version d’Excel de mon client.

Traiter dates et temps dans Excel

 

3.Concatener les deux plages de données (dates et temps), en prenant soin d’insérer un espace entre les deux.

Traiter dates et temps dans Excel

 

4.Répéter la même procédure pour la deuxième plage de données.

Traiter dates et temps dans Excel

 

5. Utiliser les fonctions If (Si), Or (Ou), And (Et) et Value (Valeur) pour déterminer dans quel sens doit se calculer l’écart.

Traiter dates et temps dans Excel

 

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.

Traiter dates et temps dans Excel

 

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.
Power Query - Dates et temps

 

2.Utiliser la fonctionnalité From Table (À partir du tableau) pour importer le tableau dans Power Query.

Power Query - From Table

 

Voici le résultat de l’importation du tableau de données dans Power Query.

Power Query - Traiter des dates et du temps

 

3.Changer les types de données (Date pour la colonne Date 1 et Time pour la colonne Time 1).

Power Query Dates et temps

 

4. Utiliser la fonction Merge Columns (Fusionner des colonnes) pour fusionner Date 1 et Time 1.

Power Query Merge

 

5.Changer le type de données pour un type Date/Time.

Power Query temps reconstitué

 

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.

Date 2 erreur

 

6.Séparer la colonne Date 2 par délimiteur (-).

Power Query - Split column

 

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.

Power Query Merge Columns

 

Voici ce que nous obtenons.

Temps reconstitué avec Power Query

 

8. Fusionner les deux colonnes pour reconstituer le temps 2 et accorder le type Date/Time au résultat.

Power Query écart de temps

 

9.Ajouter une colonne pour calculer l’écart entre les deux plages de temps.

Power Query Différence de temps

 

10.Accorder le type de données Duration au résultat.

Power Query Duration

 

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


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils 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.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Jorge ,

    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.

    Laisser un commentaire