IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Mise en forme conditionnelle avec des heures par rapport à l'heure actuelle [XL-2007]


Sujet :

Excel

  1. #1
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut Mise en forme conditionnelle avec des heures par rapport à l'heure actuelle
    Bonjour à tous,

    Je souhaiterais créer des mises en forme conditionnelles avec 3 conditions différentes.

    Après une extraction d'un logiciel externe avec mise à jour automatique du classeur Excel, j'ai plusieurs lignes avec des heures différentes. Ce que je cherche à faire, c'est que les cellules de la colonne D se colorent selon 3 conditions :

    Si l'heure de la cellule + 30 minutes >= Heure actuelle alors Couleur Jaune
    Si l'heure de la cellule + 60 minutes >= Heure actuelle alors Couleur Orange
    Si l'heure de la cellule + 90 minutes >= Heure actuelle alors Couleur Rouge

    Je joins un fichier un exemple. En colonne C j'ai mes heures qui seront fixes, et en cellule G3 l'heure actuelle (qui sera donc mise à jour automatiquement et qui est variable). Du coup, le formatage doit évoluer à chaque mise à jour.

    Merci de votre aide,

    Bonne jourée !

    Test.xlsx

  2. #2
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    La première chose à s'assurer dans la cas d'une récupération de données externe, c'est de savoir si ce sont réellement des heures (au sens Excel du terme) qui sont transmises.

    Si c'est le cas, tu pourras utiliser la fonction MAINTENANT() dans ta formule de mise en forme conditionnelle, sachant que 30, 60 et 90 min sont égales, respectivement à 1/48, 1/24 et 1/16 de jour.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  3. #3
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    En fait la donnée que je récupère comprend une date et une heure, que je transforme en deux colonnes (une avec la date et une avec l'heure donc avec un format Time).

    C'est plus au niveau de la formule en elle même que ça pose problème, je n'arrive pas à retranscrire ce que je souhaite étant donné que je suis pas un expert Excel...

  4. #4
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Citation Envoyé par Marco Woods Voir le message
    En fait la donnée que je récupère comprend une date et une heure, que je transforme en deux colonnes (une avec la date et une avec l'heure donc avec un format Time).
    Ca, ce n'est pas une bonne idée.
    Si tu veux comparer la valeur récupérer à l'instant présent, il est préférable qu'elle contienne aussi la date.

    Si tu passes la cellule au format Standard, est-ce qu'elle reste comme elle est (ce qui signifierait que ce n'est pas une date mais du texte) ou est-ce qu'elle se change en nombre ?
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  5. #5
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    En fait je fais comme ça, d'une parce que ça m'a semblé plus simple, et aussi car les lignes se mettent à jour automatiquement et ne restent pas plus de 2 heures dans le logiciel et donc dans Excel. Mais c'est vrai que le problème peut intervenir par exemple à minuit, il faudrait donc y intégrer la date mais je ne sais pas trop comment...

    Il s'agit bien d'un nombre puisqu'on format Standard j'ai un nombre et non du texte.

  6. #6
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    En supposant que ta cellule est en A1.
    Ruban Accueil > Mise en forme conditionnelle > Gérer les règles > Nouvelle règle > Utiliser une formule.
    Comme formule, pour 30 min tu tapes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    = A1 + (30/24/60) >= maintenant()
    Tu ajoutes deux règles supplémentaires respectivement pour 60 et 90 min.

    Attention de les mettre dans le bon ordre.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  7. #7
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    Merci pour ta réponse Menhir,

    J'ai bien appliqué tes formules (dans l'ordre jaune, orange, rouge) dans la MEFC mais il n'y a rien qui change. A mon avis le problème doit venir du format des différentes cellules, j'ai pourtant mis la formule INT pour la date et MOD pour l'heure...

    Je transmets le fichier modifié en pièce jointe.

    Test.xlsx

  8. #8
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    Au final j'ai trouvé une solution "bricolage" pour éviter les problèmes de format.

    Pour info, j'ai créé 3 cellules avec 30 minutes, 1 heure puis 1h30, et j'ai lié les MEFC directement à ces trois cellules.

    Merci pour votre temps en tout cas.

  9. #9
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Il n'y a pas de bricolage à faire,
    Si la date et l'heure sont en colonne B et les premières données commence en cellule A2 (première ligne avec les étiquettes de colonne).
    Admettons que l'on veuille mettre en forme l'ensemble de la ligne
    Sachant que 30' est égal à 0,0208, 60' à 0,0417 et 90' à 0,0625
    Il suffit d'additionner la valeur de la colonne B avec le temps (0,0208, 0,0417 et 0,0625) et comparer avec MAINTENANT répartie sur trois règles en commençant par la plus petite.
    Soit pour la première règle
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =$B3+0,0208>=MAINTENANT()
    et ainsi de suite
    Ne pas oublier de cocher : Interrompre si VRAI et de sélectionner la plage des données au préalable ($A$2:$?$?)

    Et comme l'a très justement fait remarquer Menhir, la date (entier) et l'heure (décimale) forme un tout et sont un numéro de série.
    Philippe Tulliez
    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
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  10. #10
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Citation Envoyé par Marco Woods Voir le message
    A mon avis le problème doit venir du format des différentes cellules, j'ai pourtant mis la formule INT pour la date et MOD pour l'heure...
    Je te répète une nouvelle fois que c'est une mauvaise idée.

    Si tu sépares l'heure de la date, ta valeur va se retrouver au 01/01/1901, donc à plusieurs ANNEES de la valeur renvoyée par la fonction MAINTENANT(), donc systématiquement à bien plus de 90 min.

    Je le répète : dans Excel, la date et l'heure forment un tout. Il est préférable d'utiliser le format pour les distinguer mais conserver l'intégrité de la valeur elle-même.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  11. #11
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    Bonjour et merci à vous deux,

    J'ai suivi ta démarche Philippe Tulliez et ça ne fonctionne pas sur mon fichier, les lignes se colorent en rouge même si le critère n'est pas respecté.

    Peut-être que ça vient du fait que dans ma colonne B (avec la date et l'heure) j'ai une formule recherche qui récupère la date dans un autre tableau, le problème vient peut-être de là ?

  12. #12
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    J'ai suivi ta démarche Philippe Tulliez et ça ne fonctionne pas sur mon fichier, les lignes se colorent en rouge même si le critère n'est pas respecté.
    J'ai fait les tests avec des cellules contenant en colonne B à partir de B2 une date + une heure
    As-tu lu l'entièreté de mon message et bien suivi l'ordre des règles ?
    Ne pas oublier de cocher : Interrompre si VRAI et de sélectionner la plage des données au préalable ($A$2:$?$?)
    Philippe Tulliez
    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
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  13. #13
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    Re,

    Oui j'ai bien suivi la démarche (condition à 30 minutes, puis 1h puis 1h30) et bien coché les cases Interrompre si vrai (quel est le but de ce critère d'ailleurs?)

    J'ai également fait le test avec la date et l'heure, en format Standard ça me donne 42059,37186 donc il s'agit bien d'une date...

  14. #14
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Voici un classeur exemple avec la mise en forme conditionnelle contenant les formules telles que décrites dans ma réponse du 23/02 à 18h42
    Comme, il est extrêmement difficile de démontrer que la MFC fonctionne en se référant à la formule MAINTENANT par rapport à des heures encodées ou importées dans une des tranches horaires situées entre 30' et 1h30 de décalage, j'ai créé une formule nommée ThisTime qui renvoie la valeur de MAINTENANT si les cellules F2(Date de test) et G2(Heure de test) sont vides mais renverra F2+G2 si celle-ci sont remplies.
    La formule nommée
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(NBVAL('Feuille test'!$F$2:$G$2)=2;'Feuille test'!$F$2+'Feuille test'!$G$2;MAINTENANT())
    La MFC
    Règle 1 (Jaune)
    Règle 2 (Orange)
    Règle 3 (Rouge)
    Date de test (F2) : 23/02/2015
    Heure de test (G2) : 18:00, 19:00, 19:20, 20:00 et 21:00

    Image de la mise en forme pour le 23 février 2015 à 19h20

    Nom : MFC avec ThisTime.jpg
Affichages : 5684
Taille : 80,9 Ko
    Fichiers attachés Fichiers attachés
    Philippe Tulliez
    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
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  15. #15
    Futur Membre du Club
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    Février 2015
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes

    Informations forums :
    Inscription : Février 2015
    Messages : 15
    Points : 7
    Points
    7
    Par défaut
    Bonjour,

    Merci pour votre fichier et les explications qui vont avec, ce qui va me permettre de solutionner mon problème.

    Bonne continuation et à bientôt sur le forum !

    Marco

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2007] Compatibilité des mises en forme conditionnelles avec les macros
    Par DarkGriffin dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 17/01/2013, 09h53
  2. Réponses: 5
    Dernier message: 23/01/2012, 14h18
  3. Réponses: 2
    Dernier message: 07/07/2008, 10h11
  4. Mise en forme conditionnelle avec Access 97
    Par soso78 dans le forum IHM
    Réponses: 4
    Dernier message: 22/11/2007, 21h25
  5. Réponses: 4
    Dernier message: 29/03/2007, 10h10

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo