Discussion :

[clety62]

Bonjour

J'ai suivi avec intérêt le tuto pour faire un calendrier perpétuel mais est il possible d’intégrer un roulement pour 3 équipe de 3/8 qui travail une semaine de matin puis nuit puis de l’Après midi

par avance merci de votre aide

[Jean-Philippe André]

Bonjour,

je suppose que tu fais reference a ce fil :
http://www.developpez.net/forums/d70...ier-perpetuel/

Mais la notion des 3 8 m'echappe ici

[clety62]

Bonjour,

je suppose que tu fais reference a ce fil :
http://www.developpez.net/forums/d70...ier-perpetuel/

Mais la notion des 3 8 m'echappe ici

non je fais reference a ceci
http://silkyroad.developpez.com/excel/dateheure/#LV-C

pour les 3/8 ce sont une équipe qui travail du matin 5H13H une de L'apres midi 13H 21 et une de nuit 21H 5H00

par avance merci de votre réponse

[papouclo]

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 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOD (MOD(numsem-1 ; 3) + MOD($A$1;3) + SIERREUR ( EQUIV($A$1 ; anneesapres53) ; 0);3) =0

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOD (MOD(numsem-1 ; 3) + MOD($A$1;3) + SIERREUR ( EQUIV($A$1 ; anneesapres53) ; 0);3) =1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOD (MOD(numsem-1 ; 3) + MOD($A$1;3) + SIERREUR ( EQUIV($A$1 ; anneesapres53) ; 0);3) =2

Cordialement
Claude

[clety62]

Bonjour

merci beaucoup a toi de ton travail je vois que tu est bien calé en programmation.

par contre je ne vois pas comment je dois faire pour faire apparaître les postes des équipes dans le calendrier (ou alors j'ai vraiment l'air c..) ( je vais imprimé ta réponse et la lire tranquillement a tête reposer) je pense que je dois programmer les cellules en O3 a Q5???.

de plus si je peux me permettre d'abuser le roulement se fait semaine 1 de nuit (21h A 5h) puis semaine 2 de l'aprés midi (13H 21H) et semaine 3 du matin (5H 13H)

par avance merci de votre travail vous êtes génial.

[papouclo]

Bonjour,
J'ai peut-être oublié de préciser que les plages O3:Q3 , O4:Q4 , O5:Q5 correspondaient respectivement aux semaines 1 , 2 et 3.

le roulement se fait semaine 1 de nuit (21h A 5h) puis semaine 2 de l'aprés midi (13H 21H) et semaine 3 du matin (5H 13H)

C'est bien ce qui se passe ; si l'on regarde l'équipe C , elle travaille la semaine 1 de 21h à 5h (Q3) puis la semaine 2 de 13h à 21h (P4) et enfin la semaine 3 de 5 h à 13h (O5)

par contre je ne vois pas comment je dois faire pour faire apparaître les postes des équipes dans le calendrier je pense que je dois programmer les cellules en O3 a Q5???

En effet, on peut remplacer la plage N2 : Q5 par un tableau plus détaillé.
Voici un exemple où les simples noms des équipes (A, B, C) sont remplacés par le nom des équipiers postés (j'ai supposé que l'on ne voulait pas permuter les équipiers sur les différents postes).

Par contre, on ne touche pas au calendrier en A1 : M32.
Cordialement
Claude

[clety62]

Bonjour

merci de votre aide et de votre temps passé.

je n'arrive pas a voir apparaître dans mon calendrier la rotation des équipes que je nommerais par exemple i j et h je suis sincèrement désolé.

je joint un fichier pour essayer de mieux me faire comprendre de ce que je désir e voir apparaître.CALENDRIER 2017.xlsx

[clety62]

ou toute autre forme meme si on ne peux faire apparaitre qu'une seule équipe

mais je pense que c'est ou trés trés compliqué ou impossible

para avance merci a tous

[Menhir]

Pourquoi ne te contentes-tu pas de recopier les 3 colonne de droite de cet exemple dans les lignes du dessous ?
Personnellement, je ne sais pas comment t'indiquer une solution parce que je ne vois même pas où est la difficulté.

[clety62]

bonjour

je l'ai fais le calendrier pour 2017 sous excel mais ce que je desirais c'est d'intégrer le roulement des équipe dans un planning perpetuel afin de ne pas avoir a refaire tous les ans le calendrier pour chaque équipes

[papouclo]

Bonjour,
Voici une proposition reprenant en partie ce que j'avais déjà proposé pour un calendrier perpétuel avec une rotation d'équipes en 3 x 8, c'est à dire :
- travailler (avec la fonction MOD et les numéros de semaine) sur une périodicité de période 3 de la répartition des équipes sur les tranches horaires,
- utiliser la même fonction MOD sur les millésimes pour décaler d'une année sur l'autre la répartition des équipes sur les tranches horaires,
- corriger le numéro de semaine des derniers jours d'une année s'ils sont considérés comme jours de la semaine 1 de l'année suivante,
- utiliser un "décalage supplémentaire de 1" après les années pour les quelles il existe une semaine 53.

Cette proposition imite au maximum la présentation demandée dans l'exemple de clety62 (Calendrie 2017) à deux différences près :
- les dates pour un mois donné sont écrites dans une seule colonne et non deux puisque le format personnalisé jjjj j permet d'obtenir le même résultat.
- les équipes ont été ordonnées dans l'ordre alphabétique - H, I, J - (force de l'habitude).
On voit apparaître une période de 4 colonnes dans la construction du tableau : dates , H , I, J.
L'écriture de l'année traitée n'ayant pas été prévue dans une cellule dédiée (elle apparaît dans l'écriture des mois en ligne 2), elle a été prévue en cellule A2 de la feuille Paramètres et a été nommée an. Les plages nommées fériés et anneesapres53 sont désormais en D2: D11 et F2:F19.

Elaboration du calendrier annuel sur la feuille nommée 3 équipes
Ecriture des titres en ligne 2 :
Ecrire en B2 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=CHOISIR(MOD(COLONNE()-2;4)+1;DATE(an;(COLONNE()+2)/4;1);"H";"I";"J")

Demander le format personnalisé mmmm aaaa.
Recopier ensuite cette formule vers la droite sur toute la plage B2:AW2.
En se plaçant dans une cellule quelconque de la plage B3:AW33 , on définit les noms :
- cejour par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(DATE(an;(COLONNE()+2)/4;LIGNE()-2)<=FIN.MOIS(DATE(an;(COLONNE()+2)/4;1);0);DATE(an;(COLONNE()+2)/4;LIGNE()-2);"")

- numsem par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= SI((MOIS(cejour)=12)*(NO.SEMAINE(cejour;21)=1);NO.SEMAINE(cejour-7;21)+1;NO.SEMAINE(cejour;21))

- typesem par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MOD(MOD(numsem-1;3)+MOD(an;3)+SIERREUR(EQUIV(an;anneesapres53);0);3)

- numequip par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MOD(COLONNE()-3;4)

(On fait correspondre aux équipes H, I , J respectivement les nombres 0, 1, 2).
Ecriture des dates et des tranches horaires:
Ecrire en B3 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= cejour

Demander pour la cellule le format personnalisé jjjj j.
Ecrire en C3 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(SI(ESTNUM(EQUIV(cejour;fériés;0))+(JOURSEM(cejour;2)>5);"";MOD(numequip+2*typesem;3)-1);"")

Si le jour correspondant en C3 au calcul de cejour n'est pas férié ni un jour de weekend, alors MOD(numequip+2*typesem;3)-1) renvoie un des trois nombres -1, 0, 1.
Demander pour la cellule le format personnalisé "matin";"a midi";"nuit".
Recopier cette formule en D3 et E3.
Sélectionner la plage B3:E3 >>> Copier >>> Sélectionner la plage F3:AW3 >>> Coller >>> Sélectionner la plage B3:AW3 >>> Recopier vers le bas sur toute la plage B4:AW33.

Les feuilles Equipe H, Equipe I, Equipe J s'obtiennent par copie de la feuille 3 équipes en masquant ensuite dans chaque feuille les colonnes "inutiles".

Cordialement
Claude

[clety62]

Bonjour

merci beaucoup pour votre aide vous avez fait un super travail de plus vous avez anticiper ma demande vous en avez fait un commun et 3 individuel

franchement c'est super sympa.

juste une petite question (oui je sais je suis trés chiant) pour changer l'année je la change bien en A2 de la feuille paramètre?

car si je fais cela ca me change bien le calendrier mais ne me donne plus les postes des équipes (désolé)

en tout cas je vous remercie sincérement de votre aide et votre temps passé.

cordialement

[papouclo]

Bonjour,

Juste une petite question (…) pour changer l'année je la change bien en A2 de la feuille paramètre ?

Oui, Je n’ai peut-être pas été assez explicite lorsque j’ai voulu écrire la structure du fichier

Car si je fais cela ça me change bien le calendrier mais ne me donne plus les postes des équipes (désolé)

Cela veut-il dire que plus rien ne s’écrit ou que les postes (ou tranches horaires) qui apparaissent ne sont pas ceux attendus ?
J’avais cru comprendre que la rotation de ces « postes » était :

J’ai donc fait un montage de ce que renvoie le fichier pour trois années consécutives 2017, 2018, 2019 avec pour l’équipe H les tranches horaires pour la première et la dernière semaine de chaque année.

On peut vérifier que :
- fin 2017 l’équipe H est en poste le matin et qu’elle passe en poste de nuit pour la première semaine de 2018
- le dernier jour de 2018 (lundi 31) elle est en poste l’après-midi et qu’aux premiers jours ouvrés de 2019 (mercredi 2, jeudi 3 et vendredi 4) elle est toujours en poste l’après-midi mais il s’agit d’une même semaine (du lundi 31/12/18 au vendredi 4/01/19).
Il n’y a donc aucune discontinuité dans la rotation de poste avec le passage d’une année à l’autre.
Cordialement
Claude

[clety62]

Bonjour

je n'ai aucun doute sur tout cela mais je dois ou bien mal utiliser le fichier joint dans le message précedent ou je dois faire une programmation pardonnez mon ignorance

non les cases des postes sont vide

par avance merci pour tout

[clety62]

bonjour

a mon avis il me manque une formule quelque part mais je trouve pas ou

désolé

[clety62]

sur votre fichier les postes s'affichent? moi non cela veux peut être dire qu'il me manque une formule non?

[Philippe Tulliez]

Bonjour Claude,
Belles réponses superbement étayées

[papouclo]

Bonjour Philippe,
Merci pour l'appréciation.
Bonjour clety62,
Je ne vois pas comment t'aider autrement qu'en jetant un coup d’œil sur ton propre fichier s'il ne contient pas de données sensibles et si tu peux le mettre à disposition sur le forum.
Cordialement
Claude

[clety62]

Bonjour

je vous joint le fichier que j'ai car je ne comprends pas car si je change la date je ne retrouve pas les équipe

par avance merci pour tout

[riaolle]

Bonjour,
je me permets d'intervenir dans la discussion, car, récemment, j'ai du créer un planning du roulement des equipes pour mon entreprise aussi. Le problème n'est pas tout à fait le même, car nous tournons en 5/8 et pas en 3/8, mais peut-être que ça donnera des idees Nous tournons en 5/8 : 2 services du matin, 2 de l'après-midi, 2 de nuit et 4 jours de repos. Ce sont donc des cycles de 10 jours qui ne se calent pas sur le motif des 7 jours/semaines.
J'ai pris une date de référence par équipe, correspondant à une date de début de service du matin. Ensuite, j'utilise la fonction mod. Je calcule (date voulue - date référence) mod 10, si c'est égal à:

• 0 ou 1, l'équipe est de service du matin
• 2 ou 3 l'équipe est de service de l'après-midi
• 4 ou 5 l'équipe est de service de nuit
• sinon, l'équipe n'a pas de service

La clef du planning repose sur la fonction MOD !

Je vous joint le fichier avec toutes les formules. Vous remarquerez que, dans le fichier :

• j'ai pris 3 dates de références et non pas 1. Ca revient exactment au même, c'est juste que j'ai pris une date de référence pour le matin, l'après-midi et la nuit et que j'ai fait les calcules séparemment. Du coup, ça fait une très longue formule de SI imbriqués.
• les abréviations sont un peu "bizarres" : c'est du néerlandais Mais je vous ai donné la traduction!