XL 2019 Comptabiliser un nb de jours fériés devenu variable dans une année glissante

[JoBar57]

Bonjour,

Dans mon tableau de décompte des jours fériés et des vacances, je me trouve confronté à un problème de jours fériés qui passe de nb fixe en année standard à un nb variable en année glissante dans le cas de figure où l'année débute par le mois de juin.
Exemple dans le fichier joint dans l'onglet "Tableau", pour le personnel "f", en 2020 j'ai 10 jours fériés (F) alors que pour 2021 je n'en ai plus que 8 : couleur orange sur le numéro du jour.
Actuellement dans mon fichier le nb de jours fériés est fixe et varie seulement en fonction de la date d'embauche et de l'année (n) - (n-1) - (n-2) soit 0, soit 9 dans les cellules AH32 AH33 et AH34
Du coup je sèche sur la meilleure formule à utiliser. Je pensais utiliser NB.SI.ENS mais la formulation est trop compliquée pour moi avec 12 plages à comparer (D15 - AH15, D20 - AH20 ....... jusqu'à D70 - AH70) à une plage de données "Fériés_2ans" (cellules AL20 à AL39 dans l'onglet "Entrées") c'est à dire les dates des jours fériés de l'année en cours ainsi que ceux de l'année suivante.
En plus il faudrait intégrer les notions de "n - n-1 et n-2"
Et je n'arrive donc pas à la faire fonctionner.

Je ne sais pas si c'est très clair tout ça , mais avec le fichier joint (mot de passe "Precaution") ça le sera certainement plus !

Merci beaucoup à ceux qui prendrons un peu de temps pour me retirer cette épine du pied !

Cordialement

 

[Bernard_XLD]

Bonjour JoBar57, le forum

Ma foi, je vous avais déjà donné une fonction capable de donner tous les jours fériés entre deux dates, n'importe lesquelles, même sur plusieurs dizaines d'années au besoin, et vous ne l'avez pas gardée.
Vous pourrez la trouver ici.
https://www.excel-downloads.com/threads/automatisme-des-jours-feries.20056501/

Bien cordialement, @+

 

[JoBar57]

Bonjour JoBar57, le forum

Ma foi, je vous avais déjà donné une fonction capable de donner tous les jours fériés entre deux dates, n'importe lesquelles, même sur plusieurs dizaines d'années au besoin, et vous ne l'avez pas gardée.
Vous pourrez la trouver ici.
https://www.excel-downloads.com/threads/automatisme-des-jours-feries.20056501/

Bien cordialement, @+

Bonjour,

Merci pour votre réponse rapide, mais j'avais bien conservé ces fonctions mais sans en comprendre vraiment la fonction (comment "TJF" peut restituer une date différente à chaque ligne alors que la formule est totalement identique ?) et surtout l'usage. J'ai déjà essayé de l'utiliser, mais je ne suis arrivé à rien. => Voir dans le cadre AG4 - AN16 dans "Entrées" où j'ai remis ce que je croyais pouvoir fonctionner
Je pensais m'en sortir plus facilement avec mon propre tableau de calcul automatique des jours fériés mais qu'il me fallait comparer aux dates du planning de l'onglet "tableau". Mais pour l'instant je reste bloqué là aussi.

Cordialement,

 

[Hasco]

Bonjour,

Votre dernier fichier est protégé !

Cordialement

 

[Bernard_XLD]

Bonjour Jobar57, Hasco, le forum

comment "TJF" peut restituer une date différente à chaque ligne alors que la formule est totalement identique ?) et surtout l'usage. J'ai déjà essayé de l'utiliser, mais je ne suis arrivé à rien.

Il y a plein d'applications possibles, la fonction peut, entre autres, renvoyer un tableau de dates que l'on peut parcourir ou tester si une date précise est fériée, j'ai monté dans votre feuille "Entrées" une matricielle verticale pour votre tableau de jours fériés pour la Suisse, canton de Genève de 2019 à 2022. Il vous suffit de changer les dates en AL et AM17 pour mettre le tableau à jour.
Si vous voulez les jours fériés français, mettez 33 à la place de 41, tout est expliqué dans le code de la fonction, vous pouvez aussi pour la Suisse, y ajouter les jours chômés en option (ex: la fête du travail pour Genève).
J'ai utilisé la dernière version 3.1 de la fonction TJF, la prochaine version 3.2, en cours de codage et presque finalisée, pourra aussi reporter, en option, le libellé des jours fériés.
C'est une matricielle standard, 2019 oblige, donc délimitée au départ et validée par CtrlL/Maj/Ent, j'ai indiqué sa taille en fond vert.

[édition Ps: si vous êtes sûr d'avoir au moins un jour férié entre les deux dates, vous pouvez aussi utiliser en formule
=NBVAL(TJF("01/01/2021";"31/12/2021"))
sinon, utilisez simplement NB
=NB(TJF("01/01/2021";"31/12/2021"))
(dates en texte ou en valeur, en paramètre ou en cellule) qui vous renverra le nombre de jours fériés entre les deux dates, les paramètres optionnels, pays, region restent opérationnels et vous pouvez les utiliser. La fonction peut aussi être encapsulée avec une fonction de recherche en formule ou en Vba]

Bien cordialement, @+

[fichier modifié]

 

[Bernard_XLD]

Bonjour

Voici votre fichier avec, en avant première, la nouvelle version 3.2 intégrant les libellés des jours fériés en paramètre optionnel. J'ai corrigé pour exemple vos formules en AL-AN16 en utilisant NB
J'ai corrigé aussi pour exemple vos formules pour la Suisse, canton de Genève en AH32-34

Bien cordialement, @

[fichier modifié]

 

[JoBar57]

Bonjour,

Merci encore pour la réponse et le temps consacré.

Concernant mon projet, le but était de reporter dans différentes cellules le nombre de jours fériés (Genève) pour l'année n-2, n-1, n, et n+1. Pour cela la formule =NBVAL(TJF(AG5;AG6)) semblait convenir mais elle me renvoie le nb de jours fériés en France (exemples en AQ5 à AQ8). Comment puis-je corriger cela (ce qui me permettra de comprendre comment ça fonctionne : nécessité que la macro soit incluse dans le fichier, etc...) ?

Ou alors le tableau (fond saumon) aurait aussi pu être utile mais les dates des fériés (en colonne par année) ne correspondent à rien !? Le but étant d'avoir pour chaque colonne les fériés compris entre la date de début et de fin de mes différentes années glissantes.

La matrice (fond vert) est impeccable pour s'y retrouver avec les jours fériés suivant les pays, mais ne me permet pas d'avoir le nombre de jours fériés entre la date du premier jour et du dernier jour de mon planning.

Cordialement, @+

 

[JoBar57]

Bonjour,

Votre dernier fichier est protégé !

Cordialement

Bonjour,

oui effectivement, le mot de passe figure dans le 1er post : "Precaution"

Cordialement,

 

[Bernard_XLD]

Re,

Bonjour,

Merci encore pour la réponse et le temps consacré.

Concernant mon projet, le but était de reporter dans différentes cellules le nombre de jours fériés (Genève) pour l'année n-2, n-1, n, et n+1. Pour cela la formule =NBVAL(TJF(AG5;AG6)) semblait convenir mais elle me renvoie le nb de jours fériés en France (exemples en AQ5 à AQ8). Comment puis-je corriger cela (ce qui me permettra de comprendre comment ça fonctionne : nécessité que la macro soit incluse dans le fichier, etc...) ?

Ou alors le tableau (fond saumon) aurait aussi pu être utile mais les dates des fériés (en colonne par année) ne correspondent à rien !? Le but étant d'avoir pour chaque colonne les fériés compris entre la date de début et de fin de mes différentes années glissantes.

La matrice (fond vert) est impeccable pour s'y retrouver avec les jours fériés suivant les pays, mais ne me permet pas d'avoir le nombre de jours fériés entre la date du premier jour et du dernier jour de mon planning.

Je viens de modifier le post 6, j'ai ajouté des explications et mis le fichier à jour, j'ai corrigé vos formules, vous avez des exemples pour la France et la Suisse pour les nombres de jours fériés entre deux dates.
Entre le 01/06/2020 et le 31/05/2021, il y a bien 12 jours fériés en France, le lundi de Pentecôte tombant le 01/06 en 2020 et le 24/05 en 2021.

nécessité que la macro soit incluse dans le fichier,

Pour votre question, la fonction personnalisée doit effectivement être intégrée au fichier pour être utilisée.
Demain, je serai absent à partir du début de matinée et ne pourrai pas vous répondre avant le soir.
N'hésitez pas à relire les explications dans le code de la fonction et à poser vos questions si vous en avez.
Vous trouverez aussi des exemples d'utilisation sur le fil de la fonction que je vous ai indiqué. Pour Genève, il faut définir le paramètre Pays à 41 pour la Suisse (quand il est absent, il est positionné par défaut à 33 pour la France) et le paramètre Région à "GE" pour le canton de Genève (il y a 26 cantons en Suisse avec des jours fériés différents pour chaque canton).

Bien cordialement, @+

 

[Bernard_XLD]

Re,

Ou alors le tableau (fond saumon) aurait aussi pu être utile mais les dates des fériés (en colonne par année) ne correspondent à rien !?

Dans le fichier joint, je vous ai remis des matricielles correctes dans votre tableau fond saumon.
Fini pour ce soir, bonne nuit

Bien cordialement, @+

[fichier modifié]

 

[Bernard_XLD]

Re,

pour monter vos matricielles correctement, validation par Ctrl/Maj/Entrée

Cordialement, @+

 

[JoBar57]

Re,


Je viens de modifier le post 6, j'ai ajouté des explications et mis le fichier à jour, j'ai corrigé vos formules, vous avez des exemples pour la France et la Suisse pour les nombres de jours fériés entre deux dates.
Entre le 01/06/2020 et le 31/05/2021, il y a bien 12 jours fériés en France, le lundi de Pentecôte tombant le 01/06 en 2020 et le 24/05 en 2021.

Pour votre question, la fonction personnalisée doit effectivement être intégrée au fichier pour être utilisée.
Demain, je serai absent à partir du début de matinée et ne pourrai pas vous répondre avant le soir.
N'hésitez pas à relire les explications dans le code de la fonction et à poser vos questions si vous en avez.
Vous trouverez aussi des exemples d'utilisation sur le fil de la fonction que je vous ai indiqué. Pour Genève, il faut définir le paramètre Pays à 41 pour la Suisse (quand il est absent, il est positionné par défaut à 33 pour la France) et le paramètre Région à "GE" pour le canton de Genève (il y a 26 cantons en Suisse avec des jours fériés différents pour chaque canton).

Bien cordialement, @+

Bonsoir,

Merci beaucoup pour les explications et les corrections.

Je travaillerai en journées complètes de 12h aussi et je m'occuperai de mettre cela en pratique sur le fichier d'origine dès que je le pourrai afin de vérifier que j'ai bien tout compris et que c'est ok.
Si besoin, je me permettrai de revenir vers vous.

Cordialement, @+

 

[Bernard_XLD]

Bonjour

Je n'y ai pas pensé mais, étant sous 2019, vous pouvez rencontrer ce problème avec NB ou NBVAL sur les formules que j'ai modifié avec 365, ce qui pourrait nuire à votre compréhension.
https://www.excel-downloads.com/thr...-dynamiques-automatiques-dexcel-365.20057540/
Dans le dernier fichier, vous avez pu avoir des matricielles non nécessaires en AL-AN-AP16 et en AH32-33-34.
Ce fichier modifié doit éviter ce problème de rétro compatibilité.
J'ai mis à jour TJF avec la dernière version déployée aujourd'hui, la 3.4 .

Bien cordialement, @+
[fichier modifié]

 

[JoBar57]

Bonjour,

Merci de prendre le temps et d'avoir la patience pour me donner les informations et corriger ce qui ne colle pas.

Par contre c'est le dernier fichier qui me donne un résultat avec des erreurs,

ce qui n'est pas le cas d'une version précédente du fichier ( document joint).

J'ai intégré et adapté tout ça à mon fichier original, cela semble fonctionner (à voir quand je l'aurai transféré sur l'ordi du boulot

Par contre dans mon fichier original il y a des retours affichés dans les 2 premières colonnes mais pas dans la 3e alors que les formules sont identiques ! Je ne comprend pas pourquoi cette différence !

Puis dans la mise en forme conditionnelle les cases vides dans la ligne des numéros des jours (onglet "Planning") sont colorées alors que ce n'était pas le cas avant !?
Là aussi je ne comprend pas pourquoi cette différence.

ET encore merci,

Cordialement, @+

 

[Bernard_XLD]

Bonjour

c'est le dernier fichier qui me donne un résultat avec des erreurs

Ok, donc l'encapsulation permet d'éviter le problème, vous me confirmez que vous ne voyez pas de matricielles dans ces cellules ? (désolé mais je n'ai que 365 et je ne peux pas tester cela)
Je corrige votre dernier fichier pour ces cellules, j'utilise NB qui sera au final plus adapté à votre utilisation.

Par contre dans mon fichier original il y a des retours affichés dans les 2 premières colonnes mais pas dans la 3e alors que les formules sont identiques ! Je ne comprend pas pourquoi cette différence !

Vous avez du faire des essais de matricielles qui n'ont pas fonctionné ce qui a bloqué pendant un recalcul l'adaptation du tableau à la matrice, un appui sur F9 aurait réglé le problème.
Les #N/A, pour not available, sont un fonctionnement normal quand la taille de la matrice de cellules est supérieure au nombre de données renvoyées, ce qui arrive quand on ne connait pas le nombre de données qui vont être renvoyées au final.
Si une matricielle de cellules comprend 10 cellules, si le calcul renvoie 8 valeurs, la matricielle affiche 8 valeurs et 2 N/A. si le calcul renvoie 12 valeurs, la matricielle de cellules n'en renvoie que 10. Je contourne ce problème dans la fonction en déterminant la taille de la matrice, son orientation et en adaptant la taille du tableau de données à la taille de la matrice de cellules si le tableau est plus petit, cela évite l'affichage des N/A en permettant de construire une matricielle de cellules suffisamment grande pour accueillir le nombre maximum de données que renverra la formule selon les cas (exemple matrice verte). Vous avez d'ailleurs le problème dans votre tableau saumon qui ne comprend que 11 possibilités pour, par effet calendaire des jours mobiles, 12 valeurs max en france métropolitaine, certains Cantons de Suisse vont jusqu'à15 (16 avec effet calendaire).
Ce problème ne se pose pas avec les matricielles dynamiques de 365 qui se propagent sur le nombre de données renvoyées par le calcul et qui renvoie l'information quand elles n'arrivent pas à se propager.

Puis dans la mise en forme conditionnelle les cases vides dans la ligne des numéros des jours (onglet "Planning") sont colorées alors que ce n'était pas le cas avant !?
Là aussi je ne comprend pas pourquoi cette différence.

Pas touché à cette feuille, il y a des MFC en lien avec la feuille Entrée colonne AL qui met en couleur quand le jour est égal à un jour férié, hors la plage testée comprend une cellule vide ce qui est le cas des cellules concernées.
Plusieurs moyens pour corriger cela:
-tester la chaine vide
-adapter la taille de la recherche à la taille du tableau de jours fériés
-utiliser TJF pour la MFC, voir exemples dans le fil d'origine de la fonction
Pour adapter, il faudrait que je sache si votre fichier doit travailler au final avec des jours fériés français ou Genevois, ou avec les deux en même temps.
Au quel cas, il serait plus simple de stocker l'information par personne pour l'utiliser dans les calculs, ce qui permettrait de fonctionner avec les deux.

En attendant, voila votre dernier fichier repris, avec la dernière version de la fonction.
J'ai retaillé vos matricielles saumon à la taille max
En tout cas, pas d'erreur dans vos calculs avec TJF

Bien cordialement, @+

[fichier modifié]