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

JoBar57 · 9 Novembre 2021

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 · 21 Novembre 2021

Re,

Et voila le même fichier en utilisant TJF pour la MFC, c'est un peu plus simple, vous verrez !
=TJF(D15;;41;"GE")
Dans les deux fichiers, j'ai intégré la dernière version 3.9 de TJF qui enlève le séparateur de date du contrôle.

Bien cordialement, @+

JoBar57 · 13 Novembre 2021

Yeahou à dit:
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.

Bonjour,

Ok compris, je testerai à la prochaine occasion

Yeahou à dit:
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)

effectivement, pas de matricielle donc pas de validation par "CTRL" + "MAJ"

Yeahou à dit:
Plusieurs moyens pour corriger cela:
-tester la chaine vide
-adapter la taille de la recherche à la taille du tableau de jours fériés

Effectivement j'avais mis une ligne de trop, après correction à la taille du tableau des jours fériés tout est rentré dans l'ordre.

Yeahou à dit:
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).

J'ai configuré la matricielle pour le canton de Genève dans ce fichier et du fait des années glissantes on se retrouve avec des jours fériés qui varient entre 8 et 10 au lieu des 9 en années fixes, d'où seulement 11 lignes.

Yeahou à dit:
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

Comment on fait ça ? ..... oups = mon côté "besoin de savoir"

Yeahou à dit:
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 principe ce fichier ne prendra en compte que les fériés du canton de Genève.
Mais qu'entendez-vous par "stocker l'information par personne" et comment on fait ?

Merci beaucoup pour la réponse ce qui solutionne mon post en principe. Les autres questions sont plutôt pour finir de faire le tour de la question et ma formation personnelle, mais la réponse sera dans tous les cas bienvenue.

Cordialement, @+

PS : j'ai enfin retrouvé la solution pour virer mon mot de passe !

Bernard_XLD · 13 Novembre 2021

Re,

JoBar57 à dit:
Comment on fait ça ? ..... oups = mon côté "besoin de savoir"

c'est cette partie qui fait cela :

VB:

        If TypeName(.Caller) = "Range" Then
            .Volatile
            With .ThisCell
                If .FormulaArray = .Offset(0, 1).FormulaArray Then
                    Test_MD = Range(.Address & ":" & .Offset(0, 1).Address).Formula 'pour test matricielle dynamique
                    If Test_MD(1, 1) = Test_MD(1, 2) Then 'teste si matricielle non dynamique
                        For Val3 = 1 To 1000
                            If Not .FormulaArray = .Offset(0, Val3).FormulaArray Then Exit For
                        Next Val3
                    End If
                    If Val3 < Val1 Then Val3 = Val1 'pour le redimensionnement des matricielles dynamiques après modification des données d'entrée
                    ReDim T_J_F2(1 To Val3)
                    Val4 = IIf(Lib, UBound(LibT_J_F), UBound(T_J_F))
                    For Val2 = 1 To Val3
                        If Val2 > Val4 Or Val1 = 0 Then T_J_F2(Val2) = "" Else T_J_F2(Val2) = IIf(Lib, LibT_J_F(Val2), T_J_F(Val2))
                    Next Val2
                    T_J_F3 = T_J_F2
                Else
                    If .FormulaArray = .Offset(1, 0).FormulaArray Then 'teste si matricielle
                        Test_MD = Range(.Address & ":" & .Offset(1, 0).Address).Formula 'pour test matricielle dynamique
                        If Test_MD(1, 1) = Test_MD(2, 1) Then 'teste si matricielle non dynamique
                            For Val3 = 1 To 1000
                                If Not .FormulaArray = .Offset(Val3, 0).FormulaArray Then Exit For
                            Next Val3
                        End If
                        If Val3 < Val1 Then Val3 = Val1 'pour le redimensionnement des matricielles dynamiques après modification des données d'entrée
                        ReDim T_J_F2(1 To Val3)
                        Val4 = IIf(Lib, UBound(LibT_J_F), UBound(T_J_F))
                        For Val2 = 1 To Val3
                            If Val2 > Val4 Or Val1 = 0 Then T_J_F2(Val2) = "" Else T_J_F2(Val2) = IIf(Lib, LibT_J_F(Val2), CLng(T_J_F(Val2)))
                        Next Val2
                        T_J_F3 = Application.Transpose(T_J_F2)
                    Else
                        '' pour matricielles dynamiques sans report de matrice
                        Select Case Val1
                            Case 0
                                T_J_F3 = ""
                            Case 1
                                T_J_F3 = IIf(Lib, LibT_J_F, CLng(T_J_F(1)))
                        End Select
                        ''
                    End If
                End If
            End With
        End If

Application.TypeName(.Caller) = "Range" me précise que l'appel de la fonction vient d'une cellule et Application.Thiscell renvoie la cellule appelant la fonction, après il suffit de tester la portée de la matricielle horizontale ou verticale avec FormulaArray. Mais le plus gros problème a été de différencier les matricielles standard ou il faut adapter le tableau à la taille de la matrice et les matricielles dynamiques d'Excel 365 ou il ne faut pas adapter la taille du tableau pour qu'elles puissent se redimensionner. Je peux tester en Vba la portée d'une matricielle standard et retailler le tableau en conséquence en ajoutant des chaines vides, sans toucher à un tableau alimentant une matricielle dynamique (ce qui l'aurait figée à la plus grande taille sans qu'elle puisse diminuer lors d'un recalcul). Désolé si ce n'est pas assez clair mais c'est vous qui avez demandé

JoBar57 à dit:
En principe ce fichier ne prendra en compte que les fériés du canton de Genève.
Mais qu'entendez-vous par "stocker l'information par personne" et comment on fait ?

Il suffit de stocker quelque part le code pays correspondant à la personne, dans tableau5 par exemple, et d'utiliser ce code lors de l'appel de la fonction, exactement comme on fait pour rechercher une valeur.

Bien cordialement, @+

JoBar57 · 13 Novembre 2021

Bonsoir,

Merci beaucoup pour le temps passé, les explications et votre patience !

Me voilà un tout petit peu mieux outillé pour Excel !

Cordialement,

JoBar57 · 16 Novembre 2021

Re bonjour,

Je reviens brièvement sur mon sujet, car j'ai transféré mon fichier sur l'ordi du boulot (à Genève) et je me retrouve avec un résultat étrange (voir images ci-dessous).
Il manque systématiquement 4 jours fériés et toujours les mêmes : les 1/08 - 25/12 - 31/12 et 1/01.
est-ce que le changement de pays peut avoir une incidence ?
Entre mon ordi au domicile et celui du boulot, il n'y a eu aucun changement d'effectué et les versions d'Excel sont toutes les deux de 2019.

Résultat obtenu au boulot :

Résultat obtenu au domicile :

Résultat obtenu au boulot :

Résultat obtenu au domicile :

Désolé de vous embêter encore mais je ne vois aucune explication évidente !
Quelqu'un aurait-il une explication que je puisse tester ?

Cordialement,

Bernard_XLD · 16 Novembre 2021

Bonjour

Il vous manque les jours fériés à date fixe, un problème de séparateur par défaut renvoyé par la fonction Format en Vba. Le point au lieu du /, je regarde.

Bien cordialement, @+

Bernard_XLD · 16 Novembre 2021

Re,

Voila le fichier modifié pour forcer le séparateur / pour la comparaison des dates.

Bien cordialement, @+

[fichier modifié]

JoBar57 · 18 Novembre 2021

Merci beaucoup, je teste ça dès que possible.

Pour mon information, à quel niveau avez-vous fait cette ou ces modification(s) ?
Est-ce que c'est bien ça ?

Cordialement,

Bernard_XLD · 18 Novembre 2021

Bonjour jobar57, le forum

JoBar57 à dit:
Est-ce que c'est bien ça ?

Non, j'ai fait diverses optimisations, pour ce problème, j'ai modifié ces lignes

Code:

            Select Case Left(DateValue(DateX), 5)

et ces intructions

VB:

Format(DateValue(Dateàanalyser), "dd/mm")

en

Code:

        Select Case Format(DateX, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm")

et

VB:

Format(Dateàanalyser, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm"))

ce qui écarte le séparateur de date par défaut renvoyé par les deux premières pour imposer un "/" ( ou Chr(47) ) pour les comparaisons de dates.
C'est un premier jet pour régler votre problème, je vais regarder pour intégrer directement le séparateur de date par défaut pour les comparaisons , voir l'éliminer de l'équation ce qui serait encore mieux, mais je dois faire des tests pour vérifier que je conserve la rapidité d'exécution et un code sans autre problème, vous trouverez les prochaines versions si il y en a sur le fil d'origine de la fonction, il vous suffit d'appuyer sur le bouton "suivre".
j'ai également variabilisé les dates mobiles pour qu'elles ne soient calculées qu'une fois par an, pour que le code soit plus rapide et que la comparaison se fasse sur les mêmes bases string que les jours fixes.

Code:

For DateX = DateDeb To DateFin
    If Not Annee = Year(DateX) Then 'calcul du Dimanche de Paques et des fêtes mobiles sur chgt d'année de DateX, simplification du calcul originel du dimanche de Pâques by patricktoulon
        Annee = Year(DateX)
        Dim_Paques = CDate(((Round(DateSerial(Annee, 4, (234 - 11 * (Annee Mod 19)) Mod 30) / 7, 0) * 7) - 6))
        Ven_Saint = Format(Dim_Paques - 2, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'Calcul Vendredi Saint, vendredi précédant le Dimanche de Pâques
        Lun_Paques = Format(Dim_Paques + 1, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'Calcul Lundi de Pâques, lundi suivant le Dimanche de Pâques
        Ascension = Format(Dim_Paques + 39, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'Calcul Ascension, 39 jours après le Dimanche de Pâques
        Pentecote = Format(Dim_Paques + 50, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'Calcul Pentecôte, 50 jours après le Dimanche de Pâques
        If Pays = 41 Then
            Fete_Dieu = Format(Dim_Paques + 60, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'Calcul Fête Dieu, 60 jours après le Dimanche de Pâques
            'Calcul Fahrtsfest, Premier jeudi d'avril
            Jour_Sem = 4: Mois_Ref = 4: Num_Cible = 1 'premier jeudi d'avril
            Date_Ref = DateSerial(Year:=Annee, Month:=Mois_Ref, Day:=1)
            Decal_Jour = Jour_Sem - Weekday(Date_Ref, 2): If Decal_Jour >= 0 Then Decal_Jour = Decal_Jour - 7
            Fahrtsfest = Format(Date_Ref + Decal_Jour + (Num_Cible * 7), "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'Calcul Fahrtsfest, Premier jeudi d'avril
            'Calcul Jeûne genevois, jeudi suivant le 1er dimanche de septembre
            Jour_Sem = 7: Mois_Ref = 9: Num_Cible = 1 'premier dimanche de septembre
            Date_Ref = DateSerial(Year:=Annee, Month:=Mois_Ref, Day:=1)
            Decal_Jour = Jour_Sem - Weekday(Date_Ref, 2): If Decal_Jour >= 0 Then Decal_Jour = Decal_Jour - 7
            Jeune_Genevois = Format(Date_Ref + Decal_Jour + (Num_Cible * 7) + 4, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'premier dimanche de septembre + 4 jours pour le jeudi
            'Calcul Lundi du Jeûne fédéral, lundi suivant le 3e dimanche de septembre
            Jour_Sem = 7: Mois_Ref = 9: Num_Cible = 3 'troisième dimanche de septembre
            Date_Ref = DateSerial(Year:=Annee, Month:=Mois_Ref, Day:=1)
            Decal_Jour = Jour_Sem - Weekday(Date_Ref, 2): If Decal_Jour >= 0 Then Decal_Jour = Decal_Jour - 7
            Lundi_du_Jeune_Federal = Format(Date_Ref + Decal_Jour + (Num_Cible * 7) + 1, "dd" & Chr(34) & Chr(47) & Chr(34) & "mm") 'troisième dimanche de septembre + 1 jour pour le lundi
        End If
    End If

la modification dont vous parlez est une simplification sur l'initialisation des matricielles dynamiques, inutile sur votre version mais qui évite un dysfonctionnement sur 365 lors de modifications des valeurs d'entrée, je l'ai intégrée lors de la suppression dans le code global de tableaux intermédiaires non nécessaires.

Bien cordialement, @+

Bernard_XLD · 18 Novembre 2021

Re,

Yeahou à dit:
voir l'éliminer de l'équation ce qui serait encore mieux

assez simple finalement à mettre en place, voila avec la dernière version de TJF, comparaison des dates sur des entiers en DDMM.

Bien cordialement, @+

dernière version

JoBar57 · 21 Novembre 2021

Bonjour,

J'ai modifié mon fichier original au boulot avec votre correction et ça fonctionne, les jours fériés apparaissent bien tous !
Merci pour la correction.

Par contre comme les jours fériés fluctuent entre 8 et 10, puisque la plage s'étend sur 3 années, le total de nb de jours peut être inférieur à 27, comme c'est le cas pour les années 2018 et 2029 où on se retrouve avec respectivement 8 - 10 - 8 jours fériés, donc un jour de moins et avec 2022 un jour de plus sur les 3 années, ce qui m'oblige à inclure la cellule AL47 dans le tableau de recherche.
De ce fait la MFC colorie à nouveau les cases de fin de mois non utilisées (entre 28 et 31), comme si c'était des jours fériés
Est-ce lié à la formule utilisée pour la MFC ?
=D$15=RECHERCHEV(D$15;Entrées!$AL$20:$AL$47;1;0)
Et y a-t-il une autre solution pour la MFC qui fonctionne malgré cette fluctuation de la longueur de la liste des jours fériés.
Sinon une solution pourrait être de prolonger ma liste en mettant une date en AM17 postérieur afin d'inclure des fériés supplémentaires, mais cela ne me semble pas très ~~élégant~~ Excel !
(= solution testée dans le fichier joint)

Merci pour vos lumières.

Cordialement, @+

Bernard_XLD · 21 Novembre 2021

Bonsoir Jobar57, le forum

JoBar57 à dit:
Est-ce lié à la formule utilisée pour la MFC ?
=D$15=RECHERCHEV(D$15;Entrées!$AL$20:$AL$47;1;0)

oui

Yeahou à dit:
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

par exemple, tester la chaine vide
=ET(D$15=RECHERCHEV(D$15;Entrées!$AL$20:$AL$99;1;0);NON(D$15=""))

Bien cordialement, @+

Bernard_XLD · 21 Novembre 2021

Bonjour Jobar57, le forum

voici votre fichier avec la MFC modifiée comme indiqué post précédent.

Cordialement, @+

Bernard_XLD · 21 Novembre 2021

Re,

Et voila le même fichier en utilisant TJF pour la MFC, c'est un peu plus simple, vous verrez !
=TJF(D15;;41;"GE")
Dans les deux fichiers, j'ai intégré la dernière version 3.9 de TJF qui enlève le séparateur de date du contrôle.

Bien cordialement, @+

JoBar57 · 22 Novembre 2021

Bonjour yeahou,

C'est bien plus simple comme ça, en effet (et plus élégant !!) !
Il ne me reste plus qu'à intégrer tout cela dans mon fichier boulot !

Merci encore pour le temps passé aux corrections et aussi aux explications, même si pour la partie VBA je rame encore pas mal, c'est un gros morceau (langage, ...).

Cordialement, @+

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

JoBar57

XLDnaute Junior

Pièces jointes

Bernard_XLD

JoBar57

XLDnaute Junior

Bernard_XLD

XLDnaute Barbatruc

JoBar57

XLDnaute Junior

JoBar57

XLDnaute Junior

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

JoBar57

XLDnaute Junior

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

JoBar57

XLDnaute Junior

Pièces jointes

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Pièces jointes

Bernard_XLD

XLDnaute Barbatruc

Pièces jointes

JoBar57

XLDnaute Junior

Discussions similaires