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
Bonjour,

SVP, J'ai besoin d'aide, ca parait assez simple mais je ne comprend pas pourquoi Excel ne réagis pas???

Log Planif Log Avant Retard
Shift"
09:04:03 09:00 00:04:03
08:59:58 09:00 #####

pour avoir le retard j'ai mis : log - planif (ca fonctionne)
mais pour avoir le log avant shift j'ai mis : planif - log (ca fonctionne pas ca donne que ####) sachant que le format et bonne ?????
Est ce qu'on peut avoir une heure négative sur excel (EX : genre - 01:58:40 ) ???

SVP si vous pouvez m'aidé sur ces deux problématique.
Merci d'avance.

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
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
2
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
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 :
=SI(A2-B2<0;"-";) & TEXTE(ABS(A2-B2);"hh:mm:ss")
Cordialement.
0
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
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
0
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
Salut mike,

j'oublie toujours cette histoire de fonction calendrier 1904. Merci de m'avoir rafraîchi la mémoire :-)
0
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
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?
0

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
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
0
Suis intéressé moi aussi.
0
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
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
0
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
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

0
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
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.
0
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.
0
Confirmation : Après avoir changé les formules par des constantes en J et K , les choses rentrent bien dans l'ordre. En fait je suis porté à penser que les formules sont si compliquées qu'on ne sait plus ce qu'elles sont; elles ressemblent à des heures mais n'en sont pas.
0
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
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
0
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
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
0
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.
0