[XL-2007] Mise en forme conditionnelle sur base d'une date

Hitman_11 · 06/07/2011, 22h39

Bonsoir,
Je ne sais pas comment parvenir à résoudre mon problème.
Je reçois un produit, avec un n° de lot, sa date de fabrication, sa durée de vie.
Je souhaite entrer ces données, calculer la date de péremption du produit. Jusque la je sais faire.
Par contre selon le statut je souhaite que le n° de lot du produit s'affiche sur un fond rouge lorsqu'il est périmé, et sur fond jaune lorsque le produit approche de la date de péremption, disons 5 mois avant.
J'ai fais beaucoup d'essais sans succès.
Je vous remercie pour vôtre aide.

@mitiées.

corona · 06/07/2011, 23h08

Bonsoir,
Voir du côté de la Mise en forme conditionnelle
Excel 2003 Format/Mise en forme conditionnelle...
Excel 2007 [Accueil]/Style/Mise en forme conditionnelle
Lire cet excellent tutoriel sur ce sujet

Hitman_11 · 07/07/2011, 06h17

Merci Corona,
Le tuto est excellent, mais malgré lui je ne parviens pas à résoudre le pb.
Je pense que cela vient de la façon que j'ai imaginé pour mon cas.
Il me semble que le calcul d'une alerte compris entre une date XX et YYY fait que la mise en forme conditionnelle ne fonctionne plus.

Pierre Fauconnier · 07/07/2011, 08h13

Salut.

Les règles doivent être établies dans un ordre précis lorsqu'il y a concurrence entre elles. Cela dépend donc de la façon de rédiger lesdites règles.

En considérant le n° de lot en A2 et la date de péremption en B2 (sous format date), tu auras ceci pour déterminer que le lot est périmé

Citation:

=(B2<AUJOURDHUI())

et ceci pour déterminer que le lot approche de la péremption

Citation:

=(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())+5;JOUR(AUJOURDHUI()))>B2)

Tu pourrais aussi te contenter d'une approximation qui rendra la formule plus simple en considérant des mois de 30 jours.

Citation:

=(AUJOURDHUI()+150>B2)

La règle déterminant que le lot est périmé doit être placée avant celle déterminant que le lot approche de la péremption.

Hitman_11 · 08/07/2011, 14h56

Merci pierre cela fonctionne nickel.
@mitiées

Citation:

Envoyé par Pierre Fauconnier

Salut.

Les règles doivent être établies dans un ordre précis lorsqu'il y a concurrence entre elles. Cela dépend donc de la façon de rédiger lesdites règles.

En considérant le n° de lot en A2 et la date de péremption en B2 (sous format date), tu auras ceci pour déterminer que le lot est périmé

et ceci pour déterminer que le lot approche de la péremption

Tu pourrais aussi te contenter d'une approximation qui rendra la formule plus simple en considérant des mois de 30 jours.

La règle déterminant que le lot est périmé doit être placée avant celle déterminant que le lot approche de la péremption.

Hitman_11 · 11/07/2011, 08h48

Bonjour Pierre,
Mes premiers essais semblaient concluant, mais à l'usage cela ne correspond pas réellement à mon besoin.
En pj le fichier en question.
Je souhaiterais une alerte à 100 jours avant la date de péremption.
Peux tu m'aider à nouveau?
Et même m'expliquer un peu comment tu as procédé, car je ne connais pas cette façon de faire?
Je te remercie.
@mitiées

Syphochaos · 11/07/2011, 09h17

Bonjour Hitman_11, corona, Pierre Fauconnier.

Hitman_11, il te faut modifier tes règles de MFC.

Remplace :

Code :

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

=(P4<AUJOURDHUI())

Par :

Code :

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

=SI(P4>AUJOURDHUI();0;1)

Remplace :

Code :

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

=(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+5);JOUR(AUJOURDHUI()))>P4)

Par :

Code :

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

=SI(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+5);JOUR(AUJOURDHUI()))>P4;0;1)

Sypho.

Hitman_11 · 11/07/2011, 20h13

Bonsoir Sypho,
Pourrais tu m'expliquer vite fait le cheminement de ta proposition?
Et ce de prévenir tout pb que pourrais l'utilisateur du fichier, pour que je puisse réparer.

Merci pour vôtre aide.

@mitiées.

Pierre Fauconnier · 11/07/2011, 21h03

Perso, je n'ai pas trop compris la raison des modifications de sypho, notamment la présence de $g$5...

La formule que j'ai donnée

Citation:

=(P4<AUJOURDHUI())

allume la cellule lorsque p4 est passée. Si tu veux allumer cette cellule également le jour de péremption, il suffit de mettre <=. L'utilisation de la fonction SI() n'est pas obligatoire, la formule que j'ai donnée renvoyant VRAI (=1) ou FAUX (=0).

Concernant ta demande d'explications, tu dois savoir que les dates sont des nombres (des numéros de série, tels que dénommés dans l'aide de l'assistant fonction et dans l'aide d'Excel). Tu peux donc ajouter ou retrancher des valeurs à une date.

Si tu veux tester que tu es dans les 100 jours précédant la date de péremption, tu peux simplement tester que la date du jour (fonction AUJOURDHUI()) est supérieure (ou égale, à toi de voir) à la date de péremption - 100 jours. Ta formule de mfc deviendrait donc

Citation:

=(aujourdhui()>p4-100)

ou

Citation:

=(aujourdhui()>=p4-100)

A nouveau, comme dit précédemment, cette formule doit être placée APRES (= en dessous) de celle qui teste que tu as dépassé la date de péremption.

L'utilisation de la fonction DATE() permet d'ajouter des valeurs à des parties de date (année, mois ou jour). Si tu ne maîtrises pas trop les dates avec Excel, je te conseille ce tutoriel de SilkyRoad.

Hitman_11 · 12/07/2011, 07h27

La condition qui vérifie la période d'alerte, ne doit pas être une formule de type encadrement.
Cas 1: la date de péremption est dépassée, le n° de lot est surligné rouge.
Cas 2: la date d'aujourd'hui est inclue dans la période d'alerte, le n° de lot est surligné en jaune.
La date de péremption n'est pas dépassé, et aujourd'hui n'est pas dans la période d'alerte, pas de mise en forme.

Je lis le tuto.

Syphochaos · 12/07/2011, 08h20

Bonjour à vous.

Citation:

Envoyé par Pierre Fauconnier

Perso, je n'ai pas trop compris la raison des modifications de sypho, notamment la présence de $g$5...

Alors, ce $G$5 est dû à une mauvaise manipulation de ma part dans Excel.. Ca m'apprendra à vouloir utiliser les flèches du clavier dans la gestion des règles de MFC. Il n'a rien à faire ici, je m'en excuse.

Ensuite, c'est en effectuant ces modifications que les règles de MFC faisaient, selon moi, ce que voulait Hitman_11. Parce que le fichier mis à disposition par Hitman_11 ne faisait rien chez moi.

Coup de chance ?! Moi qui n'ai rien compris ?! Possible..

Sypho.

Pierre Fauconnier · 12/07/2011, 11h08

Citation:

Envoyé par Hitman_11

La condition qui vérifie la période d'alerte, ne doit pas être une formule de type encadrement.
Cas 1: la date de péremption est dépassée, le n° de lot est surligné rouge.
Cas 2: la date d'aujourd'hui est inclue dans la période d'alerte, le n° de lot est surligné en jaune.
La date de péremption n'est pas dépassé, et aujourd'hui n'est pas dans la période d'alerte, pas de mise en forme.

Je lis le tuto.

Les formules que je t'ai données font ce que tu demandes...

Ca marche chez toi?

Hitman_11 · 12/07/2011, 12h16

Non ça ne fonctionne pas.

Citation:

Envoyé par Pierre Fauconnier

Les formules que je t'ai données font ce que tu demandes...

Ca marche chez toi?

corona · 12/07/2011, 13h12

Bonjour,
Qu'est-ce qui ne fonctionne pas ?
La première chose à faire lorsque le résultat attendu ne correspond pas à ce que l'on attends est de vérifier l'adressage des cellules. Le référencement doit-il être de type relatif, absolu ou mixte ?

Code :

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

=(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+$G$5+5);JOUR(AUJOURDHUI()))>P4)

Ce type de formule fonctionnera t'il dans ton tableau, où la référence à $G$5 me semble curieux ?

Syphochaos · 12/07/2011, 13h30

Citation:

Envoyé par corona

Ce type de formule fonctionnera t'il dans ton tableau, où la référence à $G$5 me semble curieux ?

Il est curieux en effet :

Citation:

Envoyé par Syphochaos

Alors, ce $G$5 est dû à une mauvaise manipulation de ma part dans Excel.. Ca m'apprendra à vouloir utiliser les flèches du clavier dans la gestion des règles de MFC. Il n'a rien à faire ici, je m'en excuse.

Citation:

Envoyé par Pierre Fauconnier

Les formules que je t'ai données font ce que tu demandes...

Chez moi, elles n'atteignaient pas l'objectif visé non plus..

A moins que l'erreur soit de Hitman_11 dans le fichier a joint à la discussion, auquel cas je vous prie de pardonner mon intervention.

Sypho.

corona · 12/07/2011, 13h34

Bonjour,
Je suis convaincu que la réponse de Pierre est corrrecte.
1 - Vérifier le référencement des cellules
2 - Dites ce qui ne vas pas.
a) Aucune mise en forme ne fonctionne ?
b) La première ligne est la seule à fonctionner
3 - Afficher les formules que vous avez placées dans la MFC

Syphochaos · 12/07/2011, 13h48

Citation:

Envoyé par corona

Bonjour,
Je suis convaincu que la réponse de Pierre est corrrecte.
1 - Vérifier le référencement des cellules

Après vérification (sans modification de ma part) du fichier de Hitman_11, le référencement des cellules est bon.

Citation:

Envoyé par corona

2 - Dites ce qui ne vas pas.
a) Aucune mise en forme ne fonctionne ?
b) La première ligne est la seule à fonctionner

Seule la MFC concernant la date dépassée (de couleur rouge) fonctionne.

Citation:

Envoyé par corona

3 - Afficher les formules que vous avez placées dans la MFC

Voici les deux formules que Hitman_11 a placé dans son fichier :

Code :

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

=(P4<AUJOURDHUI())

Code :

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

=(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+$G$5+5);JOUR(AUJOURDHUI()))>P4)

Ainsi, la première formule fonctionne correctement.

Seulement, pour la deuxième, même en enlevant le $G$5 (qui n'est pas de moi finalement

), la case ne se colore pas en jaune.

Sypho.

corona · 12/07/2011, 14h09

Bonjour,
C'est bien ce que j'avais écrit plus haut il me semble. Ce $G$5 devrait je pense être modifié par G4 ou $G4 s'il faut mettre en couleur toute la ligne.

Pierre Fauconnier · 18/07/2011, 21h37

Salut.

Citation:

Envoyé par Syphochaos

Seulement, pour la deuxième, même en enlevant le $G$5 (qui n'est pas de moi finalement ), la case ne se colore pas en jaune.

C'est normal. Le +5 a été placé dans les parenthèses de MOIS() alors que je l'avais mis dehors dans la formule que j'ai donnée initialement.

Pour balayer les trois cas, voici les bonnes formules, à partir de l'exemple proposé par Hitman_11. Il faut les préciser dans l'ordre dans lequel je les cite. Afin de pouvoir les utiliser sur plusieurs cellules de la même ligne, j'ai simplement verrouillé la colonne P qui contient la date de péremption

Citation:

=($P4<AUJOURDHUI())

avec un format "fond rouge"

Citation:

=(AUJOURDHUI()>$P4-100)

avec un format "fond orange"

Citation:

=(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())+5;JOUR(AUJOURDHUI()))>$P4)

avec un format fond jaune

Normalement, tout devrait fonctionner comme demandé initialement.

Voici le fichier de test.

06/07/2011, 22h39	#1
Hitman_11 Invité de passage Inscription : octobre 2009 Messages : 22 Détails du profil Informations forums : Inscription : octobre 2009 Messages : 22 Points : 0 Points : 0	Mise en forme conditionnelle sur base d'une date Bonsoir, Je ne sais pas comment parvenir à résoudre mon problème. Je reçois un produit, avec un n° de lot, sa date de fabrication, sa durée de vie. Je souhaite entrer ces données, calculer la date de péremption du produit. Jusque la je sais faire. Par contre selon le statut je souhaite que le n° de lot du produit s'affiche sur un fond rouge lorsqu'il est périmé, et sur fond jaune lorsque le produit approche de la date de péremption, disons 5 mois avant. J'ai fais beaucoup d'essais sans succès. Je vous remercie pour vôtre aide. @mitiées.
	00

06/07/2011, 23h08	#2
corona Expert Confirmé Philippe Tulliez Développeur et formateur VBA, Excel et Word. Inscription : janvier 2010 Messages : 1 305 Détails du profil Informations personnelles : Nom : Philippe Tulliez Localisation : Belgique Informations professionnelles : Activité : Développeur et formateur VBA, Excel et Word. Informations forums : Inscription : janvier 2010 Messages : 1 305 Points : 2 653 Points : 2 653	Bonsoir, Voir du côté de la Mise en forme conditionnelle Excel 2003 Format/Mise en forme conditionnelle... Excel 2007 [Accueil]/Style/Mise en forme conditionnelle Lire cet excellent tutoriel sur ce sujet __________________ Philippe Tulliez http://philippe.tulliez.be Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau) Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
	10

11/07/2011, 09h17	#7
Syphochaos Membre habitué Cyril CHMIEL Étudiant Inscription : avril 2011 Messages : 70 Détails du profil Informations personnelles : Nom : Cyril CHMIEL Localisation : France Informations professionnelles : Activité : Étudiant Secteur : Industrie Informations forums : Inscription : avril 2011 Messages : 70 Points : 109 Points : 109	Bonjour Hitman_11, corona, Pierre Fauconnier. Hitman_11, il te faut modifier tes règles de MFC. Remplace : Code : Sélectionner tout - Visualiser dans une fenêtre à part =(P4<AUJOURDHUI()) Par : Code : Sélectionner tout - Visualiser dans une fenêtre à part =SI(P4>AUJOURDHUI();0;1) Remplace : Code : Sélectionner tout - Visualiser dans une fenêtre à part =(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+5);JOUR(AUJOURDHUI()))>P4) Par : Code : Sélectionner tout - Visualiser dans une fenêtre à part =SI(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+5);JOUR(AUJOURDHUI()))>P4;0;1) Sypho.
	00

12/07/2011, 13h12	#14
corona Expert Confirmé Philippe Tulliez Développeur et formateur VBA, Excel et Word. Inscription : janvier 2010 Messages : 1 305 Détails du profil Informations personnelles : Nom : Philippe Tulliez Localisation : Belgique Informations professionnelles : Activité : Développeur et formateur VBA, Excel et Word. Informations forums : Inscription : janvier 2010 Messages : 1 305 Points : 2 653 Points : 2 653	Bonjour, Qu'est-ce qui ne fonctionne pas ? La première chose à faire lorsque le résultat attendu ne correspond pas à ce que l'on attends est de vérifier l'adressage des cellules. Le référencement doit-il être de type relatif, absolu ou mixte ? Code : Sélectionner tout - Visualiser dans une fenêtre à part =(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI()+$G$5+5);JOUR(AUJOURDHUI()))>P4) Ce type de formule fonctionnera t'il dans ton tableau, où la référence à $G$5 me semble curieux ? __________________ Philippe Tulliez http://philippe.tulliez.be Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau) Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
	00

12/07/2011, 13h34	#16
corona Expert Confirmé Philippe Tulliez Développeur et formateur VBA, Excel et Word. Inscription : janvier 2010 Messages : 1 305 Détails du profil Informations personnelles : Nom : Philippe Tulliez Localisation : Belgique Informations professionnelles : Activité : Développeur et formateur VBA, Excel et Word. Informations forums : Inscription : janvier 2010 Messages : 1 305 Points : 2 653 Points : 2 653	Bonjour, Je suis convaincu que la réponse de Pierre est corrrecte. 1 - Vérifier le référencement des cellules 2 - Dites ce qui ne vas pas. a) Aucune mise en forme ne fonctionne ? b) La première ligne est la seule à fonctionner 3 - Afficher les formules que vous avez placées dans la MFC __________________ Philippe Tulliez http://philippe.tulliez.be Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau) Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
	00

07/07/2011, 06h17	#3
Hitman_11 Invité de passage Inscription : octobre 2009 Messages : 22 Détails du profil Informations forums : Inscription : octobre 2009 Messages : 22 Points : 0 Points : 0	Merci Corona, Le tuto est excellent, mais malgré lui je ne parviens pas à résoudre le pb. Je pense que cela vient de la façon que j'ai imaginé pour mon cas. Il me semble que le calcul d'une alerte compris entre une date XX et YYY fait que la mise en forme conditionnelle ne fonctionne plus.
	00

11/07/2011, 20h13	#8
Hitman_11 Invité de passage Inscription : octobre 2009 Messages : 22 Détails du profil Informations forums : Inscription : octobre 2009 Messages : 22 Points : 0 Points : 0	Bonsoir Sypho, Pourrais tu m'expliquer vite fait le cheminement de ta proposition? Et ce de prévenir tout pb que pourrais l'utilisateur du fichier, pour que je puisse réparer. Merci pour vôtre aide. @mitiées.
	00

12/07/2011, 07h27	#10
Hitman_11 Invité de passage Inscription : octobre 2009 Messages : 22 Détails du profil Informations forums : Inscription : octobre 2009 Messages : 22 Points : 0 Points : 0	La condition qui vérifie la période d'alerte, ne doit pas être une formule de type encadrement. Cas 1: la date de péremption est dépassée, le n° de lot est surligné rouge. Cas 2: la date d'aujourd'hui est inclue dans la période d'alerte, le n° de lot est surligné en jaune. La date de péremption n'est pas dépassé, et aujourd'hui n'est pas dans la période d'alerte, pas de mise en forme. Je lis le tuto.
	00

12/07/2011, 14h09	#18
corona Expert Confirmé Philippe Tulliez Développeur et formateur VBA, Excel et Word. Inscription : janvier 2010 Messages : 1 305 Détails du profil Informations personnelles : Nom : Philippe Tulliez Localisation : Belgique Informations professionnelles : Activité : Développeur et formateur VBA, Excel et Word. Informations forums : Inscription : janvier 2010 Messages : 1 305 Points : 2 653 Points : 2 653	Bonjour, C'est bien ce que j'avais écrit plus haut il me semble. Ce $G$5 devrait je pense être modifié par G4 ou $G4 s'il faut mettre en couleur toute la ligne. __________________ Philippe Tulliez http://philippe.tulliez.be Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau) Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email