Calculer des heures sup avec Excel, Comment entrer une opération "différence d'heures"

[Arnozen]

Hello à tous,

J'ai besoin de construire un fichier excel pour calculer les heures sup des gens de mon service.

Je ne trouve pas la formule qui me calculerait les "différences d'heures". Je m'explique :

- les horaires normaux sont 9h24 le matin et 18h le soir.

Il faut donc que je puisse calculer les heures faites en plus le soir, mais ça, ce n'est pas un problème.

Par contre, si quelqu'un arrive à 9h30 au lieu de 9h24, il me faudrait une formule pour calculer la différence....


voilà voilà, j'espère être suffisamment clair !


et merci d'avance aux bêtes d'Excel !

[Aliboron]

si quelqu'un arrive à 9h30 au lieu de 9h24, il me faudrait une formule pour calculer la différence...

Beh, a priori, c'est assez simple. Mettons qu'en A1 tu saisisses 9:30 et en A2 9:24. En A3, tu saisis =A1-A2 et tu valides (touche "Entrée") : tu lis 0:06 dans la cellule, ça tombe bien, c'est justement la différence. Les choses se compliquent (un peu) pour les heures de nuit mais bon...

[Arnozen]

Merci beaucoup. En fait, ça j'y arrive sans problème.

Mais je ne sais pas comment faire mon total en fait.

Car en fait, si le mec est arrivé à 9h30 au lieu de 9h24, il faut que je puisse retrancher les 6 minutes au total d'heures sup.

Du coup, je cherche une formule ou je ferai 9h30 – 9h24 et qui me donnerai un résultat négatif, pour que ça puisse se soustraire au résultat final...


je sais c'est chiant !

[GeorgesV]

Bonjour,

Petite solution fantaisiste :

[attachment=41789:Image_1.png]

[Arnozen]

Waow ! comment tu as fais ?

car c'est justement ce que je n'arrive pas à faire...

à la place du résultat, il me met ############

alors qu'il devrait me mettre –0:06

[Arnozen]

En fait, pour essayer d'être plus clair, je ne sais pas comment rentrer les horaires normaux, et ensuite en entrant les horaires faits par le mec, pour que ça me calcule les heures en plus et les heures en moins...

J'essaie de joindre une image de là ou j'en suis


 Capture_d_____cran_2012_09_18____16.23.01.png ( 21.05 Ko ) Nombre de téléchargements : 40

[GeorgesV]

Comme tout le monde :

As-tu entré les heures suivant le format proposé par notre maître à tous (Aliboron) ?

[attachment=41795:Image_2.png]

Ta formule pour Heure sup matin n'est pas correcte puisqu'il a osé arriver en retard le 31

Cliques sur la colonne pour l'élargir et voir son contenu.

[Arnozen]

Ben oui, justement ! le bougre a osé arriver 4 minutes en retard... et comme il est assez débile pour me le compter quand il fait 3 minutes en plus le soir... évidemment il me décompte quand il arrive à la bourre, même de 3 minutes... Bref, ce n'est pas le sujet...


En fait, moi ce que je voudrai dans ma formule, c'est pouvoir lui décompter les minutes quand il arrive en retard, et lui ajouter les minutes quand il part plus tard....

Et c'est là ou je galère...

Quand j'élargis la colonne, il y a environ 3000 #...

[Arnozen]

a y est ! j'ai trouvé un système je crois...

mais il me manque un autre truc maintenant !

Quand j'aditionne toutes les heures, si ça dépasse 24 heures, il repart à zéro. Par exemple, si j'aditionne 20 heures et 10 heures, il me donne 6 heures... (24h + 6h)...


quelqu'un a l'astuce pour ça ?

[GeorgesV]

Petite remarque : il arrive le matin et le soir ?

Si le format est correct je ne vois pas.

[attachment=41801:Image_3.png]

[GeorgesV]

Pour le total des heures choisir le format : [hh]"h"mm;[rouge]"-"[hh]"h"mm;0

[Aliboron]

à la place du résultat, il me met ############
alors qu'il devrait me mettre –0:06

C'est peut-être (probablement ?) parce que ton classeur est paramétré pour utiliser le système de calendrier base 1900 (le système utilisé dans la version Windows), lequel n'admet pas les nombres négatifs (pour faire simple). Vérifie dans les préférences d'Excel, onglet "Calcul" que la case "Calendrier depuis 1904" est bien cochée.

Quand j'aditionne toutes les heures, si ça dépasse 24 heures, il repart à zéro. Par exemple, si j'aditionne 20 heures et 10 heures, il me donne 6 heures... (24h + 6h)...

Normal, le format d'affichage ne te montre que les heures et les minutes. Tu pourrais le forcer à mettre aussi les jours mais, pour un calcul d'heures sup, il faut bien dire que ce n'est pas très commode. Donc le mieux est de forcer l'affichage en heures. Pour cela, tu vas dans le menu "Format" > "Cellule..." > "Nombres" et tu crées le format personnalisé [h]:mm ce qui aura pour effet de forcer l'affichage en heures, même au-delà de 24.

[poissonfree]

Je ne sais pas quelle version d'excel tu utilises, mais ce que je sais et j'en suis sûr, excel ne sait pas faire un calcul négatif d'heure! (au boulot j'ai la version 2002 donc sous windows XP)
Je le sais trop bien, je me suis fait chier pendant plusieurs mois pour mon tableau de congés qui n'utilise absolument pas de macro et de vba.

L'astuce consiste à calculer en positif et d'utiliser la fonction concatener pour y mettre un "-" devant ton heure.
Bon par contre, si tu comptes repartir de cette heure négative, il faut encore une fois gruger, en utilisant la fonction gauche (ou droite) pour sortir ce "-" et calculer en positif (encore une fois) et y adjoindre le "-" si nécessaire.

Bref, faut faire des conversions.
Bon par contre, par moment il peut arriver que ça merde, mais bizarement le calcul suivant se fait sans problème -> les joies d'excel

Ne pas oublier d'utiliser les fonctions de test (je ne parle pas de la fonction si qui est autre chose), très très utile!

Après, tu peux très bien utiliser les fonctions conditionnelles pour faire apparaître en rouge (et gras) quand c'est en négatif.

[Aliboron]

.../... ce que je sais et j'en suis sûr, excel ne sait pas faire un calcul négatif d'heure ! (au boulot j'ai la version 2002 donc sous windows XP)

Erreur, mon cher poisson ! Excel 2002 (comme les autres versions pour Windows) sait très bien faire un calcul négatif d'heures, sous réserve d'aller dans "Outils" > "Options…" > "Calcul" et d'y cocher la case "Calendrier depuis 1904"…

C'est que le format par défaut des versions Windows est le calendrier depuis 1900 (hérité de Lotus, pour la petite histoire, mais n'entrons pas une fois de plus dans les détails) qui n'admet pas les résultats négatifs de dates (et d'heures, du coup). Mais le format de calendrier depuis 1904 (celui du Macintosh) qui y a été incorporé en option lorsque l'échange de classeur est devenu possible le permet sans problème. Il faut juste aller cocher la case ad hoc sous Windows (et sur Mac OS X, s'assurer qu'elle n'est pas décochée).

[Arnozen]

Merciiii à tous !

avec tous vos précieux renseignements, j'ai finalement réussi un superbe doc excel, avec toutes les heures du mec, à la minute près, les récup x 25% etc

franchement ça le fait


merci merci!

[poissonfree]

.../... ce que je sais et j'en suis sûr, excel ne sait pas faire un calcul négatif d'heure ! (au boulot j'ai la version 2002 donc sous windows XP)

Erreur, mon cher poisson ! Excel 2002 (comme les autres versions pour Windows) sait très bien faire un calcul négatif d'heures, sous réserve d'aller dans "Outils" > "Options…" > "Calcul" et d'y cocher la case "Calendrier depuis 1904"…

C'est que le format par défaut des versions Windows est le calendrier depuis 1900 (hérité de Lotus, pour la petite histoire, mais n'entrons pas une fois de plus dans les détails) qui n'admet pas les résultats négatifs de dates (et d'heures, du coup). Mais le format de calendrier depuis 1904 (celui du Macintosh) qui y a été incorporé en option lorsque l'échange de classeur est devenu possible le permet sans problème. Il faut juste aller cocher la case ad hoc sous Windows (et sur Mac OS X, s'assurer qu'elle n'est pas décochée).

OK, je regarderai ça.
Mais j'ai une question.
Comme on est au boulot et que le service informatique ne veut pas trop se faire chier, genre on réinstalle tout le système (windows + office/outlook) + les applications métiers. Le problème c'est à l'utilisateur de se taper tout le paramètrage de chaque appli et franchement c'est long car on ne pense à pas tout et que généralement on a un dossier urgent à rendre, tant qu'à faire hein!
Dans cette optique, si je modifie l'option chez moi, est-ce que quand je passe mon fichier à un autre il aura les heures négatives ou pas ?
Dans le cas, où l'option n'est pas gardée, ma "solution" n'est pas à négliger

Car c'est déjà le cas pour les macros où par défaut c'est désactivé. Pour ma part je mets sur demander à chaque fois, mais y'a des crétins qui autorisent tout le temps car ils sont fan des macros, bref une autre histoire.

[Aliboron]

Dans cette optique, si je modifie l'option chez moi, est-ce que quand je passe mon fichier à un autre il aura les heures négatives ou pas ?

Le choix du type de calendrier est mémorisé dans le classeur. Ce qui donne parfois des résultats amusant lorsqu'on copie d'un classeur "1904" vers ou depuis un classeur "1900" : les dates qu'on colle sont décalées de quatre années et un jour. Sauf depuis les toutes dernières versions d'Excel où, enfin, il y a auto-détection et auto-adaptation...

[poissonfree]

Le choix du type de calendrier est mémorisé dans le classeur....

Bon à savoir.
Mais y'a t'il une fonction (purement excel, car je n'utilise pas le vba) pour tester le type de calendrier ?
Je regarde en attendant!

[Aliboron]

En exploitant une des différences entre les deux calendriers, c'est assez facile. Par exemple le calendrier 1900 accepte le zéro janvier 1900 et le 29 février 1900, dates qui n'existent évidemment pas (c'est d'ailleurs probablement pour éviter ces pièges que Apple a choisi de faire démarrer son calendrier au 01/01/1904). Par exemple avec la formule suivante : =SI(ESTERREUR(DATE(0;1;59));"Calendrier 1904";"Calendrier 1900")

En VBA, c'est une fonction assez simple, puisqu'on peut la propriété correspondante est en lecture et écriture. On peut donc déterminer par une instruction le format choisi, par ex : ActiveWorkbook.Date1904 = True


Pour conclure (?) voici un lien vers un fil qui traitait de ce problème il y a bien longtemps (huit ans, déjà !) et dans lequel il y a (tout en bas) un lien vers une version utilisable avec les versions Office d'une macro de guillôme facilitant la cohabitation des classeurs 1900 et 1904...

Ce message a été modifié par Aliboron - 19 Sep 2012, 13:14.

[Arnozen]

à la place du résultat, il me met ############
alors qu'il devrait me mettre –0:06

C'est peut-être (probablement ?) parce que ton classeur est paramétré pour utiliser le système de calendrier base 1900 (le système utilisé dans la version Windows), lequel n'admet pas les nombres négatifs (pour faire simple). Vérifie dans les préférences d'Excel, onglet "Calcul" que la case "Calendrier depuis 1904" est bien cochée.

Quand j'aditionne toutes les heures, si ça dépasse 24 heures, il repart à zéro. Par exemple, si j'aditionne 20 heures et 10 heures, il me donne 6 heures... (24h + 6h)...

Normal, le format d'affichage ne te montre que les heures et les minutes. Tu pourrais le forcer à mettre aussi les jours mais, pour un calcul d'heures sup, il faut bien dire que ce n'est pas très commode. Donc le mieux est de forcer l'affichage en heures. Pour cela, tu vas dans le menu "Format" > "Cellule..." > "Nombres" et tu crées le format personnalisé [h]:mm ce qui aura pour effet de forcer l'affichage en heures, même au-delà de 24.

TOP pour le calendrier 1904, ça m'a mis mon fameux résultat négatif... excellent !


Allez autre, question, je voudrais que si un résultat négatif apparaît dans une cellule, ça me le traduise par un 0:00 dans la cellule suivante.

Etant donnée qu'il s'agit d'un calcul d'heures supplémentaires, si la personne a fait "moins 1 h", en récup, ça lui donne 0 heures, forcément....


Merci merci !

[Aliboron]

je voudrais que si un résultat négatif apparaît dans une cellule, ça me le traduise par un 0:00 dans la cellule suivante.

Ben, a priori, c'est un basique =SI(Condition;Résultat si oui;Résultat si non) qu'il faut utiliser. Par ex. :
=SI(C1<0;0;C1)

Non ?