Excel: Un truc qui vous permettra de gagner bien du “temps”!

Publié le 17 avril 2012
par Sophie Marchand M.Sc.

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 1er 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 arriver à nos fins!

 


CFO-Masque_Formations-en-ligne_FBLa 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é.

11 réflexions sur “Excel: Un truc qui vous permettra de gagner bien du “temps”!”

  1. Bonjour,
    Je voudrais faire un calcul de jours ouvrables entre deux dates sur excel. Les dates à ne pas prendre en compte sont les dimanches et jours fériés.
    Les jours ouvrables doivent prendre en considération le samedi.
    Merci pour votre aide,
    Cordialement,
    Jessica

    1. Bonjour Jessica,

      Merci pour votre question.

      Pour effectuer ce type de calcul, vous devez utiliser la fonction Networkdays.intl(start_date,end_date,[weekend],[holidays]).

      En français, networkdays.intl est NB.JOURS.OUVRES.INTL.

      Avec cette fonction, vous pouvez utiliser le 3ième paramètre, [weekend] pour spécifier quels sont les jours de la semaine que vous considérez comme “week-end”, dans votre cas, le samedi seulement.

      Ensuite, vous pouvez utiliser le 4ième paramètre, [holidays] pour spécifier vos jours fériés.

      Et voilà.

      Sophie

  2. bonjour,

    j’ai la meme problématique pour plannifier une production uniquement sur les jours ouvrables
    pour ma part va version excel ne trouve pas la formule NB.JOURS.OUVRES.INTL.

    comment puis je faire?
    merci

  3. Bonsoir,
    Désolée de vous solliciter mais je souhaiterais obtenir votre aide pour une formule de calcul que je n’arrive pas à trouver, si vous le voulez bien svp:
    Exemple: dans la cellule a1 j’indique la date de transmission du devis au technicien. Dans la cellule b1 la date retour du technicien. Je souhaiterais que, si la cellule b1 est vide (donc pas de retour du technicien) au bout de 4 jours ouvrés alors la cellule Evra apparaître en rouge afin de l’alerter. Le calcul doit se faire en jours ouvrés
    En espérant que vous pourrez m’apporter votre précieuse aide,
    Bien cordialement

  4. Bonjour
    J’ ai une petite question j’espere que vous pourrez m’aider ?
    Mon patron me demande de convertir les extraits de compte en format Excel. Je n’ai aucune idée comment faire ca ni par ou commencer…
    Merci d’avance

  5. Ping : Temp2 | Pearltrees

  6. bonjour,

    j’aimerais calculer le total des flux eclatés par canal de paiement et jour.

    par exemple j’ai trois canaux de paiement A,B et C (le paiement à travers ces canaux se fait de façon éclatée) pour moi je dois calculer le total des paiements éclatés par canal pour une journée X.

    comment je peux faire svp

    1. Bonjour,

      Je ne comprends pas bien votre problématique. Je vous suggère donc de partager avec nous un exemple de données de base de votre problématique et une idée de ce que vous recherchez comme résultat, dans un fichier Excel, via notre forum: https://www.lecfomasque.com/forum/. En ayant accès aux données et en sachant le résultat que vous avez en tête, nous serons davantage en mesure de vous aider.

      Merci.

      Sophie

  7. Bonjour
    Je souhaiterai savoir comment calculer le probleme suivant
    1. Date d’entree de la commande: saisie manuelle
    2. Commande sera effectuee: Calcul automatique de la date = 22 working days apres la date de la saisie manuelle (ma semaine commencant le Dimanche et se terminant le Jeudi)
    3. Date de livraison: si le resultat du point 2 tombe un jour qui n’est pas un Lundi alors la date sera pousse au Lundi suivant

    Example:
    1. Mercredi 16 Decembre 2020
    2. Automatiquement me donnera la date du: Dimanche 17 Janvier 2021
    3. Automatiquement me donnera la date du: Lundi 18 Janvier 2021
    J’ai considere que le 25 Decembre 2020 et le 1 Janvier 2021 etait des jours feries mais etant un Vendredi donc durant le week end il ne sont pas consideres comme des “working days” ou des “jours feries”

    Merci d’avance pour votre aide.

    Cordialement

  8. Bonjour a nouveau,

    Nouveau cas de figure:
    cette fois je prends le probleme a lenvers:
    1. Saisie manuelle du debut de la campagne
    2. Automatiquement excel m’indiquera le 1er Lundi qui suit la date saisie manuellement dans le point 1
    3. Automatiquement excel m’indiquere a quelle date je dois recuperer le brief de la campagne en considerant 22 Working days precedent date indiquee en “2”

    Mes semaines commencant un DImanche et se finissant un Jeudi – Vendredi et samedi etant des jours de week end

    Example:
    1. Saisie manuelle: Jeudi 4 Mars 2021
    2. Automatiquement excel me donnera: Lundi 8 Mars 2021
    3. Automatiquement excel me donnera: Jeudi 4 Fevrier 2021

    Dans l’attente de vous lire,

    Cordialement,

Laisser un commentaire

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

Retour en haut