Excel: un truc qui vous permettra de sauver bien du “temps”!

Encore une fois, je vous propose aujourd’hui un petit truc dans Excel, qui vous permettra de modéliser un cas plutôt complexe via la fonction de temps “serie.jour.ouvre” (“workday” en anglais). Pour davantage de fonctions concernant le calcul du temps dans Excel, je vous propose de relire l’article Excel: Calculer du temps.

Au-delà de la traduction francophone de la fonction “workday”, qui laisse à désirer (!), la fonction “serie.jour.ouvre” peut vous aider à résoudre des calculs de temps complexes.

Par exemple, dernièrement, un client me demandait de lui bâtir un modèle dans lequel il serait en mesure de calculer la date de livraison de ses mandats, en considérant le taux d’occupation de ses employés, le nombre de jours fériés et le fait que chaque employé travaille un maximum de 7 heures par jour.

RAPPEL DE LA FONCTION “SERIE.JOUR.OUVRE“

Avant d’aller plus loin, revenons à la base de la fonction “serie.jour.ouvre”:

Cette fonction permet donc de déterminer une date, X jours après une date de départ et considérant les jours fériés et les week-ends.

Par exemple, dans le cas ci-dessous, Excel nous apprend qu’entre le 17 avril 2012 et le 5 mai 2012 (considérant les jours fériés d’une entreprise X qui sont le 23 avril 2012, le 24 avril 2012 et le 25 avril 2012 et les week-ends), il y a 7 jours ouvrables.

En effet, quand on jette un coup d’oeil au calendrier, on s’aperçoit que le calcul est juste!

PROBLÈME PLUS COMPLEXE

Revenons maintenant à mon client, qui souhaitait connaître la date de livraison de ses projets mais en accordant un maximum de 7h de travail par employé par jour.

Voici comment j’ai traité le problème:

CLIQUEZ SUR L’IMAGE POUR L’AGRANDIR

Dans la colonne G, j’ai d’abord transféré les heures de travail planifiées en fraction de journée (je les ai donc divisées par 7). En H, j’ai d’abord inscrit la date de début du calcul sur la première ligne (input) mais pour les autres lignes, j’ai simplement lié les cellules à la colonne O (date de fin “finale”). En I, j’ai simplement additionné les dates de début avec le nombre d’heures planifiées. Notez que j’ai utilisé (en H et en I)  le format “aaaa-mm-jj hh:mm” de façon à obtenir des fragments d’heures. En J, j’ai calculé le nombre de jours entiers entre la date de début et la date de fin. Pour cela j’ai utilisé la fonction “Edate” (ou “mois.decaler” en français). Ensuite, j’ai utilisé la fameuse fonction “serie.jour.ouvre” pour obtenir la date de livraison en fonction de la date de début et des jours fériés et week-ends. Notez que cette formule calcule le nombre de jours ouvrables “entiers”. C’est pourquoi il m’a fallut plusieurs étapes intermédiaires avant d’arriver à mon calcul final! Dans les colonnes L et M, j’ai calculé le nombre d’heures et le nombre de minutes à ajouter à cette date pour considérer les fractions de jours. C’est ce qui m’a permis de reconstituer une date réelle en N. Comme j’ai supposé dans cette façon de calculer mes dates de livraison qu’une journée n’avait que 7 heures (ce qui n’est pas le cas d’une journée normale!), j’ai ensuite utilisé la colonne O pour remettre les dates en format “aaaa-mm-jj” pour ne pas mélanger mon client avec des indications d’heures et de minutes incongrues! L’important était de déterminer les bonnes dates de livraison. Les calculs intermédiaires peuvent bien sûr être cachés.

Alors voilà! Excel possède de nombreuses fonctions et fonctionnalités intéressantes, mais parfois, il faut utiliser des détours pour arrivers à nos fins!