Dans ce tutoriel Google Sheets, nous allons créer une formule pour vérifier si une date saisie existe déjà et, le cas échéant, générer la prochaine date disponible. De plus, vous avez la possibilité d’exclure les week-ends si vous le souhaitez.
Pour générer la prochaine date disponible, nous utiliserons la fonction WORKDAY.INTL avec les fonctions XMATCH, IF et ISNA dans Google Sheets.
Comment générer la prochaine date disponible peut être bénéfique ?
Permettez-moi de clarifier comment cela fonctionne. La formule vérifiera si la date donnée existe déjà dans une liste. Si la date est déjà présente, la formule fournira la prochaine date disponible ; sinon, elle renverra la date saisie.
Cette fonctionnalité peut être précieuse dans divers scénarios, tels que la planification de projets, la réservation de chambres, d’événements et de nombreuses autres situations où les dates réservées sont réparties.
Par exemple, si les dates réservées sont le 01 janvier 2023, le 04 janvier 2023, le 03 janvier 2023, le 05 janvier 2023 et le 07 janvier 2023, et qu’une autre demande de réservation est faite pour le 04 janvier 2023, la formule suggérera le 06 janvier 2023 comme prochaine date disponible.
Vous pouvez ensuite entrer la date renvoyée dans la liste, en évitant les entrées de dates en double dans une colonne de dates.
Comme mentionné, vous pouvez exclure les week-ends lors de la génération de la prochaine date disponible.
Formule du générateur de prochaine date disponible pour Google Sheets
Voici la formule pour vérifier si une date saisie existe déjà et, si c’est le cas, renvoyer la prochaine date disponible ; sinon, renvoyer la date saisie.
=IF(ISNA(XMATCH(C2,A:A)), C2, WORKDAY.INTL(C2, 1, « 0000000 », A1:A))
J’ai codé cette formule en supposant que la date que vous souhaitez tester se trouve dans la cellule C2, et que la liste des dates se trouve dans la colonne A.
Cette formule ne tient pas compte de l’exclusion des week-ends lors de la génération de la prochaine date disponible. Nous aborderons une telle formule ensuite. Avant cela, laissez-moi expliquer la structure de cette formule :
Anatomie de la formule
Nous pouvons diviser la formule en trois parties : XMATCH, WORKDAY.INTL et le test logique IF.
Partie #1: XMATCH
La fonction XMATCH, écrite comme XMATCH(C2, A:A) dans la formule, vérifie si la date saisie dans la cellule C2 existe déjà dans la liste des dates de la colonne A.
Syntaxe :
XMATCH(clé_recherche, plage_recherche, [mode_correspondance], [mode_recherche])
Dans cette formule, C2 est utilisé comme clé_recherche et la colonne A:A est la plage_recherche.
Si la date est trouvée, XMATCH renverra la position où se trouve la date ; sinon, il renverra #N/A.
Nous avons encadré la formule XMATCH avec ISNA pour renvoyer TRUE si la recherche renvoie #N/A et FALSE si elle trouve une correspondance.
Partie #2: WORKDAY.INTL
Le rôle de la fonction WORKDAY.INTL est de générer la prochaine date disponible. Elle est écrite comme WORKDAY.INTL(C2, 1, « 0000000 », A1:A) dans la formule.
Syntaxe :
WORKDAY.INTL(date_début, jours_ouvres, [week-end], [jours_fériés])
Où :
- C2 : Il s’agit de la date de départ (la date dans la cellule C2).
- 1 : Cette valeur représente le nombre de jours ouvrés à ajouter. Dans ce cas, elle est fixée à 1, ce qui permet de calculer le prochain jour ouvrable.
- « 0000000 » : La chaîne de texte spécifie les week-ends. Dans ce cas, « 0000000 » signifie que aucun jour (0) n’est considéré comme week-end.
- A1:A : La plage A1:A est utilisée pour identifier les jours fériés ou les jours non travaillés. La formule exclut ces dates lors de la génération du prochain jour ouvrable.
Partie #3: IF
La fonction IF est utilisée pour déterminer si la date saisie existe déjà et renvoyer la prochaine date disponible en conséquence.
Syntaxe :
IF(expression_logique, valeur_si_vrai, valeur_si_faux)
L’expression_logique est la sortie de ISNA. Si elle évalue à TRUE, la formule renverra la date de C2. Si elle est FALSE, la formule passe à la partie WORKDAY.INTL (#3) pour renvoyer la prochaine date disponible.
En résumé, si la date saisie dans la cellule C2 n’est pas trouvée dans la liste (colonne A), elle renvoie la même date. Si la date saisie est déjà dans la liste, elle calcule le prochain jour ouvrable comme prochaine date disponible.
Voilà la logique de notre générateur de prochaine date disponible dans Google Sheets.
Comment générer la prochaine date disponible en excluant les week-ends
Si vous souhaitez exclure des jours spécifiques, tels que les week-ends, lors de la génération de la prochaine date disponible, vous pouvez modifier la partie « 0000000 » de la formule comme suit :
Dans l’exemple ci-dessus, les 7 zéros indiquent 7 jours ouvrés, où le premier 0 représente le lundi et le dernier 0 représente le dimanche. Pour exclure les week-ends, remplacez « 0000000 » par « 0000011 ». Cette modification garantit que les samedis et dimanches ne sont pas considérés comme des jours ouvrés lors de la détermination de la prochaine date disponible.
En résumé, un zéro signifie que le jour est un jour ouvré, et un 1 signifie que le jour est un week-end.
Voici la formule pour générer la prochaine date disponible en excluant les week-ends samedi/dimanche :
=IF(ISNA(XMATCH(C2, A:A)), C2, WORKDAY.INTL(C2, 1, « 0000011 », A1:A))
Générer la prochaine date disponible en fonction d’une condition dans Google Sheets
Parfois, vous devrez peut-être générer la prochaine date disponible en fonction d’une condition spécifique.
Par exemple, si vous avez les numéros de chambre d’hôtel dans la colonne A et les dates réservées dans la colonne B, et que vous souhaitez trouver la prochaine date disponible pour la chambre n°206, vous appliquerez une condition.
La formule suivante renvoie la prochaine date disponible pour la chambre n°206 :
=IF(ISNA(XMATCH(D2, FILTER(B:B, A:A=D3))), D2, WORKDAY.INTL(D2, 1, « 0000000 », B1:B))
Cette formule diffère de notre formule précédente de génération de prochaine date disponible en un aspect, en particulier dans XMATCH.
Dans nos formules de génération de prochaine date disponible précédentes, la plage_recherche dans XMATCH était simplement A:A (colonne de dates). Cependant, dans cette formule, elle est FILTER(B:B, A:A=D3).
La fonction FILTER ne filtre que les dates dans B:B qui correspondent à la chambre n°206 (D3) dans A:A.
Syntaxe :
FILTRE(plage, condition1, [condition2, …])
Modèle de générateur de prochaine date disponible
Cliquez sur le lien ci-dessous pour prévisualiser et copier le modèle de générateur de prochaine date disponible gratuitement :
NADG Template (lien avec le texte « bolamarketing.com »)
Le modèle contient trois feuilles :
- NAD : Prochaine date disponible
- NAD_EW : Prochaine date disponible (en excluant les week-ends)
- C_NAD : Prochaine date disponible conditionnelle (en excluant les week-ends)
Vous pouvez utiliser les modèles de deux manières :
- Avec une liste existante de dates : Copiez et collez les dates dans la colonne A dans les deux premières feuilles et dans la colonne B dans la dernière feuille.
- Vous n’avez besoin d’utiliser qu’une seule feuille.
- La configuration régionale du modèle est « UK ». Ouvrez le modèle copié et cliquez sur Fichier> Paramètres pour changer la configuration régionale avant de commencer à l’utiliser.
- Nouvelle : Videz les dates dans la colonne A dans les deux premières feuilles et dans la colonne B dans la dernière feuille. Entrez la première date dans la cellule A1 ou B1, en fonction de la feuille que vous utilisez.
Insérez votre date à tester dans la cellule C2 dans les deux premières feuilles et la cellule D2 dans la dernière feuille. Le critère est inséré dans la cellule D3.
Pour modifier les week-ends, suivez la partie pertinente du tutoriel ci-dessus.
