IPB

Bienvenue invité ( Connexion | Inscription )

 
Reply to this topicStart new topic
> Calculer des heures sup avec Excel, Comment entrer une opération "différence d'heures"
Options
Arnozen
posté 18 Sep 2012, 12:49
Message #1


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



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 !



--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
Aliboron
posté 18 Sep 2012, 13:26
Message #2


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 3 601
Inscrit : 1 Jan 2008
Lieu : Toulouse.cong
Membre no 103 942



Citation (Arnozen @ 18 Sep 2012, 13:49) *
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...


--------------------
Bernard

MacMini, iMac et tout un tas d'accessoires -- FAQ Office Macintosh
Go to the top of the page
 
+Quote Post
Arnozen
posté 18 Sep 2012, 14:30
Message #3


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



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 !



--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
GeorgesV
posté 18 Sep 2012, 14:45
Message #4


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 8 441
Inscrit : 28 Aug 2004
Lieu : Villers-La-Ville (Belgique)
Membre no 22 631



Bonjour, wink.gif

Petite solution fantaisiste :

[attachment=41789:Image_1.png]


--------------------
IMac 24 - 3.06 GHz - Mémoire 4 GB - 500 GB + 1 USB 1 To - OsX 10.9.4 * HP Photosmart C6180
IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.9.5
* HP Photosmart Premium - Airport extrème - iPad 3 - IOS 8.0.2 - iPhone 5s IOS 8.0.2
Go to the top of the page
 
+Quote Post
Arnozen
posté 18 Sep 2012, 14:53
Message #5


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



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




--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
Arnozen
posté 18 Sep 2012, 15:29
Message #6


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273




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


Fichier joint  Capture_d_____cran_2012_09_18____16.23.01.png ( 21.05 Ko ) Nombre de téléchargements : 38


--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
GeorgesV
posté 18 Sep 2012, 15:43
Message #7


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 8 441
Inscrit : 28 Aug 2004
Lieu : Villers-La-Ville (Belgique)
Membre no 22 631



Comme tout le monde : laugh.gif

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

[attachment=41795:Image_2.png]

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

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


--------------------
IMac 24 - 3.06 GHz - Mémoire 4 GB - 500 GB + 1 USB 1 To - OsX 10.9.4 * HP Photosmart C6180
IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.9.5
* HP Photosmart Premium - Airport extrème - iPad 3 - IOS 8.0.2 - iPhone 5s IOS 8.0.2
Go to the top of the page
 
+Quote Post
Arnozen
posté 18 Sep 2012, 15:52
Message #8


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



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 #...



--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
Arnozen
posté 18 Sep 2012, 16:19
Message #9


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



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 ?



--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
GeorgesV
posté 18 Sep 2012, 16:23
Message #10


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 8 441
Inscrit : 28 Aug 2004
Lieu : Villers-La-Ville (Belgique)
Membre no 22 631



Petite remarque : il arrive le matin et le soir ? biggrin.gif

Si le format est correct je ne vois pas. wacko.gif

[attachment=41801:Image_3.png]


--------------------
IMac 24 - 3.06 GHz - Mémoire 4 GB - 500 GB + 1 USB 1 To - OsX 10.9.4 * HP Photosmart C6180
IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.9.5
* HP Photosmart Premium - Airport extrème - iPad 3 - IOS 8.0.2 - iPhone 5s IOS 8.0.2
Go to the top of the page
 
+Quote Post
GeorgesV
posté 18 Sep 2012, 16:44
Message #11


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 8 441
Inscrit : 28 Aug 2004
Lieu : Villers-La-Ville (Belgique)
Membre no 22 631



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


--------------------
IMac 24 - 3.06 GHz - Mémoire 4 GB - 500 GB + 1 USB 1 To - OsX 10.9.4 * HP Photosmart C6180
IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.9.5
* HP Photosmart Premium - Airport extrème - iPad 3 - IOS 8.0.2 - iPhone 5s IOS 8.0.2
Go to the top of the page
 
+Quote Post
Aliboron
posté 18 Sep 2012, 16:55
Message #12


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 3 601
Inscrit : 1 Jan 2008
Lieu : Toulouse.cong
Membre no 103 942



Citation (Arnozen @ 18 Sep 2012, 15:53) *
à 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.

Citation (Arnozen @ 18 Sep 2012, 17:19) *
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.


--------------------
Bernard

MacMini, iMac et tout un tas d'accessoires -- FAQ Office Macintosh
Go to the top of the page
 
+Quote Post
poissonfree
posté 18 Sep 2012, 17:26
Message #13


Macbidouilleur de vermeil !
****

Groupe : Membres
Messages : 1 394
Inscrit : 4 Jun 2007
Membre no 87 884



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 blink.gif -> 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.


--------------------
Go to the top of the page
 
+Quote Post
Aliboron
posté 18 Sep 2012, 17:51
Message #14


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 3 601
Inscrit : 1 Jan 2008
Lieu : Toulouse.cong
Membre no 103 942



Citation (poissonfree @ 18 Sep 2012, 18:26) *
.../... 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 ! wink.gif 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).


--------------------
Bernard

MacMini, iMac et tout un tas d'accessoires -- FAQ Office Macintosh
Go to the top of the page
 
+Quote Post
Arnozen
posté 18 Sep 2012, 18:21
Message #15


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



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!


--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
poissonfree
posté 19 Sep 2012, 06:14
Message #16


Macbidouilleur de vermeil !
****

Groupe : Membres
Messages : 1 394
Inscrit : 4 Jun 2007
Membre no 87 884



Citation (Aliboron @ 18 Sep 2012, 18:51) *
Citation (poissonfree @ 18 Sep 2012, 18:26) *
.../... 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 ! wink.gif 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 wink.gif

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.


--------------------
Go to the top of the page
 
+Quote Post
Aliboron
posté 19 Sep 2012, 07:48
Message #17


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 3 601
Inscrit : 1 Jan 2008
Lieu : Toulouse.cong
Membre no 103 942



Citation (poissonfree @ 19 Sep 2012, 07:14) *
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...


--------------------
Bernard

MacMini, iMac et tout un tas d'accessoires -- FAQ Office Macintosh
Go to the top of the page
 
+Quote Post
poissonfree
posté 19 Sep 2012, 08:54
Message #18


Macbidouilleur de vermeil !
****

Groupe : Membres
Messages : 1 394
Inscrit : 4 Jun 2007
Membre no 87 884



Citation (Aliboron @ 19 Sep 2012, 08:48) *
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!


--------------------
Go to the top of the page
 
+Quote Post
Aliboron
posté 19 Sep 2012, 13:02
Message #19


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 3 601
Inscrit : 1 Jan 2008
Lieu : Toulouse.cong
Membre no 103 942



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.


--------------------
Bernard

MacMini, iMac et tout un tas d'accessoires -- FAQ Office Macintosh
Go to the top of the page
 
+Quote Post
Arnozen
posté 21 Jan 2013, 18:19
Message #20


Adepte de Macbidouille
*

Groupe : Membres
Messages : 109
Inscrit : 21 Sep 2007
Lieu : Paris
Membre no 95 273



Citation (Aliboron @ 18 Sep 2012, 16:55) *
Citation (Arnozen @ 18 Sep 2012, 15:53) *
à 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.

Citation (Arnozen @ 18 Sep 2012, 17:19) *
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 !


--------------------
Macbook Pro 15" 2.2 GHz Intel Core i7 - 10.6.7/Window 7
iPhone 4 16 Go
iPad 16 Go
Go to the top of the page
 
+Quote Post
Aliboron
posté 22 Jan 2013, 01:07
Message #21


Macbidouilleur d'Or !
*****

Groupe : Membres
Messages : 3 601
Inscrit : 1 Jan 2008
Lieu : Toulouse.cong
Membre no 103 942



Citation (Arnozen @ 21 Jan 2013, 18:19) *
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 ?


--------------------
Bernard

MacMini, iMac et tout un tas d'accessoires -- FAQ Office Macintosh
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 utilisateur(s) sur ce sujet (1 invité(s) et 0 utilisateur(s) anonyme(s))
0 membre(s) :

 



Nous sommes le : 1st October 2014 - 09:19