Excel calcul des heures
Résolu
pyfil
Messages postés
3
Date d'inscription
Statut
Membre
Dernière intervention
-
Mike-31 Messages postés 18405 Date d'inscription Statut Contributeur Dernière intervention -
Mike-31 Messages postés 18405 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
je souhaite savoir s'il est possible avec Excel 2003 de calculer des heures au format heure en affichant le résultat en négatil?
Ex: 10:00:00 - 1:00:00 me donne 9:00:00 ok c'est bon
mais 1:00:00 - 10:00:00 me donne ########
Je ne trouve rien sur l'aide, si quelqu'un peut m'aider à résoudre ce serait sympa.
Merci.
je souhaite savoir s'il est possible avec Excel 2003 de calculer des heures au format heure en affichant le résultat en négatil?
Ex: 10:00:00 - 1:00:00 me donne 9:00:00 ok c'est bon
mais 1:00:00 - 10:00:00 me donne ########
Je ne trouve rien sur l'aide, si quelqu'un peut m'aider à résoudre ce serait sympa.
Merci.
A voir également:
- Excel calcul des heures
- Calcul moyenne excel - Guide
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Déplacer colonne excel - Guide
- Si ou excel - Guide
16 réponses
bonjour
calendrier depuis 1904 n'est pas coché
Pour traiter les heures négatives dans un classeur, cette option permet effectivement de fonctionner sans aucun problème avec tous les formats que l'on souhaite : sur CCM vous avez plein d'exemples qui en parlent.
Cependant lorsque l'on préconise cette option il ne faut jamais oublier de préciser que le fait de cocher cette option décale de 4 ans et 1 jour toutes les dates présentes dans le classeur.
Ainsi si vous avez saisi tous vos horaires de 2009 dans le classeur, vous allez vous retrouver avec un classeur 2013 dont les dates ne seront pas facilement en phase.
Cette option est donc à cocher lors de la création avant saisie des dates et ensuite effectivement c'est la façon la plus simple de gérer des heures négatives : retour vers le passé ?
calendrier depuis 1904 n'est pas coché
Pour traiter les heures négatives dans un classeur, cette option permet effectivement de fonctionner sans aucun problème avec tous les formats que l'on souhaite : sur CCM vous avez plein d'exemples qui en parlent.
Cependant lorsque l'on préconise cette option il ne faut jamais oublier de préciser que le fait de cocher cette option décale de 4 ans et 1 jour toutes les dates présentes dans le classeur.
Ainsi si vous avez saisi tous vos horaires de 2009 dans le classeur, vous allez vous retrouver avec un classeur 2013 dont les dates ne seront pas facilement en phase.
Cette option est donc à cocher lors de la création avant saisie des dates et ensuite effectivement c'est la façon la plus simple de gérer des heures négatives : retour vers le passé ?
Bonjour tout le monde,
Comment va?
- Je pense que pyfil voudrait le résultat négatif soit : -09:00:00. A moins que je ne m'abuse (ce qui est fort possible ces temps ci). Or : =MOD(B1-A1;1) nous donne le nombre d'heures exact entre 2 heures données. Dans l'exemple du sujet nous aurons donc comme résultat : 15:00:00
- La formule de Vaucluse(bonjour au passage) nous donne la différence mais sous forme positive (09:00:00).
- Par contre en modifiant légèrement sa formule on obtient le résultat escompté :
=SI(A11>=B11;(A11-B11);TEXTE(-(A11-B11);"- " & "hh:mm:ss"))
Inconvénient : avec cette formule il est impossible d'effectuer des calculs avec ce résultat dès lors qu'il est négatif...
michel_m salut!! ta parkinson a agit également sur le e de Eeriiic...
Comment va?
- Je pense que pyfil voudrait le résultat négatif soit : -09:00:00. A moins que je ne m'abuse (ce qui est fort possible ces temps ci). Or : =MOD(B1-A1;1) nous donne le nombre d'heures exact entre 2 heures données. Dans l'exemple du sujet nous aurons donc comme résultat : 15:00:00
- La formule de Vaucluse(bonjour au passage) nous donne la différence mais sous forme positive (09:00:00).
- Par contre en modifiant légèrement sa formule on obtient le résultat escompté :
=SI(A11>=B11;(A11-B11);TEXTE(-(A11-B11);"- " & "hh:mm:ss"))
Inconvénient : avec cette formule il est impossible d'effectuer des calculs avec ce résultat dès lors qu'il est négatif...
michel_m salut!! ta parkinson a agit également sur le e de Eeriiic...
Binjour
sur 2003, je ne pense pas (mais ce n'est pas sur) que cela soit possible
Vous pouvez, au mieux, afficher le résultat, mais pas en négatif
soir la ,formule, si votre opération de base est A1-B1:
=SI(B1>A1;B1-A1;A1-B1)
ce qui ne permet pas de renvoyez des calculs ensuite, sauf peut être en combinant les formules sur deux colonnes pour faire une colonne positive et une colonne négative:
soit
en C colonne heure +
en D colonne Heure -
en C la formule:
SI(A1<B1;A1-B1;0)
est en D
SI(A1>B1;B1-A1;0)
ainsi vous pourrez utiliser les sommes de chaque colonne pour faire un calcul final.
Crdlmnt
-
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
sur 2003, je ne pense pas (mais ce n'est pas sur) que cela soit possible
Vous pouvez, au mieux, afficher le résultat, mais pas en négatif
soir la ,formule, si votre opération de base est A1-B1:
=SI(B1>A1;B1-A1;A1-B1)
ce qui ne permet pas de renvoyez des calculs ensuite, sauf peut être en combinant les formules sur deux colonnes pour faire une colonne positive et une colonne négative:
soit
en C colonne heure +
en D colonne Heure -
en C la formule:
SI(A1<B1;A1-B1;0)
est en D
SI(A1>B1;B1-A1;0)
ainsi vous pourrez utiliser les sommes de chaque colonne pour faire un calcul final.
Crdlmnt
-
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
bonjour
A1 est l'heure début
B1 est l'heure fin
formule communiquée par Eeriiiiic (j'ai pas compté le nombre de i, Parkinson a encore frappé)
A1 est l'heure début
B1 est l'heure fin
formule communiquée par Eeriiiiic (j'ai pas compté le nombre de i, Parkinson a encore frappé)
=MOD(B1-A1;1)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonjour
et merci Pijaku pour cette précision sur la formule MOD, je commençais à douter depuis que j'en entends parler!
En fait elle permet de calculer le temps passé entre deux horaires à cheval ou non sur deux jours, mais pas la différence entre deux "montants" d'heures.
crdlmnt
et merci Pijaku pour cette précision sur la formule MOD, je commençais à douter depuis que j'en entends parler!
En fait elle permet de calculer le temps passé entre deux horaires à cheval ou non sur deux jours, mais pas la différence entre deux "montants" d'heures.
crdlmnt
Salut Vaucluse,
Pour rebondir sur ton message, la fonction MOD() ne sert pas qu'à cela...
pour info, tu trouveras d'autres exemples concrets ici de son utilisation.
@ +
Pour rebondir sur ton message, la fonction MOD() ne sert pas qu'à cela...
pour info, tu trouveras d'autres exemples concrets ici de son utilisation.
@ +
Salut tout le monde,
Si le calcul porte uniquement sur des heures saisies et non sur un calcul ou la fonction MOD () est tout à fait indiqué.
Il est possible de faire un calcul et de créer un format personnalisé.
si on reprend l'exemple 1:00:00 - 10:00:00 il est possible d'utiliser la simple formule =A1-B1 par exemple si ces deux valeurs se trouvent dans ces cellules ensuite Format/Cellule/Nombre/Personnalisé et coller ce format
[hh]":"mm":"ss;[Rouge]"- "[hh]":"mm":"ss
il est encore possible d'agrémenter le format en remplaçant les : par h pour heure et mn
[hh]" h "mm" mn "ss;[Rouge]"- "[hh]" h "mm" mn "ss
et le format reste numérique exploitable dans les calculs
Si le calcul porte uniquement sur des heures saisies et non sur un calcul ou la fonction MOD () est tout à fait indiqué.
Il est possible de faire un calcul et de créer un format personnalisé.
si on reprend l'exemple 1:00:00 - 10:00:00 il est possible d'utiliser la simple formule =A1-B1 par exemple si ces deux valeurs se trouvent dans ces cellules ensuite Format/Cellule/Nombre/Personnalisé et coller ce format
[hh]":"mm":"ss;[Rouge]"- "[hh]":"mm":"ss
il est encore possible d'agrémenter le format en remplaçant les : par h pour heure et mn
[hh]" h "mm" mn "ss;[Rouge]"- "[hh]" h "mm" mn "ss
et le format reste numérique exploitable dans les calculs
rebonjour ou bonjour
Mike31, es tu certain de ton format? je viens d'essayer: ca me donne une suite de ###
d'autre ton signe négatif est entre guillemets "-" donc la valeur en mémoire tiendrait compte d'une mise en format ?
mais j'ai peut être rien pigé (probable)
Mike31, es tu certain de ton format? je viens d'essayer: ca me donne une suite de ###
d'autre ton signe négatif est entre guillemets "-" donc la valeur en mémoire tiendrait compte d'une mise en format ?
mais j'ai peut être rien pigé (probable)
bonjour et merci gbinforme!
pour appuyer tes propos je cite le grand chef Excel Laurent Longre:
http://xcell05.free.fr/pages/form/dateheure.htm#Calendriers
..."Excel laisse le choix entre deux dates de référence possibles : le 1er janvier 1900 ou le 1er janvier 1904. Le choix de la date de référence peut être effectué dans l'onglet "Calcul" de la boîte de dialogue Outils -> Options. Par défaut, l'option "Calendrier depuis 1904" est désactivée lorsque l'on crée un nouveau classeur. Il est plutôt conseillé de ne pas l'activer. Celle-ci a été prévue surtout pour des raisons de compatibilité avec les versions Macintosh d'Excel, qui utilisent un système de dates démarrant au 1er janvier 1904.
Lorsque l'on copie une date reposant sur le calendrier 1904 vers un classeur reposant sur le calendrier 1900, cette date est automatiquement transposée. Par exemple, si une cellule contenant la date 1/1/1990 dans un classeur "standard" (calendrier 1900) est copiée dans un classeur avec l'option 1904, cette date est automatiquement transposée en fonction de la nouvelle date de référence et devient le 02/01/94.
Si les classeurs ne sont pas destinés à être lus sur des Mac, le calendrier depuis 1900 devrait rester l'option choisie par défaut. Les formules présentées dans cette page reposent toutes sur ce calendrier, et pourraient renvoyer des résultats faux si 1904 est choisi comme date de référence. "...
dont acte.
maintenant, il serait intéressant de savoir ca que veut réellement pyfil: heures de présence ou calcul car on cause sans trop savoir le but de la demande
merci aussi à Mike
pour appuyer tes propos je cite le grand chef Excel Laurent Longre:
http://xcell05.free.fr/pages/form/dateheure.htm#Calendriers
..."Excel laisse le choix entre deux dates de référence possibles : le 1er janvier 1900 ou le 1er janvier 1904. Le choix de la date de référence peut être effectué dans l'onglet "Calcul" de la boîte de dialogue Outils -> Options. Par défaut, l'option "Calendrier depuis 1904" est désactivée lorsque l'on crée un nouveau classeur. Il est plutôt conseillé de ne pas l'activer. Celle-ci a été prévue surtout pour des raisons de compatibilité avec les versions Macintosh d'Excel, qui utilisent un système de dates démarrant au 1er janvier 1904.
Lorsque l'on copie une date reposant sur le calendrier 1904 vers un classeur reposant sur le calendrier 1900, cette date est automatiquement transposée. Par exemple, si une cellule contenant la date 1/1/1990 dans un classeur "standard" (calendrier 1900) est copiée dans un classeur avec l'option 1904, cette date est automatiquement transposée en fonction de la nouvelle date de référence et devient le 02/01/94.
Si les classeurs ne sont pas destinés à être lus sur des Mac, le calendrier depuis 1900 devrait rester l'option choisie par défaut. Les formules présentées dans cette page reposent toutes sur ce calendrier, et pourraient renvoyer des résultats faux si 1904 est choisi comme date de référence. "...
dont acte.
maintenant, il serait intéressant de savoir ca que veut réellement pyfil: heures de présence ou calcul car on cause sans trop savoir le but de la demande
merci aussi à Mike
Salut et bravo tout le monde.
En tout cas, même si pyfil n'est plus là, il aura eu un bel éventail de possibilités...
maintenant, il serait intéressant de savoir ca que veut réellement pyfil: heures de présence ou calcul car on cause sans trop savoir le but de la demande Tout à fait, à plus forte raison si l'auteur du sujet ne refait pas surface tantôt...
Bonne journée à vous en tout cas.
En tout cas, même si pyfil n'est plus là, il aura eu un bel éventail de possibilités...
maintenant, il serait intéressant de savoir ca que veut réellement pyfil: heures de présence ou calcul car on cause sans trop savoir le but de la demande Tout à fait, à plus forte raison si l'auteur du sujet ne refait pas surface tantôt...
Bonne journée à vous en tout cas.
Bonjour et merci à tous pour ces réponses qui m'ont bien aidées.
Le but de mon calcul est simplement de calculer des heures de présence avec prise en compte des heures supplémentaires et des heures de récupération (repos) dans le cas ou celles-ci ne sont pas payées.
Mes formules étaient justes, mais j'en ais profité pour les modifier ce qui apporte un petit plus à mon tableau.
La date 1904 n'étais pas cochée donc je tournais en rond, ce qui ne cause pas de problème pour les autres tableaux au niveau des dates car il sont d'autres classeurs; et au cas ou je les mélange je saurais d'où viens l'érreur.
A la fin du mois je commence une remise à niveau sur Excel et Word, cella me fera le plus grand bien.
Bon weekend à tous et au plaisir.
Pyfil
Le but de mon calcul est simplement de calculer des heures de présence avec prise en compte des heures supplémentaires et des heures de récupération (repos) dans le cas ou celles-ci ne sont pas payées.
Mes formules étaient justes, mais j'en ais profité pour les modifier ce qui apporte un petit plus à mon tableau.
La date 1904 n'étais pas cochée donc je tournais en rond, ce qui ne cause pas de problème pour les autres tableaux au niveau des dates car il sont d'autres classeurs; et au cas ou je les mélange je saurais d'où viens l'érreur.
A la fin du mois je commence une remise à niveau sur Excel et Word, cella me fera le plus grand bien.
Bon weekend à tous et au plaisir.
Pyfil
Salut,
Je reviens vite fait sur la discussion, pour remercier gbinforme d’avoir apporté mieux que je ne l’aurai fait, les explications sur la fonction calendrier 1904 , surtout que ces derniers jours j’étais pris par le temps et un peu absent du forum.
Comme le précise gbinforme cette fonction pose problème sur les dates saisies avant la déclaration de cette utilisation.
Il est possible d’écrire un code à rallonge pour faire ce calcul en fonction du calendrier 1900, à condition que le résultat soit une finalité et n’entre plus dans une formule de calcul vu que le résultat sera en format texte non convertible, si ça peu intéresser ?
Je reviens vite fait sur la discussion, pour remercier gbinforme d’avoir apporté mieux que je ne l’aurai fait, les explications sur la fonction calendrier 1904 , surtout que ces derniers jours j’étais pris par le temps et un peu absent du forum.
Comme le précise gbinforme cette fonction pose problème sur les dates saisies avant la déclaration de cette utilisation.
Il est possible d’écrire un code à rallonge pour faire ce calcul en fonction du calendrier 1900, à condition que le résultat soit une finalité et n’entre plus dans une formule de calcul vu que le résultat sera en format texte non convertible, si ça peu intéresser ?
Re,
Et voila, à contrôler, en cherchant un peu, je pense que les formules pourraient s'écrire différemment
https://www.cjoint.com/?low3Az887n
Et voila, à contrôler, en cherchant un peu, je pense que les formules pourraient s'écrire différemment
https://www.cjoint.com/?low3Az887n
Re,
Pour répondre à un message privé, petite mise au point, en D8 lorsque j'ai écrit concaténation des trois cellules D6, E6, F6, j'aurai dû écrire concaténation des trois formules, qui rend les trois cellules D6, E6, F6 inutiles, je les ai laissés pour tester et éventuellement les modifier
En conservant ces trois cellules, la concaténation des cellules aurai donné =D6&E6&F6
la formule cellule C8 par contre est indispensable, l'intégrer dans la formule en D8 dépasserai les possibilités d'Excel. Si les secondes ne sont pas nécessaire dans le résultat, la formule serait considérablement écourtée et permettrai d'intégrer la formule en C8
Pour répondre à un message privé, petite mise au point, en D8 lorsque j'ai écrit concaténation des trois cellules D6, E6, F6, j'aurai dû écrire concaténation des trois formules, qui rend les trois cellules D6, E6, F6 inutiles, je les ai laissés pour tester et éventuellement les modifier
En conservant ces trois cellules, la concaténation des cellules aurai donné =D6&E6&F6
la formule cellule C8 par contre est indispensable, l'intégrer dans la formule en D8 dépasserai les possibilités d'Excel. Si les secondes ne sont pas nécessaire dans le résultat, la formule serait considérablement écourtée et permettrai d'intégrer la formule en C8
bonjour à tous
Tu as fait un énorme travail Mike pour ces formules mais est-ce bien nécessaire ?
Le calendrier 1904 permet l'affichage des heures négatives mais enregistre, comme le calendrier 1900 un nombre décimal signé. Ce nombre en 1900 ne présente pas un format affichable mais il est totalement valide.
Étant donné que ce n'est qu'un problème d'affichage, il suffit de régler le problème d'affichage pour fonctionner avec des heures négatives en calendrier 1900.
Avec cette formule, on permet l'affichage, sous forme de texte des heures négatives :
Ce n'est plus du tout exploitable pour les calculs nous dira-t-on. Ce n'est que partiellement vrai car ainsi on peut continuer les calculs normalement et retrouver l'heure d'origine :
Un petit exemple avec le complément dans ton classeur :
http://www.cijoint.fr/cjlink.php?file=cj200911/cijqFB5Idy.xls
Tu as fait un énorme travail Mike pour ces formules mais est-ce bien nécessaire ?
Le calendrier 1904 permet l'affichage des heures négatives mais enregistre, comme le calendrier 1900 un nombre décimal signé. Ce nombre en 1900 ne présente pas un format affichable mais il est totalement valide.
Étant donné que ce n'est qu'un problème d'affichage, il suffit de régler le problème d'affichage pour fonctionner avec des heures négatives en calendrier 1900.
Avec cette formule, on permet l'affichage, sous forme de texte des heures négatives :
=SI(A1<B1;"-"&TEXTE(ABS(A1-B1);"[h]:mm:ss");A1-B1)
Ce n'est plus du tout exploitable pour les calculs nous dira-t-on. Ce n'est que partiellement vrai car ainsi on peut continuer les calculs normalement et retrouver l'heure d'origine :
=SI(GAUCHE(C1;1)="-";(SUBSTITUE(C1;"-";"")*-1)+B1;C1+B1)
Un petit exemple avec le complément dans ton classeur :
http://www.cijoint.fr/cjlink.php?file=cj200911/cijqFB5Idy.xls
Salut gbinforme,
Tout à fait d'accord avec toi, totalement inutile puisque le calendrier 1904 le fait très bien.
Par contre bravo et bien vu tes formule avec ABS que j'ai zappé.
Ces discussions pertinentes nous permettent de réviser les possibilités d’Excel, tout en aidant, enrichissent nos connaissances.
Tout à fait d'accord avec toi, totalement inutile puisque le calendrier 1904 le fait très bien.
Par contre bravo et bien vu tes formule avec ABS que j'ai zappé.
Ces discussions pertinentes nous permettent de réviser les possibilités d’Excel, tout en aidant, enrichissent nos connaissances.