Combien y’a t’il de jours ouvrés (du lundi au vendredi) entre deux dates ? A cette question vieille comme le monde, on a eu droit à de nombreux algorithmes, parfois exacts mais rarement rapides.
Je vais vous expliquer le mien qui apparaît comme étant très efficient…

Rapide historique
Les premiers algos que j’ai vu, faisaient une boucle for/next sur les dates et utilisaient la fonction standard VBA WeekDay() sur chacune des dates pour incrémenter un compteur si le jour de semaine de la date était différent d’un samedi ou dimanche. Efficace mais lent…
En 2007, pour répondre à un micro challenge lançait par Maxince Hubiche, j’avais trouvé un algo qui m’a servit de base à ma fonction d’aujourd’hui.
En 2009, on trouve un autre algo de Microsoft sur MSDN qui est dérivé d’un code du livre de Ken Getz (VBA Developer’s Handbook). Fonction d’une écriture très académique mais lente…

Ma Fonction WorkDays

Explications
Le code mérite quelques explications car, au premier abord, il n’est pas très éloquent… L’objectif était la rapidité de la fonction, il fallait donc éviter d’utiliser des fonctions VBA trop évoluées !

Il s’agit d’une fonction (retourne une valeur contrairement à un Sub) qui est Public donc accessible partout dans l’application (modules de code, requête, formulaire,…) contrairement à un type ‘Private’.
Cette fonction exige deux arguments en paramètre. Une date de début (StartDate) de période et une date de fin (endDate).
On remarquera que chaque paramètre est précédé du mot-clé ‘ByVal’ pour imposer que les dates soient passées par valeur et non par référence (ByRef par défaut). Un paramètre passé par valeur peut être modifié dans la fonction mais cette modification ne sera pas visible en dehors de celle-ci contrairement à ByRef. En résumé, les modifications d’un paramètre passé par ByRef seront visibles par l’appelant contrairement à ByVal.
J’utilise plus rarement ByRef que ByVal pour trois raisons :

• Paradoxalement, ByVal est généralement plus rapide que ByRef en VBA
• ByVal permet un Cast implicite des paramètres (par exemple, pour un paramètre déclaré Long, un Integer sera casté automatiquement contrairement à ByRef)
• Evite de ce faire piéger avec une donnée modifiée dans la fonction puis utilisait ailleurs…

Le suffixe ‘As Date’ de chaque déclaration de paramètre permet d’imposer le type de variable que l’on passe à la fonction. Eviter d’omettre cette information car le type devient par défaut un Variant (variable non typée) ce qui est source d’erreurs !

Pour finir, la déclaration de la fonction se termine par ‘As Long’ ce qui veut dire qu’elle va retourner une variable de type Numérique Entier Long signé. Ne pas omettre le type de variable retournée pour ne pas tomber dans le piège décrit précédemment.

La date de début de période doit être supérieur ou égale au 30/12/1899 qui correspond à la date 0 pour Access. La fonction risque de retourner un résultat inexact si la ou les dates sont inférieures à 0.

Cette ligne déclare (mot-clé Dim) les variables spécifiques à la fonction. On remarquera le typage de chaque variable pour éviter le type Variant, plus lourd en mémoire et plus lent…

La ligne active de code (avec dateDiff()) affecte à la variable d, le nombre de jours entre le début et la fin de la période considérée.
La ligne d = Int(endDate) – Int(startDate) + 1 a été remplacée par la fonction standard DateDiff() car DateDiff() arrondit la date au jour supérieur si l’heure est supérieure à 23h 59min et 59 secondes + ? d’où parfois des écarts sur le nombre de jours calculé entre les deux méthodes.

Remarque : La partie décimale d’une date est égale à 0 s’il s’agit de minuit et elle est égale à 86399/86400 pour 23h 59min et 59secondes (86400 étant le nombre de secondes qui composent 24h).

On ajoute 1 à la période car on inclut les deux dates extrêmes (Exemple : 9 – 5 + 1 = 5 jours).

Remarque : Si on connaissait la valeur de coupure exacte (23h 59min et 59 secondes + ?) on pourrait s’affranchir totalement de DateDiff() et de WeekDay()…
d serait égal à Int(endDate + x) – Int(startDate + x) + 1
et w serait égal à (7 – (6 + Int(startDate + x)) Mod 7) Mod 7 ‘6 = samedi 30/12/1899
La valeur de coupure n’est pas loin de 770754 / 133186260107…

Version pour le fun et la vitesse seulement…

Cette ligne affecte à la variable r le reste de la division du nombre de jours par 7 (Mod = modulo), c’est à dire la part non entière restante d’une semaine.
Par exemple, Deux semaines pleines donnera 0 (14 Mod 7 = 0 car 2 x 7 = 14) et 15 jours donnera 1 (15 Mod 7 = 1 car ((15 / 7) – Int(15 / 7)) * 7 = (2,142857… – 2) x 7 = 1).
Le reste (r) sera donc toujours compris entre 0 et 6 jours.

La variable wd est affectée du nombre de jours ouvrés de la partie entière (en semaines) de la période + du reste (r).
Par exemple, pour une période de 15 jours :
Int(15 / 7) = au nombre de semaines pleines x le nombre de jours ouvrés par semaine (5 jours) soit 10 jours + le reste calculé (r = 1) soit 11 jours en tout.

L’algorithme ne s’arrête malheureusement pas là car on est sûr que les 10 jours de notre exemple sont bien ouvrés mais on ne sait pas encore si le reste (ici r = 1 jour) correspond ou non à un jour ouvré…
Wd contient donc pour l’instant le nombre de jours potentiellement maximal de jours ouvrés sur la période.

Si le reste est superieur à zéro, il nous faut donc plus d’informations pour savoir si ce reste contient des jours non ouvrés et combien…
Si le reste est égale à 0, on a déjà le résultat dans la variables wd.

Comment déterminer le nombre de jours non ouvrés contenus dans le reste ?
Reprenons notre exemple d’une période de 15 jours avec donc un reste égal à 1 jour.

Si le 1er jour de la période est un lundi, ceci veut dire que la 1ère semaine est pleine (lundi à dimanche), la deuxième aussi et la troisième semaine est entamée par un seul jour qui est donc un lundi.

Si le 1er jour de la période est un mardi, la première semaine (en raisonnant en nombre de jours) se termine le lundi d’après, la deuxième semaine se terminera donc aussi un lundi et la période s’achévera par un mardi car le reste est égal à 1.

Et ainsi de suite pour chaque jour de début de période et pour chaque valeur du reste.

Si on affecte un numéro à chaque jour de la semaine en commençant par 1 pour lundi jusqu’à 7 pour dimanche, on peut déterminer le nombre de jours non ouvrés (JNO) en fonction du 1er jour (W=1(lundi),2(mardi),…7(dimanche)) de la période et de la valeur du reste (R) :

si le Reste = 6 et le 1er jour = dimanche, la 1ère semaine pleine s’arrête un samedi, pareil pour la deuxième semaine. Le reste de 6 jours s’étend donc du dimanche au vendredi, soit un seul jour non ouvré à retrancher.

Résumé pour la suite des possibilités :

Finalement on constate que :

• si W = 7 donc JNO = 1
• si W + R = 7 donc JNO = 1
• si W + R >7 donc JNO = 2

Pour les autres possibilités, JNO = 0 et on ne retranche aucun jour au reste.

On s’intéresse maintenant au reste pour lui retrancher le nombre de jours non ouvrés qui le compose.
Comme vu précédemment, pour réaliser le calcul il faut au moins savoir quel est le jour de semaine de la première date. S’agit-il d’un dimanche, d’un samedi, d’un vendredi,…
La fonction standard VBA WeekDay() permet de déterminer le jour de semaine de la date passée en paramètre. Le deuxième paramètre (vbMonday) impose que la valeur retournée tienne compte que le 1er jour de la semaine est un lundi.
Si startdate est un lundi, Weekday retournera la valeur 1, 2 pour un mardi, …, 7 pour un dimanche. Sans ce deuxième paramètre défini, la semaine commence par défaut au dimanche (états-Unis). Si startDate est un lundi, WeekDay retournerait la valeur 2, 3 pour un mardi, 7 pour un samedi et 1 pour dimanche.

Cette formule permet de simplifier la suite des calculs.

Si le Reste est supérieur ou égal à W (R > 7 – W), on retranche 1 jour non ouvré (-1)
On retranche un autre jour non ouvré si w est différent d’un dimanche et si w est différent du Reste. En effet, si le test logique (w <> 0 And w <> r) est vrai, il retourne -1 (True) en VBA. Si le test renvoi faux, on retire rien (False = 0).
Finalement en une ligne de code, on gère les 3 cas de soustraction.

C’est sûr, ce n’est pas un code ‘académique’ mais il fallait respecter l’objectif de rapidité !

Finalement, on affecte à la fonction la valeur du nombre de jours ouvrés.

Qualité des résultats
Pour vérifier la fonction j’utilise comme référence la fonction trouvée sur le site de Microsoft. Elle a été un peu épurée pour l’utiliser aussi dans la comparaison de performance.

La fonction ci-dessous génère aléatoirement deux dates supérieures ou égales au 30/12/1899 puis on compare les résultats obtenus.

La fonction de référence :

La fonction de comparaison des résultats (100 000 tests par défaut !)

Aucune erreur n’a été constatée pendant mes essais.

Performance
Test effectué sur 1 000 000 de calcul par défaut.
Commenter la ligne de la fonction non testée.

 
La fonction semble deux fois plus rapide que la fonction de référence.

@+

Philippe