Comment savoir si une date tombe un week-end sur Excel ?

Dans ce tutoriel, je vais vous montrer une série de formules qui va nous permettre de déterminer rapidement si une date donnée tombe pendant un week-end ou en cours de semaine. Nous verrons également comment extraire directement des dates d’une base de données afin de compter le nombre de dates tombant un week-end, tout en appliquant des critères de sélection.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Introduction

Comme nous venons de le voir à l’instant, nous allons maintenant voir comment déterminer si une date donnée tombe un week-end.

Et pour illustrer ce tutoriel, nous allons utiliser la base de données suivantes :

Dans celle-ci, nous retrouvons donc une liste des déplacements effectués par des commerciaux d’une entreprise.

L’objectif sera alors de déterminer si ces déplacements ont eu lieu en semaine ou durant le week-end, ce qui permettra par exemple de calculer des primes à leur verser.

2. Comment savoir si une date tombe un week-end

Bien entendu, pour déterminer si une date donnée tombe en semaine ou week-end, nous allons tout d’abord devoir déterminer le jour correspondant dans la semaine.

Pour cela, nous allons pouvoir utiliser une fonction que nous avons déjà utilisée à plusieurs reprises au cours des tutoriels précédents, il s’agit de la fonction JOURSEM().

 =JOURSEM(numéro_de_série;type_retour) 

Les deux arguments attendus sont les suivants :

• Numéro_de_série : il s’agit de la date pour laquelle nous souhaitons obtenir le numéro du jour au sein de la semaine
• Type_retour : permet de spécifier le type de semaine à utiliser en fonction des jours de début et de fin :

Pour une semaine classique en France, nous souhaiterons que le premier jour soit un lundi et le dernier un dimanche, nous saisirons alors la valeur « 2 » à cet argument type_retour.

Dans notre exemple, nous saisirons donc la formule suivante dans la cellule C9 :

 =JOURSEM([@[Date déplacement]];2) 

Cela nous permet donc d’obtenir la position de chaque journée au sein de la semaine, en sachant que les jours de week-end, donc le samedi et le dimanche arrivent respectivement en 6^e et 7^e position.

Pour déterminer s’il s’agit d’un jour de week-end, il suffira donc de modifier la formule comme ceci :

 =SI(JOURSEM([@[Date déplacement]];2)>=6;"Week-end";"Semaine")  

3. Comment compter le nombre jours tombant un week-end

Maintenant que nous avons déterminé si une date tombe durant la semaine ou le week-end, nous allons pouvoir dénombrer très rapidement le nombre de déplacements effectués uniquement sur les samedis et dimanches.

Pour cela, il nous suffira en effet d’utiliser la fonction NB.SI() qui permet de dénombrer des cellules en fonction d’un critère donné :

 =NB.SI(plage;critère) 

Ici, l’argument plage correspond aux cellules dans lesquelles nous venons d’insérer la formule de détermination du type de déplacement (la colonne « Est week-end ? ») et l’argument critère correspond au critère permettant d’effectuer le dénombrement :

 =NB.SI(baseDeplacements[Est week-end ?];"Week-end")  

Ici, l’argument critère étant un texte, nous le saisissons entre guillemets.

Il y a donc 38 déplacements effectués par tous les commerciaux pendant les week-ends.

Maintenant, imaginons que nous souhaitions réaliser le même calcul, mais sans utiliser de colonne de traitement intermédiaire.

Dans ce cas-là, nous ne pourrons utiliser la fonction NB.SI(), étant donné que celle-ci ne permet d’effectuer de traitement sur l’argument plage.

À la place, nous utiliserons plutôt la fonction SOMMEPROD(), que nous détournerons de son usage classique pour effectuer des calculs sur la base de données, comme nous l’avons vu lors d’un tutoriel dédié à cette fonction très puissante.

Pour effectuer ce calcul, nous commençons par appeler la fonction SOMMEPROD() :

Ensuite, nous y saisissons notre critère de dénombrement entre parenthèses, en reprenant la formule vue précédemment :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)>=6)) 

Si nous validons à ce moment-là, nous obtiendrons comme résultat 0 :

En effet, cette formule ne permet pour le moment que d’obtenir une matrice constituée de VRAI et de FAUX, comme nous le montre l’outil d’évaluation des formules :

Il suffit ensuite de multiplier cette matrice par « 1 » pour remplacer les VRAI par des 1 et les FAUX par des zéros, de manière à ce qu’Excel en calcul la somme et donc détermine le nombre VRAI :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)>=6)*1) 

Ce qui nous permet bien entendu d’obtenir exactement le même résultat, à savoir 38 déplacements effectués durant des jours de week-end :

4. Comment compter le nombre de déplacement en week-end par commercial ?

Maintenant que nous savons comment calculer le nombre de week-ends total, nous pouvons développer un tout petit peu la formule pour obtenir l’information par commercial.

Pour cela, nous allons reprendre la dernière formule :

Puis, nous allons y introduire un second critère qui va correspondre au nom du commercial.

Pour cumuler les critères de sélection dans la fonction SOMMEPROD(), il faut multiplier les matrices de critères entre elles, en les saisissant entre parenthèses :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)>=6)*(baseDeplacements[Commercial]=[@Commercial])*1) 

Enfin, pour déterminer le nombre de déplacements effectués en semaine par commercial, il suffit d’inverser l’inégalité afin de ne garder dans le calcul que les jours de la semaine inférieurs ou égaux à 5 :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)<=5)*(baseDeplacements[Commercial]=[@Commercial])*1)