Il y a quelques jours, l’un de mes lecteurs a demandé une formule pour retourner toutes les dates de travail entre deux dates dans Google Sheets.
Il voulait simplement exclure les week-ends et lister toutes les dates de travail entre une date de début et une date de fin.
J’ai poussé la réflexion plus loin !
En plus de simplement générer une liste de dates comme indiqué ci-dessus, je vais vous montrer comment exclure des vacances spécifiques (locales, nationales, internationales) de la liste.
Formule pour lister toutes les dates de travail entre deux dates dans Google Sheets
Dans l’un de mes articles récents, Comment générer des dates séquentielles en excluant les week-ends dans Google Sheets, j’ai expliqué une partie de la solution à ce problème.
La formule que j’ai partagée retourne un nombre spécifié de dates de travail à partir d’une date de début. La date de fin ne faisait pas partie de cette formule.
J’y ai déjà expliqué comment utiliser la fonction WORKDAY.INTL pour cela.
Voici un récapitulatif.
La date de début suivante se trouve dans la cellule C2, et la date de fin se trouve dans la cellule C3.
Date de début : 15/12/2019
Date de fin : 02/01/2020
Je ne tiens pas compte de la date de fin pour le moment.
La formule suivante liste cinq dates de travail (‘n’ dates) du 16/12/2019 au 20/12/2019.
=ArrayFormula(WORKDAY.INTL(C2,sequence(5,1),1))
Comment spécifier la date de fin dans la formule?
La formule de tableau ci-dessus retournera cinq dates de travail à partir de la date de début. Le nombre de dates de travail est contrôlé par sequence(5.
Cela signifie que nous pouvons contrôler la date de fin en modifiant la partie Séquence de la formule ci-dessus.
Pour comprendre comment fonctionne la formule WORKDAY.INTL ci-dessus, veuillez consulter le post/tutoriel mentionné ci-dessus.
Dans cet article, vous apprendrez comment retourner toutes les dates de travail entre deux dates, c’est-à-dire une date de début et une date de fin, dans Google Sheets.
Cela signifie, comment spécifier une date de fin dans la formule ci-dessus ?
Avant de commencer, vous devez savoir quelles dates exclure en tant que jours fériés du week-end de la liste des dates de travail entre les deux dates mentionnées ci-dessus.
J’ai utilisé le numéro de week-end 1 pour exclure le samedi et le dimanche de la formule ci-dessus (veuillez voir la partie en gras).
Définir la semaine de travail dans la formule
Ici, dans notre pays, la semaine de travail est de cinq jours du lundi au vendredi. Le numéro qui représente cela (numéro de week-end) est 1.
Dans votre pays (ou votre entreprise), la semaine de travail peut être de cinq jours ou même de six jours (travaillant du samedi au jeudi). Cela signifie que les week-ends sont différents.
Si tel est le cas, vous pouvez trouver le numéro de week-end correspondant à utiliser ici – Comment utiliser les fonctions de date de Google Sheets.
Dans cet article, veuillez lire la fonction NETWORKDAYS.INTL pour obtenir le numéro de week-end que vous souhaitez. Je ne répète pas la même chose ici.
Il est essentiel de retourner toutes les dates de travail entre deux dates (une date de début et une date de fin) dans Google Sheets.
Nous utiliserons ici la même fonction, à savoir NETWORKDAYS.INTL, pour inclure la date de fin dans la formule ci-dessus.
Le rôle de NETWORKDAYS.INTL dans la séquence
Avec la fonction NETWORKDAYS.INTL, nous pouvons trouver la différence de dates entre deux dates, pas seulement la différence de dates !
Pour calculer la différence de dates, il existe d’autres fonctions, telles que DAYS et DATEDIF.
=DAYS(C3,C2) =DATEDIF(C2,C3,"D") =C3-C2
Les formules ci-dessus renverront la différence de dates des deux dates dans les cellules C2 et C3, soit 18.
Ce que nous voulons, c’est le nombre total de jours ouvrés entre les dates dans les cellules C2 et C3.
Nous pouvons le trouver en utilisant la fonction NETWORKDAYS.INTL dans Google Sheets.
=NETWORKDAYS.INTL(C2,C3,1)
La formule ci-dessus renverra 14.
Explication :
C2 – date de début
C3 – date de fin
,1) – numéro de week-end (samedi et dimanche)
Formule pour lister/générer toutes les dates de travail entre deux dates dans Google Sheets :-
Je vais utiliser la fonction NETWORKDAYS.INTL dans la partie séquence de la formule ci-dessus.
Modifiez cette formule qui renvoie 5 (ou vous pouvez dire ‘n’) dates de travail à partir de la date de début (veuillez vous référer à la capture d’écran ci-dessus) ;
=ArrayFormula(WORKDAY.INTL(C2,sequence(5,1),1))
- pour une formule qui renvoie un certain nombre de dates de travail à partir d’une date de début.
Un certain nombre de dates de travail signifie le total des dates de travail entre les deux dates (date de début et date de fin).
=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1))
Remarque :
Utilisez le même numéro de week-end dans les fonctions WORKDAY.INTL et NETWORKDAYS.INTL.
MISE À JOUR 1 le 20-10-2021 :
Parfois, la formule ci-dessus peut renvoyer 1 à 2 jours de travail supplémentaires à la fin de la liste. Pour résoudre ce problème, nous pouvons utiliser la fonction QUERY.
=query(ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1)),"Select * where Col1 <date '"&TEXT(C3,"yyyy-mm-dd")&"'")
Dates de travail entre deux dates et exclusion de vacances spécifiques
La formule ci-dessus exclut simplement les week-ends spécifiés des dates générées.
Que se passe-t-il si vous souhaitez exclure des vacances spécifiques (locales, nationales, internationales) de la liste ?
Avec une colonne supplémentaire pour les vacances, nous pouvons gérer cela.
Étapes :
Saisissez les vacances sous forme de liste et référez-vous à cette liste dans la formule.
J’ai saisi deux jours fériés dans la cellule D2 et D3, qui sont le 25/12/2019 (Noël) et le 01/01/2020 (jour de l’An).
Comment exclure ces deux jours fériés des dates de travail générées du 15/12/2019 au 02/01/2020 ?
Si vous vérifiez la syntaxe des fonctions WORKDAY.INTL et NETWORKDAYS.INTL, vous verrez que ces fonctions ont des arguments facultatifs pour exclure les jours fériés.
NETWORKDAYS.INTL(date_de_début, date_de_fin, [numéro_de_week-end], [jours_fériés]) WORKDAY.INTL(date_de_début, nombre_de_jours, [numéro_de_week-end], [jours_fériés])
Voyez comment j’utilise ces arguments facultatifs dans ma formule en spécifiant D2:D5 comme jours fériés.
Formule pour lister toutes les dates de travail entre deux dates en excluant des vacances spécifiées dans Google Sheets
=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3))
MISE À JOUR 2 le 20-10-2021 :
Ici aussi, nous devons modifier la formule ci-dessus en utilisant QUERY.
=query(ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3)),"Select * where Col1 <date '"&TEXT(C3,"yyyy-mm-dd")&"'")
C’est tout. Amusez-vous bien !
