Récemment, un client m’a demandé s’il était possible de calculer le nombre de jours ouvrables entre deux dates, en tenant compte des jours fériés, via Power Query. La réponse est oui. Cet article vise à démontrer deux façons d’arriver au résulat. La première méthode consiste à rédiger un script à l’aide du code M, alors que la deuxième n’en nécessite pas.
Calculer le nombre de jours ouvrables : Script en code M
Pour effectuer ma démonstration, je vais utiliser les tables Dates (qui contient la date de début et la date de fin de la période sous analyse) et Holidays (qui contient les jours fériés).
J’ai récupéré le script ci-dessous sur un forum (à quelques détails près). Celui-ci permet de calculer le nombre de jours ouvrables entre deux dates en tenant compte des jours fériés. Ce script est expliqué plus loin dans cet article.
Le script ci-dessus retourne le résultat suivant:
Examinons maintenant ce script en détail.
Débutons par la ligne de code ci-dessous. Cette commande demande à Excel de vérifier s’il y a une table dans le fichier Excel, qui se nomme Holidays, sinon, il demande de créer une table, qui portera le nom Holidays.
Holidays_Import = try Excel.CurrentWorkbook(){[Name= »Holidays »]}[Content] otherwise Table.FromColumns({{}}, {« Holidays »}),
Cette commande crée une liste à partir de la table Holidays et transforme les dates en nombre, puis garde le résultat en mémoire.
Holidays = List.Buffer(Table.TransformColumnTypes(Holidays_Import,{{« Holidays », type number}})[Holidays]),
Cette commande permet de récupérer les données de la table Dates du présent fichier.
Source = Excel.CurrentWorkbook(){[Name= »Dates »]}[Content],
Cette commande change le type de données de la table de dates pour des dates.
ChType = Table.TransformColumnTypes(Source,{{« StartDate », type date}, {« EndDate », type date}}),
Cette commande ajoute une colonne calculée à la table de dates, en utilisant la fonction FxWorkingDays, avec les paramètres StartDate/EndDate (colonnes de la table Dates) et Holidays (table Holidays)). La fonction est définie plus haut dans le script et sera expliquée ci-après.
NumberOfWorkingDays = Table.AddColumn(ChType, « Workdays », each fxWorkingDays([StartDate], [EndDate], Holidays))
Les sections suivantes définissent la fonction fxWorkingDays.
On voit que cette fonction utilise les mêmes paramètres que la fonction NETWORKDAYS d’Excel.
fxWorkingDays = (start as date, end as date, optional HDays as list) as number =>
S’il n’y a pas de liste Holidays, on affiche null, sinon on prend la liste Holidays (une liste de dates transformées en nombre).
LstOfHolidays = if HDays = null then {} else HDays,
Number.From convertit les dates de la table de dates en nombre.
LstOfDays = {Number.From(start)..Number.From(end)},
On fait la différence entre la liste de jours comprenant tous les jours (LstOfDays) et la liste de jours qui sont des fériés, on obtient donc une liste de jours, en nombre, sans les jours fériés.
LstDiff = List.Difference(LstOfDays, LstOfHolidays),
Pour chacune des dates de la liste résultante (en nombre), on fait un modulo 7.
LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
Pour revoir comment utiliser la fonctoin MOD, vous pouvez relire l’article Connaissez-vous la fonction MOD?
Ci-dessous, on peut voir que la fonction MOD rapporte 0 pour les samedi et 1 pour les dimanches.
On ne retient que les valeurs plus grandes que 1 (donc on retire les 0 et les 1, qui sont les samedis et dimanches).
LstSel = List.Select(LstMod, each _>1),
On compte le nombre d’éléments de la liste résultante.
Result = List.Count(LstSel)
Calculer le nombre de jours ouvrables : Sans script en code M
Si vous n’êtes pas familier avec le code M, vous pouvez néanmoins vous en sortir. Voici une procédure qui n’implique pas de connaissance majeure du code M.
Cet exemple utilise les 3 tables ci-dessous. La table de dates est une table de dates en continu, la table Fériés contient les jours fériés et la table Calendrier comprend la date de début et de fin de l’intervalle de temps.
Dans cet exemple précis, nous avons un peu modifié le code M afin de pouvoir utiliser les données de notre table Calendrier mais cette étape n’est pas nécessaire. On pourrait seulement fournir une table de dates avec la date de début et la date de fin et toutes les dates intermédiaires, ce qui nous éviterait cette modification au code M. Cela dit, dans l’exemple ci-dessous, les lignes Calendrier, Date_Debut et Date_Fin sont essentielles seulement si on utilise une table Calendrier. Et cette technique est expliquée dans l’article suivant: Power Query : Faire pointer vos requêtes vers une nouvelle source de données.
D’abord, j’ai importé ma table de dates et je l’ai modifiée afin d’ajouter le jour de semaine de chacune des dates (DayOfWeek), ainsi que les samedis et les dimanches.
Plus précisément, pour ajouter la colonne avec les jours de semaine, j’ai utilisé la fonctionnalité Date/Day/Day of Week.
Pour déterminer les samedis et les dimanches, j’ai simplement utilisé une fonction IF (i.e. si la colonne DayOfWeek est égale à 6, afficher 1, pour les samedi et si la colonne DayOfWeek est égale à 0, afficher 1, pour les dimanches). Notez que dans Power Query, la numérotation commence toujours à 0, donc, 1 est égal à 0, 2 à 1, etc.
À l’étape suivante, j’ai fusionné la table de dates avec la table de jours fériés. Pour savoir comment fusionner deux tables dans Power Query, vous pouvez relire l’article : Power Query: Importer, transformer et fusionner des tables de données dans Excel.
J’ai ensuite remplacé les null par des 0, dans la colonne Férié.
L’étape Lignes filtrées a été effectuée en fonction du code M généré à cause de la table Calendrier. Cette étape n’est pas nécessaire si vous n’utilisez pas de table Calendrier.
J’ai ensuite ajouté une colonne pour déterminer les jours fermés (soit les jours fériés et ceux de fin de semaine).
J’ai ensuite ajouté une colonne pour déterminer les jours ouvrables.
J’ai ensuite supprimé des colonnes et renommé les colonnes restantes afin de ne conserver que l’information pertinente.
Finalement, j’ai utilisé la fonctionnalité Group By pour faire la somme des jours ouvrables.
Conclusion
Quelle méthode préférez-vous?
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 [email protected]
Jeu de briques
Snake
Pacman
Bubble