XL 2021 SI OU ET - rajouter une condition supplémentaire

[JoBar57]

Bonjour,

Lorsque je planifie des vacances par exemple du 1er du mois jusqu'à la fin du mois la formule ci-dessous permet de les inscrire uniquement du lundi au vendredi, mais pas les samedi ni dimanche.
Par contre pour les jours fériés c'est possible de les inscrire sur un samedi ou un dimanche. J'aurais besoin de rajouter aussi les congés maternité qui se décomptent sur l'ensemble de la semaine, week-end inclus ainsi que les jours fériés.
j'ai essayé de reproduire ce qui avait été mis en place pour les jours fériés, mais cela ne fonctionne pas. Il y a toujours une erreur. Comme la formule commence à être un peu trop complexe pour moi, est-ce que quelqu'un pourrait me dépanner ? Est-ce qu'il est possible de rajouter cette modification dans la formule actuelle ou bien faut-il la remplacer ?

=SI(OU(ET(D28<7;D28>1);SIERREUR(INDEX(Tableau1;EQUIV(1;(Planning!D25>=Tableau1[Date début])*(Tableau1[Date de Fin]>=Planning!D25)*(Tableau1[Personnel]=Planning!$V$2);0);5);"")="F");SIERREUR(INDEX(Tableau1;EQUIV(1;(Planning!D25>=Tableau1[Date début])*(Tableau1[Date de Fin]>=Planning!D25)*(Tableau1[Personnel]=Planning!$V$2);0);5);"");"")

C'est la formule qui se trouve dans l'onglet "Planning" dans les lignes 19-24-29-34-39-44-49-54-59-64-69 et 74
Dans le fichier joint on peut constater que le résultat "Mat" ne s'est pas inscrit sur les week-ends du mois de mai.

Merci

 

[vgendron]

Hello

Je crois que j'ai enfin réussi à m'en sortir avec la fonction "TJF" que j'ai purgé de tous les pays et régions hors Suisse-Genève

j'ai transformé pour remplacer la fonction par une procédure qui n'est lancée qu'UNE seule fois lorsque l'année du planning est changée
les jours fériés sont mis dans la table "t_JoursFériés" avec le jour et l'intitulé
4 colonnes sont masquées pour pouvoir alimenter le second petit tableau de récap
et les 3 formules dessous

comme la fonction est appelée par une MFC, j'ai volontairement laissé le code originel tel que défini par BernardXld

 

[JoBar57]

Hello

Je crois que j'ai enfin réussi à m'en sortir avec la fonction "TJF" que j'ai purgé de tous les pays et régions hors Suisse-Genève

4 colonnes sont masquées pour pouvoir alimenter le second petit tableau de récap
et les 3 formules dessous

j'ai transformé pour remplacer la fonction par une procédure qui n'est lancée qu'UNE seule fois lorsque l'année du planning est changée
les jours fériés sont mis dans la table "t_JoursFériés" avec le jour et l'intitulé

comme la fonction est appelée par une MFC, j'ai volontairement laissé le code originel tel que défini par BernardXld

vraiment super ton boulot de restructuration, c'est super clean à coté de mon "bordel", par contre il semble qu'il y ai un problème avec les tableaux "Calcul des V" car en (n-3) il commence le 13/4/2020 au-lieu du 10/04/2020, il manque un jour et du coup en année (n) il n'y a que 8 jours sur les 9 attendus car toutes les dates sont décalées.
Pourtant dans le tableau t-Fériés le 10/04/2020 figure bien.

Super l'idée du cadre de texte semi transparent pour protéger les formules d'une modif accidentelle !
Comment tu rends le cadre semi transparent ?
(Pour l'instant je n'ai fait qu'un copier-coller du tiens pour le mettre sur le tableau de calcul du nbres de jours fériés)
J'ai corrigé la cellule E10 aussi.

4 colonnes sont masquées pour pouvoir alimenter le second petit tableau de récap
et les 3 formules dessous

Dans les 4 colonnes j'ai rajouté un "=" à la 1ère condition (>=$S$G16) afin d'inclure le 1er jour du mois (correcte ?)
Quant aux 3 formules en dessous du tableau, dans la configuration actuelle il n'y en a à priori pas besoin (sauf si j'ai loupé quelque chose), aussi je les ai supprimées.

 

[vgendron]

Hello

Effectivement pour le décalage des date, il y avait une erreur dans la formule:
je faisais un -3 au lieu d'un -4 ==> Corrigé
ET j'ai aussi corrigé le >= (bizarre, tu disais avoir corrigé, mais je ne le vois pas.. peut etre as tu oublié d'enregistrer avant d'envoyer, mais l'idée était bien la bonne


pour les 3 formules: il me semblait que tu utilisais le résultat dans tes calculs feuille Planning
D'ailleurs attention j'avais donné des noms à ces trois cellules
dans la PJ, j'ai remis des valeurs bidon en jaune==> on voit bien que le NbRFé (-100) est utilisé dans planning (E9)
comme il y a quelques cellules nommées, j'ai noté à coté de chaque cellule concernée son nom
par exemple: ('==>Reste_Fé2)
comme ca, tu vois où elles sont

pour le cadre semi transparent.. tu cliques droit sur le cadre, et dans les propriétés tu cherches la case "Transparence" que tu peux définir entre 0 et 100
mais c'est juste un artifice pas vraiment efficace.. il suffit de déplacer le cadre pour faire ce qu'on veut..
si vraiment tu veux éviter que quelqu'un modifie les formules ou autre, il vaudrait mieux protéger la feuille avec mot de passe (onglet Révision / Protéger la feuille ==> tu tapes un mot de passe, et dans la liste, tu peux choisir un certain nombre d'action que tu autorises quand meme)

 

[JoBar57]

Hello,

Merci pour les précisons, comme ça plus facile pour moi de s'y retrouver.

mais c'est juste un artifice pas vraiment efficace.

C'est suffisant en tous cas pour éviter un clic malencontreux

dans la PJ, j'ai remis des valeurs bidon en jaune==> on voit bien que le NbRFé (-100) est utilisé dans planning (E9)

Dans E9, j'ai remplacé "NbRFé2" par la cellule "E6" qui vient de toute façon de "NbFé" donc pas besoin de multiplier les calculs (... tu vois la leçon commence à rentrer )
donc en principe il n'y aurait plus besoin de NbRFé2, NbRFé1 et NbRFé puisque qu'on peut trouver ces chiffres dans la ligne 18 du tableau de "Calcul des "F" selon les années" dans les cellules NbFé à NbFé3

Reste maintenant ma demande de départ, permettre à "Fé" et "Mat" de s'inscrire sur les WE.

....... Et j'ai failli louper ça, mais si la personne est en congés "Mat" alors elle sera inscrite "Mat" sur tous les jours du mois et du coup mon décompte des jours fériés non travaillés sera faussé.
Exemple dans le planning pour "A" sur le mois de mai 2023 les 18 et 23 sont notés "Mat" et ne pourrons pas aussi être comptabilisés comme étant des fériés non travaillés alors que cela devrait être la cas puisque "A" n'était pas au travail ces 2 jours.
Comment peut-on compter les jours fériés des congés Mat aussi dans la rubrique des jours fériés non travaillés ?
=> Rajouter une condition supplémentaire : si un jour férié est noté "Mat" on comptabilise Mat et Fé ?
ça c'est la théorie, en pratique aucune idée de comment intégrer cela dans ta formule

Voir dans le fichier joint

 

[vgendron]

Hello
à mon avis, ca risque de donner des formules à rallonge illisibles...
il faudrait sans doute passer par du VBA qui remplit le planning automatiquemnet
==> plus de formule==> plus fichier plus léger
et on peut ainsi coder toutes les conditions qu'on veut

 

[vgendron]

voici une V5 qui se passe des formules dans les différents tablo mensuels du planning
tout est rempli par une macro qui est déclenchée dès que
- la feuille Planning est affichée
- l'année ou l'employé changent

reste juste à remplir les colonnes de totaux
==> en mai== on va donc retrouver les 31 jours de Mat + 2 Jours fériés (= les deux journées colorées par la MFC qui utilise la fonction TJF)

mais pour le mois d'avril...
il y a aussi deux Jours féris (07 et 10 avril) colorés par MFC
mais il y a aussi un Fé dans la ligne "congés" du tablo..
==> Bon.. ici, ca tombe un jour déjà coloré==> on va dire qu'on ne le compte pas (mais pourquoi le 07, il n'y a pas de Fé

et que se passe t il, si il y a un Fé sur un jour NON férié (= non coloré??)==> on compte quoi?

 

[vgendron]

Hello
Et voila !

en PJ,
le planning est mis à jour avec calculs des totaux
regarde le code: j'y ai mis des commentaires pour expliquer.

 

[JoBar57]

Hello,

Super boulot d'allègement supplémentaire !
Merci pour ta disponibilité et ta ténacité à résoudre les problèmes structurels de ce classeur !

mais pourquoi le 07, il n'y a pas de Fé

Les "Fé" sont programmés si la personne n'a pas travaillé ce jour là et a donc bénéficié de son férié. Donc si le 7 avril il n'y a rien de noté c'est parce que la personne a travaillé et n'a donc pas bénéficié de son jour férié.
=> du coup en avril il ne doit y avoir que le jour férié marqué "Vé" qui doit être comptabilisé et non les deux comme c'est le cas actuellement.

et que se passe t il, si il y a un Fé sur un jour NON férié (= non coloré??)==> on compte quoi?

-/ Les "Fé" ne doivent apparaître que sur les différents jours fériés, donc en principe ça ne doit pas arriver (sauf erreur de programmation) car dans ce cas ce sera un "RFé" cad un jour férié travaillé qui est récupéré à un autre moment, le but étant de savoir si la personne a bénéficié de tous les fériés de l'année, soit les jours en question soit en récupération un autre jour, voire l'année d'après (RFé-1 ...).

-/ Les "Va" ne peuvent pas s'inscrire sur un jour férié, car si la personne ne travaille pas ce jour là, même si c'est en plein milieu d'une série de "Va" il faut comptabiliser ce jour là comme un férié dont elle a bénéficié et on ne décomptera pas ce jour là du nbre de jours de vacances encore disponibles

-/ L'exception étant comme vu précédemment le congé "Mat", qui s'il tombe sur un jour férié, doit être comptabilisé doublement (Mat + Fé)

-/ Il semble y avoir un soucis dans la chronologie des dates du planning : on se retrouve avec décembre 2022 après novembre 2023, puis on repart avec janvier 2023 au lieu de 2024

Et zut, pour la modif de l'onglet planning je voulais rajouter une colonne à gauche afin de pouvoir escamoter l'actuelle colonne C et faire disparaître les dates et chiffres de l'affichage mais je n'avais pas anticipé le passage en VBA, du coup j'aurais dû le faire plus tôt car maintenant ce n'est plus possible sans revoir toute la macro !
=>Je trouverai un autre moyen

 

[vgendron]

Re

pour etre sur d'avoir bien compris
==>on compte les Fé de la ligne 1 (colorés par MFC) UNIQUEMENT SI il y a MAT en ligne 4
==>si pas de Mat, on compte on ne compte que les Fé de la ligne 4 ..
correct?

pour le problème des mois qui ne se suivent pas, j'ai corrigé

pour la colonne que tu voulais ajouter, pas de problème, vas y, ajoute la: il n'y a que 2 ou lignes de code à modifier.. c'est pas grand chose
celle où on copie colle les tableaux mensuels: .cells(....,4)=... ==> il faut juste modifer le 4 (= colonne D)
et celles où on fait les calculs du solde général
avec les =.range("K10").. qui deviendrait .range("I10")...

profites en tant que j'ai encore du temps pour t'aider.. demain c'est fini, je serai absent pour plusieurs mois pour un nouveau boulot

 

[JoBar57]

Re

==>on compte les Fé de la ligne 1 (colorés par MFC) UNIQUEMENT SI il y a MAT en ligne 4
==>si pas de Mat, on compte on ne compte que les Fé de la ligne 4 ..
correct?

oui, correct, on ne les compte que s'il y a Fé ou Mat en ligne 4

Ci-joint le fichier avec la colonne rajoutée à gauche du planning et les colonnes B,C,D masquées.
Du coup ça bug car rien n'est plus à sa place

Et bonne chance avec ton nouveau boulot et beaucoup de plaisir j'espère.

 

[vgendron]

et voila la version rectifiée

attention, j'ai noté une formule qui me semble erronée: En L8: la formule va chercher quelque chose en AQ7 (cellule qui est totalement hors tablo ou zone de calcul..??

 

[JoBar57]

Merci beaucoup pour ton aide qui a été bien au-delà de ma demande initiale mais qui a été plus que bienvenue.

Concernant L8, je pense que ça venait peut-être de mes déplacements successifs des différentes cases (copier-coller) en vue de réorganiser le "foutoir" du début. Quelque chose a dû louper.
En tous cas c'est rectifié, c'était juste L6 au lieu de AQ7.
J'ai corrigé aussi dans la macro au niveau des cellules utilisées pour calculer le total des jours de congé les n-3 étaient pris sur la ligne 12 au lieu de 10 comme pour n, n-1 et n-2

Et ça fonctionne nickel

Pour mon info perso, si je veux ajouter par la suite, exemple en colonne "AU" à droite du planning, une rubrique supplémentaire (exemple "M" pour comptabiliser les absences maladie et qui ne s'inscrivent pas sur les WE) que dois-je rajouter dans la macro et où :
à part ReDim tabMois (1 to 43) au lieu de 42
et TabMois(3, 42) = "M" ?
(je n'ai encore pas tout décortiqué)

En attendant, je te souhaite de réussir ton changement professionnel et peut-être à (beaucoup) plus tard sur ce forum.