XL 2010 Formule mise en forme conditionnelle

[Calypso50]

Bonjour a tous, je suis nouvelle sur ce forum que j'apprécie beaucoup, alors peut être trouverais je une solution a mon problème de formule ? Cela fais plusieurs jours que je suis sur mon planning. J'ai fais comme la plupart d'entre vous a aller chercher sur différents sites afin de trouver LA SOLUTION, mais je ne trouve pas exactement ce que je cherche. Et oui pourquoi faire simple quand on peut faire compliquer. En gros, je souhaiterais faire une mise en forme conditionnelle qui prendrait en compte une heure et une date(style agenda par semaine), il en va de même pour mon planning mois, lorsque je fais ma mise en forme conditionnelle qui part d'une date à une autre date ( exemple le 03/10/2016 au 04/11/2016), ma mise en forme conditionnelle continue sur le mois de décembre alors qu'elle devrait s'arrêter au 04/11/2016. Ce qui est dingue, c'est que sur le mois de novembre, elle s'arrête correctement sur certaines lignes.
Je souhaiterais également, mettre un jeu d'icone, dans ma colonne G, ceci afin de savoir ou j'en suis dans la progression par rapport à la date de début et la date de fin. Par avance, je vous remercie de votre aide. Calypso

 

[TooFatBoy]

Bonjour,

Essaye ceci :
=ET(H$6<>"";H$6>=$B11;H$6<=$C11)

 

[Calypso50]

Bonjour Marcel 32

Tout d'abord je tiens a te remercier d'avoir répondu à mon message. J'ai essayé ta formule, le souci c'est qu'elle ne marche sur a peu près les 10 premières lignes de mon planning et après ça roule tout seul. Pourquoi ?

 

[TooFatBoy]

Bizarre, chez moi ça semble marcher sur l'ensemble du tableau que tu as fourni.

Essaye ceci :
- sélectionne la cellule H11,
- ajoute la MFC en mettant la formule que j'ai indiquée plus haut,
- dans le champ "S'applique à", tu mets $H$11:$AL$55

 

[TooFatBoy]

Au fait... : bien vérifier que toutes les cellules des colonnes B et C, contiennent bien des dates et non du texte ayant l'apparence d'une date... car c'est en fait ça ton problème.
(voir par exemple la cellule C11)

 

[Calypso50]

Bonjour ou Bonsoir Marcel, je vois que vous êtes comme moi un peu insomniaque.
J'ai fais comme vous me l'avez dit, malheureusement cela ne marche toujours pas. Après je me suis faite la réflexion de savoir si cela pouvait être dû a la version Excel. En effet, je croyais que j'avais la version 2010, mais non c'est la version 2016. Et a mon taff, j'ai la version 2007, hier lorsque j'ai travaillé sur mon tableau afin de mettre en forme mon jeu d'icone pour la colonne G, j'ai du cogité pour trouver la formule qui me permettrait de faire la différence entre deux dates.
Je sais que je te demande beaucoup, mais as tu réfléchi à la formule concernant mon autre question, a savoir "quelle est la MFC afin que tel jour à tel heure les cases soient colorées en fonction de ma semaine. Pour cela, je te joins l'autre tableau pour que tu puisses voir ce que je cherche a faire. Je te remercie encore une fois de ton aide. Calypso

 

[TooFatBoy]

Dans le tableau du fichier "copieClasseur1.xlsx", c'est le mois de novembre 2017.
Est-ce normal ? Ne devrait-ce pas être le mois de novembre 2016 ???

D'autre part, la cellule C11 pose toujours problème : elle ne semble pas être reconnue en tant que date.
Va donc dans la cellule C11 et saisi de nouveau la date, tu verras que la ligne prendra la bonne coloration (si tu as d'abord changé le mois de novembre 2017 en novembre 2016, bien sûr ).

Je ne comprends pas bien l'intérêt de ce que tu as fait en colonne G : tu comptes simplement le nombre de jours entre les deux dates des colonnes B et C, et selon s'il y a moins de 15, entre 15 et 30, ou plus de 30 jours, alors tu changes la couleur du point.

 

[Calypso50]

Bonsoir Marcel,

J'ai trouvé par rapport à la cellule C11, c'était pas la même écriture de police, du coup ça marche. Merci pour cela.
Après l'intérêt de ma colonne G en faisant la différence de date je voudrais que si la date de fin est passée par rapport au mois, je puisse avoir (un icône vert, pour dire que c'est terminé pour le mois en cours), (un orange pour dire que la date arrive à échéance) ,et (un rouge pour dire que c'est toujours en cours).

 

[TooFatBoy]

Concernant la cellule C11, et ce n'est pas la seule cellule dans ce cas, j'ai bien vu que ce n'est pas la même police de caractères que la cellule C25 par exemple.
Toutefois, le problème ne vient pas de la police de caractères mais du fait que le contenu de cette cellule, et de plein d'autres cellules dans la colonne C, n'est pas considéré par Excel comme étant une date mais simplement du texte.

Pour corriger ça il faut saisir de nouveau toutes les dates posant problème.
Ou alors tu changes la formule de la MFC en la remplaçant par ceci :
=ET(H$6<>"";H$6>=SIERREUR(DATEVAL($B11);$B11);H$6<=SIERREUR(DATEVAL($C11);$C11))
Mais ça ne changera pas le fait que pour Excel certaines cellules de la colonne C contiennent du texte et non une date, ce qui risque de poser des problèmes par la suite.

Après l'intérêt de ma colonne G en faisant la différence de date je voudrais que si la date de fin est passée par rapport au mois, je puisse avoir (une icône verte, pour dire que c'est terminé pour le mois en cours), (une orange pour dire que la date arrive à échéance), et (une rouge pour dire que c'est toujours en cours).

Même si je ne comprends pas bien ce que veut dire "si la date de fin est passée par rapport au mois", je me doutais bien que c'était quelque chose dans ce genre, mais ça n'a pas de rapport avec le nombre de jours de la formation comme tu l'as fait.

Est-ce que le point doit changer de couleur en fonction des dates de début et de fin de la formation ainsi que de la date d'aujourd'hui ?

 

[TooFatBoy]

Pour la mise en forme de ton tableau, je te propose différentes petites choses ci-dessous.


Je te propose de mettre en cellule AJ6 la formule suivante :
=SIERREUR(SI(JOUR(AI$6+1)=1;"";AI$6+1);"")
En suite, copier la cellule AJ6, puis la coller en AK6 et AL6.
Ainsi ça marchera aussi pour le mois de février, qu'il comporte 28 ou 29 jours.

Bien sûr, en AJ7 tu mets la formule suivante :
=SIERREUR(JOUR(AJ$6);"")
à copier, puis coller en AK7 et AL7.

Et en AJ8 tu mets la formule suivante :
=SIERREUR(CHOISIR(JOURSEM(AJ$6);"D";"L";"M";"M";"J";"V";"S");"")
à copier, puis coller en AK8 et AL8.



Je te propose également de changer la formule de la cellule H9 comme ceci :
="S "&NO.SEMAINE(H$6;21)
Ainsi tu auras toujours le numéro de semaine du premier jour du mois, que ce soit un lundi ou pas.

Tu sembles utiliser la convention selon laquelle une semaine commence le lundi et non le dimanche, donc il faut ajouter un paramètre à la fonction "NO.SEMAINE".
Ce paramètre peut dans ce cas être soit 2, soit 11, soit 21.
Je ne sais pas la différence qu'il y a entre 2 et 11, mais perso je préfère 21 qui évite d'avoir deux semaines n° 1 en début d'année...
En effet, avec la valeur 21 le premier jour de l'année peut alors faire partie de la 51ème ou 52ème semaine de l'année précédente. Alors que si tu utilises la valeur 2, le premier jour de l'année peut faire réellement partie de la 52ème semaine de l'année mais afficher qu'il fait partie de la première semaine...
Essaye avec le mois de janvier 2017, tu comprendras tout de suite.

Si ça te convient, tu peux modifier la formule en cellule I9 :
=SI(I$8="L";"S "&NO.SEMAINE(I$6;21);"")
à copier et coller de J9 à AL9.



Une fois que tu as fais ça, si tu affiches le mois de février 2017, tu dois avoir les trois dernières colonnes qui apparaissent toujours mais qui sont vides.
Je te propose donc d'essayer un truc.
Va sur la cellule AJ6 et ajoute une MFC comme ceci :
- formule de la MFC : =AJ$6=""
- Format : dans l'onglet "Bordure", clique (deux fois s'il le faut) sur la bordure du haut pour qu'elle disparaisse, et fais de même pour la bordure du bas et celle de droite
- s'applique à : =$AJ$6:$AL$55
Tu valides tout ça et ça doit être bon : les colonnes vides de fin de tableau doivent avoir "disparu".

Si en plus tu vas dans l'onglet "Affichage" et que tu décoches "Quadrillage", ça doit être encore plus joli.



Si cette "clarification" du mois par "disparition" des colonnes superflues t'as plu, je te propose d'utiliser le même principe pour mieux mettre en valeur les séparations entre les semaines.
Va sur la cellule I9 (et non pas H9) et ajoute une MFC comme ceci :
- Formule : =ET(I$8<>"";I$8<>"L")
- Format : dans l'onglet "Bordure", clique (deux fois s'il le faut) sur la bordure de gauche pour qu'elle disparaisse, mais ne touche surtout aux autres bordures.
- s'applique à : =$I$9:$AL$9
Tu valides tout ça et tu dois alors mieux voir les semaines.

 

[TooFatBoy]

Juste une remarque : tout le contenu du message précédent n'est pas indispensable, c'est juste pour s'amuser à peaufiner l'aspect visuel du tableau.

 

[Calypso50]

Bonjour Marcel,

Merci pour toutes tes réponses, pour l'instant, j'ai fais la correction pour la cellule 11, je comprends ce que tu veux dire lorsque tu dis que ce n'est pas une date mais du texte, du coup en réfléchissant, je me dit normal puisqu'en fonction des mois à venir c'est moi qui entrera les dates donc forcément du texte.
Pour répondre ta question, est-ce que le point doit changer de couleur en fonction des dates de début et de fin de la formation ainsi que de la date d'aujourd'hui ? je souhaiterais que lorsque la date de fin est arrivée , mon icône passe en vert pour dire que c'est terminé. , faut il faire par rapport à la différence de date? , faut il faire une moyenne ?, ou faut il faire un pourcentage ? Toutes bonnes idées et bonnes à prendre.
Je n'ai pas encore eu le temps pour mon deuxième tableau. Je te tiens au jus.
Je te remercie encore mille fois de m'aider pour mettre en forme mes tableaux, mille fois encore merci pour ta patience concernant mon manque de logique pour les formules.

 

[TooFatBoy]

Merci pour toutes tes réponses, pour l'instant, j'ai fais la correction pour la cellule 11, je comprends ce que tu veux dire lorsque tu dis que ce n'est pas une date mais du texte, du coup en réfléchissant, je me dit normal puisqu'en fonction des mois à venir c'est moi qui entrera les dates donc forcément du texte.

Non : si tu définis le format des cellules des colonnes B et C comme étant des dates, la saisie que tu feras au clavier donnera bien une date dans la cellule et non du texte.
En revanche, si tu remplis tes cellules par un copier/coller, alors tu peux (selon les cas) obtenir du texte (au lieu d'une date) dans les cellules.

Visuellement c'est pareil (ou presque), mais pour Excel c'est totalement différent car tu peux effectuer des calculs sur une date mais cette même formule ne fonctionnera pas si c'est du texte.

Dans Excel, toujours bien faire attention au format des cellules car une cellule peut contenir quelque chose et afficher autre chose. Par exemple une cellule peut contenir le nombre 42705 et afficher "42075" ou afficher "01/12/2016" ou encore afficher "jeudi 1 décembre 2016".
Le format des cellules, c'est le plus important dans ta feuille de calcul !

Pour répondre ta question, est-ce que le point doit changer de couleur en fonction des dates de début et de fin de la formation ainsi que de la date d'aujourd'hui ? je souhaiterais que lorsque la date de fin est arrivée , mon icône passe en vert pour dire que c'est terminé. , faut il faire par rapport à la différence de date? , faut il faire une moyenne ?, ou faut il faire un pourcentage ? Toutes bonnes idées et bonnes à prendre.

Normalement il n'y a rien de si compliqué que des pourcentages.
Il devrait suffire de comparer à aujourd'hui je suppose. Mais ce que tu veux exactement obtenir n'est vraiment pas clair pour moi.

Quand tu dis "lorsque la date de fin est arrivée", est-ce que ça veut dire (comme je le crois) que la date de fin est antérieure à la date d'aujourd'hui ? Auquel cas on ne tient aucunement compte du mois qui est affiché, mais uniquement de la date dans la colonne C et de la date d'aujourd'hui.
Exemple : si le contenu de la cellule C est strictement inférieur à la date d'aujourd'hui, alors ça veut dire que la formation est terminée et du coup on passe la boule en vert.

Et encore, ça c'est le plus simple... Parce que sinon, quand exactement la boule doit-elle être rouge ??? Est-ce quand la formation n'a pas encore commencé ? Auquel cas on ne tient aucunement compte du mois qui est affiché, mais uniquement de la date dans la colonne B et de la date d'aujourd'hui.
Exemple : si le contenu de la cellule B est strictement supérieur à la date d'aujourd'hui, alors ça veut dire que la formation n'a pas encore commencé et du coup on passe la boule en rouge.

Et enfin, quand exactement la boule doit-elle être orange ??? Est-ce quand la formation est en cours ? Auquel cas on ne tient aucunement compte du mois qui est affiché, mais uniquement des dates dans les colonnes B et C, et de la date d'aujourd'hui.
Exemple : si le contenu de la cellule B est inférieur ou égal à la date d'aujourd'hui et que le contenu de la cellule C est supérieur ou égal à la date d'aujourd'hui, alors ça veut dire que la formation est en cours et du coup on passe la boule en orange.

Est-ce cela ? Si oui, ben tu as les explications, il ne te reste plus qu'à mettre les formules dans ta (triple) MFC.

Je n'ai pas encore eu le temps pour mon deuxième tableau. Je te tiens au jus.

Je n'ai pas compris ce que tu veux faire avec le deuxième tableau (deuxième fichier). Mais on n'en est pas encore là...

 

[Calypso50]

Bonjour Marcel,

J'espère que tu vas bien et que je te prends pas trop la tête avec mes tableaux ? Pour répondre a tes questions concernant mon jeux d'icone, je pencherais plus sur ta dernière solution. C'est a dire que la boule doit passée en rouge quand la formation est toujours en cours , en vert quand la formation est terminé et en orange quand la formation arrive a échéance..
Après je me pose la question de savoir, peut on faire un réalisé pour la colonne G, je m'explique, si je part de la colonne B par rapport a ma date d'aujourd'hui, je suis à tant de ma date de fin ? Est ce que cela serait plus judicieux ?
Je m'inquiète pas on va finir par se comprendre. Je te souhaite de passer une excellente journée. . Caroline

 

[TooFatBoy]

C'est-à-dire que la boule doit passer en rouge quand la formation est en cours, en vert quand la formation est terminée et en orange quand la formation arrive à échéance..

Pardon, mais pour moi ça reste flou...
Il faudrait que tu précises ce que tu veux dire par "arrive à échéance" : est-ce "arrive bientôt à échéance", ou est-ce "est arrivée à échéance" ?

Si c'est "arrive bientôt à échéance", que représente la notion de "bientôt" ? Est-ce un nombre de jours constent (dans 2 jours, par exemples), est-ce un pourcentage par rapport au nombre de jours de la formation (10% = dans 1 jour pour une formation de 10 jours, dans 6 jours pour une formation de deux mois, etc.) ?

Et quand la formation n'a pas encore commencé, elle est comment cette boule ? noire ? invisible ? autre ?

Après je me pose la question de savoir, peut-on faire un réalisé pour la colonne G ?
Je m'explique : si je pars de la colonne B par rapport à aujourd'hui, je suis à tant de jours de ma date de fin. Est-ce que cela serait plus judicieux ?

Là encore je ne vois pas bien ce que tu appelles un "réalisé".
Est-ce que ça veut dire qu'au lieu d'afficher une boule de couleur tu veux afficher le nombre de jours restant avant la fin de la formation ?