Bonjour,
Dans le classeur ci-joint, un calendrier perpétuel est établi avec des formules légèrement différentes que l’exemple donné par clety62 et donc emprunté à SilkyRoad (en particulier la formule donnant le lundi de Pâques empruntée à d’autres sources dans la plage fériés de la feuille Paramètres).
Les jours fériés apparaissent sur fond vert et les jours de weekend (samedi, dimanche) sur fond orange. Les deux règles sont appliquées à la plage B2 : M32 ; elles doivent être les deux premières de la liste de règles dans le gestionnaire des règles de MFC et, pour les deux, la case Interrompre si vrai est cochée de façon que les règles de rotation des équipes ne s’appliquent ensuite qu’aux jours ouvrés.
Voici une proposition utilisant les MFC pour mettre en évidence sur un calendrier perpétuel la rotation de trois équipes A, B, C sur les trois plages horaires correspondant à une répartition d’une journée (24h) de travail en 3 fois 8.
Le tableau à côté du calendrier sur la plage O2 : Q5 montre que cette rotation s’effectuera bien sûr avec une période de trois semaines d’où l’idée de s’intéresser aux restes 0, 1, 2 dans les divisions des numéros ISO de semaine de chaque date par 3, ce qui s’obtient facilement avec la fonction MOD et la fonction NO.SEMAINE (date ;21) (Excel 2010) ou la fonction NO.SEMAINE.ISO (pour Excel 2013 ou 2016).
D’où une première idée de formule pour trois règles de MFC :
1
2
3
| = MOD ( NO.SEMAINE(date ;21) - 1 ;3) = 0
= MOD ( NO.SEMAINE(date ;21) - 1 ;3) = 1
= MOD ( NO.SEMAINE(date ;21) – 1 ;3) = 2 |
permettant de différencier avec des formats convenablement choisis les trois répartitions possibles des équipes A, B, C sur 24h.
Premier problème :
Les premiers jours ouvrés de n’importe quelle année sont des jours de la semaine 1 et, pour de nombreuses années, les derniers jours ouvrés sont des jours de la semaine 52 or on a :
MOD( 1 – 1 ; 3) = MOD(52 – 1 ; 3) = 0
Donc la première et la dernière semaine d’une de ces années, on aura la même répartition A, B, C (plage O2 : Q2) mais on aura aussi cette répartition en première semaine de l’année suivante d’où une rupture de la rotation d’une semaine sur l’autre au début de pratiquement toutes les années.
Une solution possible :
Combiner MOD (NO.SEMAINE(date ;21)-1 ;3) avec MOD(année ;3)
En notant x pour MOD (NO.SEMAINE(date ;21) -1 ;3) , y pour MOD(année ;3) et en notant enfin x \ y pour
MOD( MOD(NO.SEMAINE(date ;21)-1 ;3) + MOD(année ;3) ; 3) , on obtient la table suivante :
et on peut voir ainsi qu’il y a maintenant un décalage d’une année sur l’autre .
Deuxième problème :
Pour certaines années se terminant un lundi, un mardi ou un mercredi, après la semaine 52, le dernier jour ouvré (lundi 31 décembre) ou les deux derniers jours ouvrés (lundi 30 et mardi 31) ou les trois derniers jours ouvrés (lundi 29 et mardi 30 et mercredi 31) sont des jours de la semaine 1 de l’année suivante et se retrouveront de la même couleur sur le calendrier que les jours de la semaine 52.
Une solution possible :
Se placer en B2 et définir le nom numsem (ou autre) par :
=SI((MOIS(Calendrier!B2)=12)*(NO.SEMAINE(Calendrier!B2;21)=1);NO.SEMAINE(Calendrier!B2-7;21)+1;NO.SEMAINE(Calendrier!B2;21))
ce qui permettra de remplacer le numéro 1 de ces derniers jours par un numéro 53 et ainsi de changer de distribution des équipes à la fin de ces années.
Bien sûr, il faudra dans les formules des MFC précédentes remplacer NO.SEMAINE(date;21) par numsem.
Troisième problème :
Il y a des années qui ont une semaine 53 ce qui crée en fin de ces années des jours ouvrés qui auront une couleur de mise en forme qui sera la même que celle de la semaine 1 de l’année suivante puisque « l’incrémentation » prévue jusqu’ici tablait sur des années allant jusqu’à la semaine 52.
Une solution possible :
Dans la feuille Paramètres, on a écrit en colonne D à partir de la cellule D2 la liste des années suivant une année avec une semaine 53 : 2021, 2027, 2033, 2038…,2112, 2117 (en D19).
Dans le classeur joint, la plage D2 : D19 a été nommée anneesapres53.
L’ennui est que deux années consécutives sont séparées tantôt par 5 ans tantôt par 6 ans, d’où l’emploi de la fonction EQUIV pour aider à « incrémenter » le passage d’une année avec une semaine 53 à l’année suivante.
Les trois règles de FMC seront finalement :
=MOD (MOD(numsem-1 ; 3) + MOD($A$1;3) + SIERREUR ( EQUIV($A$1 ; anneesapres53) ; 0);3) =0
=MOD (MOD(numsem-1 ; 3) + MOD($A$1;3) + SIERREUR ( EQUIV($A$1 ; anneesapres53) ; 0);3) =1
=MOD (MOD(numsem-1 ; 3) + MOD($A$1;3) + SIERREUR ( EQUIV($A$1 ; anneesapres53) ; 0);3) =2
Cordialement
Claude
planning 3/8 perpétuel
Envoyé par Jean-Philippe André
Bonjour,
Répondre avec citation
Partager