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

Publié le 03 mars 2016
par Sophie Marchand M.Sc., CPA, CGA, MVP
Traiter dates et temps dans Excel

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

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.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

2 réflexions sur “Excel: Combiner des données de jours et d’heures et calculer des écarts”

  1. 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.

  2. 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

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top