Formule pour trouver deux périodes différentes sur même lign
Bonsoir
Voici mon tableau calendrier ou j'essaie de trouver une formule qui me permette de repérer deux dates de début et de fin de périodes distinctes sur une même ligne.
Pas évident pour ma part
Merci pour les idées
Cdt
NB
Bonsoir
Wouah , c'est super ! Merci infiniment.
J'essaie à présent de dissequer le mécanisme et ne désèspere pas de comprendre quelques subtilités. C'est vraiment génial excel quand on connait
J'aimerais dans la mesure du possible ajouter une colonne pour compter le nombre de "a " dans la période (date de départ/ date de fin).
Je l'ai ajouté mais je n'arrive pas à trouver les bons termes excel...
En tous les cas encore tous mes remerciements
Cordialement
Berlingo
Bonjour
Super, merci beaucoup. C'est génial.
Par contre j'ai remis les différentes formules sur mon tableau et j'ai un bug au niveau des fin de mois. La date du 31 n'est pas pris en compte. J'essaie de comprendre pourquoi , mais je ne trouve pas.
Je joins mon tableau.
Enfin je cherche un moyen de m'alerter lorsque j'ai 35 jours <>0.
Cdt
Berlingo
Bonjour
J'ai inséré une colonne AG avec des 0 sur chaque ligne et changé les formules.
Le réglage avec DECALER, n'est pas évident;
Teste et dis-moi.
Enfin je cherche un moyen de m'alerter lorsque j'ai 35 jours <>0.
En DF4
=NB.SI($B$7:$AF$18;"<>0")qui donne le total des cellules <>0
Quel genre d'alerte?
Cordialement
Bonsoir
Merci une nouvelle fois pour la réponse, super sympa.
Je vais tester tout ça.... dans quelques instants.
Par contre pour l'alerte j'ai écrit un peu trop vite, désolé, car j'a oublié d'indiquer que c'était 35 jours consécutifs d'où là la difficulté car obligatoirement ça sera à cheval sur deux mois.
Cdt
NB
Bonjour
Le total des cellules <>0 des 35 derniers jours (par rapport à la fin de période) pourrait-être donné par une formule du type (Ici en DA8)
=SI(MAX(DD8;DJ8)=0;0;NB.SI(INDIRECT("$B"&LIGNE()&":"&ADRESSE(LIGNE();MAX(DD8;DJ8)+1));"<>0")+NB.SI(INDIRECT(ADRESSE(LIGNE()-1;MAX(DD8;DJ8)-2)&":AF"&LIGNE()-1);"<>0"))Petit bémol, cette formule raisonne comme si tous les mois avaient 31 jours.
Il faudrait la complexifier encore pour l'adapter au calendrier, en connaissant bien sur l'année pour février.
Cordialement
Bonjour
Merci un nouvelle fois pour la réponse. C'est vrai que le problème va venir pour les mois de 30 jours et du mois de février. Sur mon tableau original j'ai cependant en AK 2 l'année en cours qui s'affiche. Je ne sais pas si cela peut aider à résoudre une partie du problème.
En attendant, je vais regarder le dernier tableau reçu ....et merci encore
Cdt
NB
Bonjour
Pour travailler avec les dates et pour rendre la formule opérationnelle.
- J'ai corrigé l'orthographe des mois en colonne A, en rajoutant les accents manquants.
La formule en DA8, incrémentée vers le bas qui donne le nombre de jours (et pas de cellules) différents de 0 est:
=SI(MAX(DD8;DJ8)=0;0;NB.SI(INDIRECT("$B"&LIGNE()&":"&ADRESSE(LIGNE();MAX(DD8;DJ8)+1));"<>0")+NB.SI(INDIRECT(ADRESSE(LIGNE()-1;MAX(DD8;DJ8)-3+(JOUR(DATE($B$5;MOIS("1/"&A7)+1;0)))-31)&":AF"&LIGNE()-1);"<>0"))Cordialement
Bonjour
Merci encore pour ce nouveau retour. Il est vrai que ce n'est pas facile à mettre en place cette alerte.
Je viens de regarder et j'avoue que je ne comprends pas très bien cette nouvelle formule. En effet, en DA9 j'ai 14 ce qui est normal mais en DA 15 j'ai le chiffre 22 et je ne saisis pas à quoi il correspond. La cellule devrait indiquer 14.
Mon but recherché, c'est d'avoir une alerte quand sur deux mois (ou trois) j'ai 35 jours de congés consécutifs.
J'ai essayé de remplir deux mois complets en sigles congés et rien ne s'est passé.
Cordialement
NB
Bonjour
Les explications demandées:
DA 14 indique 21 (au lieu de 22 précédemment, petit réglage de formule)
21! Pourquoi?
Du 1er au 4 Août, 4 cellules sont occupées (<>0)
sur 35 jours à partir du 4 Août, en remontant, juillet ayant 31 jours, nous tombons sur le 1er juillet et du premier juillet au 4 août, nous avons 35 jours et 21 jours renseignés.
Sur ce fichier, pas sur le précédent, une MFC passe les cellules de la colonne DA en rouge dès que le seuil de 35 jours renseignés est atteint.
La formule en DA14
=SI(MAX(DD14;DJ14)=0;0;NB.SI(INDIRECT("$B"&LIGNE()&":"&ADRESSE(LIGNE();MAX(DD14;DJ14)+1));"<>0")+NB.SI(INDIRECT(ADRESSE(LIGNE()-1;MAX(DD14;DJ14)-2+(JOUR(DATE($B$5;MOIS("1/"&A13)+1;0)))-31)&":AF"&LIGNE()-1);"<>0"))On teste d'abord si nous avons un jour de départ sur la plage DD14:DJ14.
Si oui, la suite de la formule compte les cellules renseignées de la ligne 14 avec NB.SI et le dernier NB.SI compte celles du mois précédent pour arriver à un total de 35 jours.
Pour cela, la formule
=JOUR(DATE($B$5;MOIS("1/"&A13)+1;0)donne le dernier jour du mois précédent, qui est juillet, donc 31
et ôte 31 (nombre de colonnes nécessaire pour le décalage)
Peut-être compliqué à expliquer et comprendre...
Cordialement
Bonjour
Merci pour ces nouvelles explications. J'arrive à saisir le fonctionnement, mais jamais je n'aurais pu le traduire tout celà en langage excel. Vraiment super, merci.
J'ai testé le tableau.
- J'ai fait un essai avec février et mars j'ai mis 40 jours consécutifs, mais rien ne se passe
- En juillet j'ai 31 cellules renseignées puis 9 en août, mais là rien ne se passe alors que j'ai aussi 40 jours consécutifs ?
- en DA j'ai mis la formule qui était absente et j'ai une erreur
Je joins le fichier qui est plus explicatif.
Belle après midi
NB
Bonjour
- J'ai fait un essai avec février et mars j'ai mis 40 jours consécutifs, mais rien ne se passe
Le calcul se fait à partir de la dernière saisie sur le mois et comme le 31 mars est renseigné, le retour ne se fait que jusqu'au 26 février, soit 17 jours renseignés.
- En juillet j'ai 31 cellules renseignées puis 9 en août, mais là rien ne se passe alors que j'ai aussi 40 jours consécutifs ?
Même raisonnement sur août.
- en DA j'ai mis la formule qui était absente et j'ai une erreur
La formule est absente parce que le tableau n'inclut pas décembre précédent et donc, le calcul ne peut se faire que sur 31 jours et non sur les 35 demandés.
Si tu veux calculer sur 2 périodes, voir le fichier joint
Cordialement
Bonjour
Je crois que ma demande pour les 35 est vraiment trop compliqué à mettre en place. Sincèrement désolé.
Je viens de tester à nouveau le tableau en incluant une possibilité de périodes, les 2 derniers jours d'août renseignés, les 30 de septembre et les 4 premiers d' octobre , ce qui fait 36. Mais rien ne se passe, j'ai une erreur.
Je joins le fichier modèle
Bonne journée
Cordialement
NB
Bonjour
en effet, je comprends que tu laisses tomber.
D'autant plus que nous sommes partis sur 2 périodes et que pour certains mois, j'en vois aujourd'hui 3. Ce qui est incohérent avec les formules prévues pour 2!
Très belle journée à toi aussi.
Cordialement
Bonjour
Je viens de penser à une idée. j'ai créé en DQ 7 une ligne qui compte les 366 jours de l'année en liaison avec le tableau renseigné.
Il suffirait de mettre une formule en DP 7 qui devient rouge si sur la ligne, on repere 35 cellules qui sont <>0 et sont consécutives.
Surement plus simple, non ?
Cordialement
NB
Bonjour
Une méthode
Comme l'antériorité sur 35 jours n'est atteinte que le 4 février,
Formule en EY4 à incrémenter vers la droite
=SI(NB.SI(DECALER(EY6;;-34;1;35);"<>0")>=35;"Alerte";"")La première alerte est au 6 mars
Cordialmement