Soustraction et heure négative
Fermé
vigie123
Messages postés
18
Date d'inscription
samedi 3 novembre 2012
Statut
Membre
Dernière intervention
26 avril 2013
-
Modifié par vigie123 le 8/02/2013 à 08:22
tessel75 - 10 févr. 2013 à 22:29
tessel75 - 10 févr. 2013 à 22:29
A voir également:
- Soustraction et heure négative
- Excel heure négative ✓ - Forum Excel
- Changer police heure iphone - Guide
- 1go internet = combien d'heure ✓ - Forum Internet / Réseaux sociaux
- Cette photo n’a pas été prise cette année. trouvez la date, l'heure et avec quel modèle d'appareil photo elle a été prise. ✓ - Forum Graphisme
- Convertir heure en centième excel ✓ - Forum Excel
11 réponses
michel_m
Messages postés
16593
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 mars 2023
3 293
9 févr. 2013 à 09:20
9 févr. 2013 à 09:20
Bonjour,
Le calendrier 1904 a été créé par MS pour une question de comptabilité avec Mac Intosh
Il est dangereux d'utiliser cette option car TOUS les calculs de dates et d'heures
ont alors comme base 1904
dans le cas d(heures négatives il vaut mieux faire 2 colonnes positif- négatif et rester sur le calendrier 1900
Le calendrier 1904 a été créé par MS pour une question de comptabilité avec Mac Intosh
Il est dangereux d'utiliser cette option car TOUS les calculs de dates et d'heures
ont alors comme base 1904
dans le cas d(heures négatives il vaut mieux faire 2 colonnes positif- négatif et rester sur le calendrier 1900
chossette9
Messages postés
4239
Date d'inscription
lundi 20 avril 2009
Statut
Contributeur
Dernière intervention
12 septembre 2014
1 303
8 févr. 2013 à 08:30
8 févr. 2013 à 08:30
Bonjour,
Excel ne gère pas les heures négatives. Il est possible de le contourner, en affichant un "-" devant si le résultat est négatif, mais la cellule contiendra du texte et non un nombre, donc le contenu ne sera pas directement exploitable dans des calculs.
Voici une formule à coller dans la colonne retard, en supposant que les premières données soient en ligne 2 :
Excel ne gère pas les heures négatives. Il est possible de le contourner, en affichant un "-" devant si le résultat est négatif, mais la cellule contiendra du texte et non un nombre, donc le contenu ne sera pas directement exploitable dans des calculs.
Voici une formule à coller dans la colonne retard, en supposant que les premières données soient en ligne 2 :
=SI(A2-B2<0;"-";) & TEXTE(ABS(A2-B2);"hh:mm:ss")Cordialement.
Mike-31
Messages postés
18188
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
13 mars 2023
5 041
8 févr. 2013 à 08:43
8 févr. 2013 à 08:43
Salut,
Si Excel gère avec le calendrier 1904 mais cela implique quelques règles.
Il faut que le fichier soit créé avec l'activation de la fonction calendrier 1904 avant toute saisie
le fichier en question ne doit pas avoir des liaisons avec d'autres fichiers travaillant avec le calendrier 1900 ou recevoir d'information date ou heure venant d'autre sources
Si c'est le cas il n'y a pas de problème sinon il faudra se contenter de formules qui formateront les cellules en texte
Si Excel gère avec le calendrier 1904 mais cela implique quelques règles.
Il faut que le fichier soit créé avec l'activation de la fonction calendrier 1904 avant toute saisie
le fichier en question ne doit pas avoir des liaisons avec d'autres fichiers travaillant avec le calendrier 1900 ou recevoir d'information date ou heure venant d'autre sources
Si c'est le cas il n'y a pas de problème sinon il faudra se contenter de formules qui formateront les cellules en texte
chossette9
Messages postés
4239
Date d'inscription
lundi 20 avril 2009
Statut
Contributeur
Dernière intervention
12 septembre 2014
1 303
8 févr. 2013 à 08:44
8 févr. 2013 à 08:44
Salut mike,
j'oublie toujours cette histoire de fonction calendrier 1904. Merci de m'avoir rafraîchi la mémoire :-)
j'oublie toujours cette histoire de fonction calendrier 1904. Merci de m'avoir rafraîchi la mémoire :-)
vigie123
Messages postés
18
Date d'inscription
samedi 3 novembre 2012
Statut
Membre
Dernière intervention
26 avril 2013
8 févr. 2013 à 10:08
8 févr. 2013 à 10:08
excusez mon ignorance mais cela veut dire quoi : l'activation de la fonction calendrier 1904
effectivement la feuil importe d'une autre feuil du même fichier à l'aide de la formule RECHERCHE V
peut être que je me suis mal exprimé mais est qu'il y a moyen de vous joindre le fichier?
effectivement la feuil importe d'une autre feuil du même fichier à l'aide de la formule RECHERCHE V
peut être que je me suis mal exprimé mais est qu'il y a moyen de vous joindre le fichier?
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Mike-31
Messages postés
18188
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
13 mars 2023
5 041
8 févr. 2013 à 16:51
8 févr. 2013 à 16:51
et et Re,
pour joindre ton fichier avec ce lien, clic dessus/Parcourir pour sélectionner ton fichier/créer le lien et colle le lien généré dans un post, je t'expliquerai comment activer et la différence entre le calendrier 1900 et 1904 sous Excel.
https://www.cjoint.com/
Quel est ta version Excel
pour joindre ton fichier avec ce lien, clic dessus/Parcourir pour sélectionner ton fichier/créer le lien et colle le lien généré dans un post, je t'expliquerai comment activer et la différence entre le calendrier 1900 et 1904 sous Excel.
https://www.cjoint.com/
Quel est ta version Excel
vigie123
Messages postés
18
Date d'inscription
samedi 3 novembre 2012
Statut
Membre
Dernière intervention
26 avril 2013
9 févr. 2013 à 00:25
9 févr. 2013 à 00:25
Salut Mike,
J'ai joint le fichier,c'est Excel 2007.
le Lien est : https://www.cjoint.com/?0Bjav73dbQp
J'ai colorifié les cellules en rouge, SVP si c'est possible d'evité les ### ou #valeur! en cas ou il n y a pas de résultat (La formule Sierreur ne m'a pas donné de résultat).
Merci beaucoup
J'ai joint le fichier,c'est Excel 2007.
le Lien est : https://www.cjoint.com/?0Bjav73dbQp
J'ai colorifié les cellules en rouge, SVP si c'est possible d'evité les ### ou #valeur! en cas ou il n y a pas de résultat (La formule Sierreur ne m'a pas donné de résultat).
Merci beaucoup
Mike-31
Messages postés
18188
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
13 mars 2023
5 041
9 févr. 2013 à 07:21
9 févr. 2013 à 07:21
Re,
apparemment il n'y a pas de problème sur ton fichier pour activer le calendrier 1904, mais n'alimente pas un autre fichier avec le calendrier 1900 sinon tes résultats risque de te surprendre.
sur Excel 2007 clic sur le bouton Microsoft en haut à droite/Option Excel en bas à droite/dans la liste colonne de gauche Options Avancées/dans la rubrique pratiquement en fin de liste Lors du calcul de ce classeur coche Utiliser le calendrier depuis 1904 et OK
contrôle tes données.
dans la cellule M8 comme N8, P8, Q8r8 et S8 pour éviter d'avoir le message d'erreur #VALEUR dû aux cellules vides
modifie tes formules exemple pour M8
=SI(ET(H8<>"";I8<>"");I8-H8;"")
ou comme cela
=SI(ESTERREUR(I8-H8);"";I8-H8)
ensuite incrémente vers le haut pour adapter la formule des cellule de tes plages
apparemment il n'y a pas de problème sur ton fichier pour activer le calendrier 1904, mais n'alimente pas un autre fichier avec le calendrier 1900 sinon tes résultats risque de te surprendre.
sur Excel 2007 clic sur le bouton Microsoft en haut à droite/Option Excel en bas à droite/dans la liste colonne de gauche Options Avancées/dans la rubrique pratiquement en fin de liste Lors du calcul de ce classeur coche Utiliser le calendrier depuis 1904 et OK
contrôle tes données.
dans la cellule M8 comme N8, P8, Q8r8 et S8 pour éviter d'avoir le message d'erreur #VALEUR dû aux cellules vides
modifie tes formules exemple pour M8
=SI(ET(H8<>"";I8<>"");I8-H8;"")
ou comme cela
=SI(ESTERREUR(I8-H8);"";I8-H8)
ensuite incrémente vers le haut pour adapter la formule des cellule de tes plages
vigie123
Messages postés
18
Date d'inscription
samedi 3 novembre 2012
Statut
Membre
Dernière intervention
26 avril 2013
9 févr. 2013 à 19:23
9 févr. 2013 à 19:23
Merci beaucoup pour votre aide,ca m'a vraiment aidé pour la colone M et N.
SVP voila les problemes que je rencontre :
EX :
1-Cellule P5 : =SI(H5<J5;J5-H5;"") la condition est H5<J5 ce qui n'est pas le cas mais malgré ca il donne ### au lieu du Vide, et dans la cellule P6 normalement ca doit présenté un résultat par ce que vraiment H6<J6??? et sur la colone R c'est le meme probleme sauf que c'est a l'inverse I5<K5.
Meme si j'essaie d'optimiser est metre rien que J6-H6 ca donne pas de résultat???
2-Cellule Q6 par ex : =SI(H6>J6;"";H6-J6) aussi la condition de > c'est comme s'il n'est pas pris en concidération.
En fait je vous met ci-dessous le lien du fichier après modification aussi j'ai mis just a coté du tableau un second comme exemple des résultats que je souhaite avoir.
https://www.cjoint.com/?0BjtwD5ylVd
Merci de votre aide.
SVP voila les problemes que je rencontre :
EX :
1-Cellule P5 : =SI(H5<J5;J5-H5;"") la condition est H5<J5 ce qui n'est pas le cas mais malgré ca il donne ### au lieu du Vide, et dans la cellule P6 normalement ca doit présenté un résultat par ce que vraiment H6<J6??? et sur la colone R c'est le meme probleme sauf que c'est a l'inverse I5<K5.
Meme si j'essaie d'optimiser est metre rien que J6-H6 ca donne pas de résultat???
2-Cellule Q6 par ex : =SI(H6>J6;"";H6-J6) aussi la condition de > c'est comme s'il n'est pas pris en concidération.
En fait je vous met ci-dessous le lien du fichier après modification aussi j'ai mis just a coté du tableau un second comme exemple des résultats que je souhaite avoir.
https://www.cjoint.com/?0BjtwD5ylVd
Merci de votre aide.
Bonsoir à tous, Bonsoir Vigie,
J'ai regardé ton tableau et j'ai constaté que les valeurs et les formats ne sont pas cohérents.
On lit : I5 = 17:00:41 en format hh:mm:ss
et si on passe en format standard I5 = 41301,70881
Ce qui peut ne pas poser de problème puisque Excel convertit les nombres standard en date et heures.
Mais en rapport on lit : K5 = 17:00 en format hh:mm
et en passant en format standard K5 = 17:00 ce qui est qqch mais certainement pas une heure en enregistrement Excel.
Dès lors, quand tu fait l'opération K5 - I5 il est normal que tu aies = ####
Bien évidemment je n'ai rien touché à tes expressions qui sont bien trop compliquées pour moi, mais je pense que tu devrais revoir la cohérence de tes formats qui est manifestement défaillante.
Bonne suite.
J'ai regardé ton tableau et j'ai constaté que les valeurs et les formats ne sont pas cohérents.
On lit : I5 = 17:00:41 en format hh:mm:ss
et si on passe en format standard I5 = 41301,70881
Ce qui peut ne pas poser de problème puisque Excel convertit les nombres standard en date et heures.
Mais en rapport on lit : K5 = 17:00 en format hh:mm
et en passant en format standard K5 = 17:00 ce qui est qqch mais certainement pas une heure en enregistrement Excel.
Dès lors, quand tu fait l'opération K5 - I5 il est normal que tu aies = ####
Bien évidemment je n'ai rien touché à tes expressions qui sont bien trop compliquées pour moi, mais je pense que tu devrais revoir la cohérence de tes formats qui est manifestement défaillante.
Bonne suite.
vigie123
Messages postés
18
Date d'inscription
samedi 3 novembre 2012
Statut
Membre
Dernière intervention
26 avril 2013
10 févr. 2013 à 21:00
10 févr. 2013 à 21:00
Bonjour,
Merci beaucoup pour vos efforts, ca donne des résultats négatif en -00:00:00 par exemple en essayons le calendrier 1904, mais la colonne J et K importe les heures d'une autre feuille avec le format 00:00 et même si nous changeons le format en 00:00:00 ca change pas, j'ai même testé d'enlevé la formule K5 en mettant 18:00:00 manuellement pour forcer le format sauf que ca donne : K5-I5 (18:00:00-17:00:41) = -23:00:41 au lieu de 00:59:19.
Ce que j'ai fais j'ai utilisé la formule (TEXTE(........;"hh:mm:ss") pour obliger les cellules a se mettre a cette format (elle s'est redrésser)( Mais malgré cela les même résultats, coté cellules elle ne prennent toujours pas en considération la condition < ou > ex: H5<J5 pour calculer ... ???
:) Nous avons pu avancer un peu mais mon problème n'est pas résolu tant que les résultats ne ressemblent pas au tableau en bleu à droite.
Je vous joins encore le fichier avec les dernières modifications, si pouvez m'aider a résoudre se cauchemar :) (tout le reste des colonnes que je vais ajouté après se base sur ses 4 colonnes).
https://www.cjoint.com/c/CBku7T2UTVZ
Merci d'avance
Merci beaucoup pour vos efforts, ca donne des résultats négatif en -00:00:00 par exemple en essayons le calendrier 1904, mais la colonne J et K importe les heures d'une autre feuille avec le format 00:00 et même si nous changeons le format en 00:00:00 ca change pas, j'ai même testé d'enlevé la formule K5 en mettant 18:00:00 manuellement pour forcer le format sauf que ca donne : K5-I5 (18:00:00-17:00:41) = -23:00:41 au lieu de 00:59:19.
Ce que j'ai fais j'ai utilisé la formule (TEXTE(........;"hh:mm:ss") pour obliger les cellules a se mettre a cette format (elle s'est redrésser)( Mais malgré cela les même résultats, coté cellules elle ne prennent toujours pas en considération la condition < ou > ex: H5<J5 pour calculer ... ???
:) Nous avons pu avancer un peu mais mon problème n'est pas résolu tant que les résultats ne ressemblent pas au tableau en bleu à droite.
Je vous joins encore le fichier avec les dernières modifications, si pouvez m'aider a résoudre se cauchemar :) (tout le reste des colonnes que je vais ajouté après se base sur ses 4 colonnes).
https://www.cjoint.com/c/CBku7T2UTVZ
Merci d'avance
eriiic
Messages postés
24513
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
25 mars 2023
7 168
10 févr. 2013 à 21:59
10 févr. 2013 à 21:59
Bonjour,
Et si tu tenais compte des remarques qui sont faites ?
En Sign-On!D4 par exemple tu n'as pas 11:59:08 mais 28/01/2017 11:59:08 c'est à dire 41301.4993981481 et non pas 0.4993981481.
Si tu ne veux que les heures : =mod(D4;1)
Et oublie le calendrier 1904.
Si tu as des résultats qui peuvent être négatifs tu utilises une colonne qui sera masquée mais utilisée par les calculs, et une autre pour formater l'affichage (mais qui ne servira pas aux calculs) avec par exemple :
=SI(C1<0;"-";"")&TEXTE(ABS(C1);"[h]:mm")
eric
Et si tu tenais compte des remarques qui sont faites ?
En Sign-On!D4 par exemple tu n'as pas 11:59:08 mais 28/01/2017 11:59:08 c'est à dire 41301.4993981481 et non pas 0.4993981481.
Si tu ne veux que les heures : =mod(D4;1)
Et oublie le calendrier 1904.
Si tu as des résultats qui peuvent être négatifs tu utilises une colonne qui sera masquée mais utilisée par les calculs, et une autre pour formater l'affichage (mais qui ne servira pas aux calculs) avec par exemple :
=SI(C1<0;"-";"")&TEXTE(ABS(C1);"[h]:mm")
eric
Bonsoir à tous,
Je ne peux qu'appuyer la remarque d'Eric. En plus je me permettrai d'insister sur le fait que tes formules emboitées sont bien trop compliquées pour que tu arrives à suivre ce qui se passe. Tu as des expressions d'heures qui sont en fait des dates (jj/mm/aaaa hh:mm:ss) et d'autres qui sont du texte mais qui apparaissent en heures (hh:mm).
Aussi je permets une suggestion: mettre en format "standard" les cellules qui manifestement posent problèmes, arranger tes formules, et à la fin seulement remettre au format horaire, il sera toujours temps.
Une autre possibilité est de mettre des résultats intermédiaires dans des cellules masquées ou non, mais qui te permettraient de suivre l'évolution des calculs, plutôt que trainer des formules de 3 ou 4 lignes. (C'est bien masculin ça (LOL))
Bonne suite.
Je ne peux qu'appuyer la remarque d'Eric. En plus je me permettrai d'insister sur le fait que tes formules emboitées sont bien trop compliquées pour que tu arrives à suivre ce qui se passe. Tu as des expressions d'heures qui sont en fait des dates (jj/mm/aaaa hh:mm:ss) et d'autres qui sont du texte mais qui apparaissent en heures (hh:mm).
Aussi je permets une suggestion: mettre en format "standard" les cellules qui manifestement posent problèmes, arranger tes formules, et à la fin seulement remettre au format horaire, il sera toujours temps.
Une autre possibilité est de mettre des résultats intermédiaires dans des cellules masquées ou non, mais qui te permettraient de suivre l'évolution des calculs, plutôt que trainer des formules de 3 ou 4 lignes. (C'est bien masculin ça (LOL))
Bonne suite.