Calculer des heures sup avec Excel, Comment entrer une opération "différence d'heures" |
Bienvenue invité ( Connexion | Inscription )
Calculer des heures sup avec Excel, Comment entrer une opération "différence d'heures" |
18 Sep 2012, 12:49
Message
#1
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
18 Sep 2012, 13:26
Message
#2
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 4 197 Inscrit : 1 Jan 2008 Lieu : Toulouse.cong Membre no 103 942 |
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... -------------------- |
|
|
18 Sep 2012, 14:30
Message
#3
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
18 Sep 2012, 14:45
Message
#4
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 10 122 Inscrit : 28 Aug 2004 Lieu : Villers-La-Ville (Belgique) Membre no 22 631 |
Bonjour,
Petite solution fantaisiste : [attachment=41789:Image_1.png] -------------------- IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.12.6
* HP OfficeJet Pro 9012 - Airport extrème - iPhone 5 - IOS 10.3.4 - iPhone 5s - IOS 12.5.5 - Apple TV Système 8.4.2 - Logiciel 7.2.2 - iPad Air 2 - iPadOS 15.4.0 |
|
|
18 Sep 2012, 14:53
Message
#5
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
18 Sep 2012, 15:29
Message
#6
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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 Capture_d_____cran_2012_09_18____16.23.01.png ( 21.05 Ko ) Nombre de téléchargements : 40 -------------------- Macbook Pro 15" 2.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
18 Sep 2012, 15:43
Message
#7
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 10 122 Inscrit : 28 Aug 2004 Lieu : Villers-La-Ville (Belgique) Membre no 22 631 |
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. -------------------- IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.12.6
* HP OfficeJet Pro 9012 - Airport extrème - iPhone 5 - IOS 10.3.4 - iPhone 5s - IOS 12.5.5 - Apple TV Système 8.4.2 - Logiciel 7.2.2 - iPad Air 2 - iPadOS 15.4.0 |
|
|
18 Sep 2012, 15:52
Message
#8
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
18 Sep 2012, 16:19
Message
#9
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
18 Sep 2012, 16:23
Message
#10
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 10 122 Inscrit : 28 Aug 2004 Lieu : Villers-La-Ville (Belgique) Membre no 22 631 |
Petite remarque : il arrive le matin et le soir ?
Si le format est correct je ne vois pas. [attachment=41801:Image_3.png] -------------------- IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.12.6
* HP OfficeJet Pro 9012 - Airport extrème - iPhone 5 - IOS 10.3.4 - iPhone 5s - IOS 12.5.5 - Apple TV Système 8.4.2 - Logiciel 7.2.2 - iPad Air 2 - iPadOS 15.4.0 |
|
|
18 Sep 2012, 16:44
Message
#11
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 10 122 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
-------------------- IMac 27 - 3.4 GHz - Mémoire 16 GB - Fusion + 2 thunderbold 3 To + 1 USB 1 To - OsX 10.12.6
* HP OfficeJet Pro 9012 - Airport extrème - iPhone 5 - IOS 10.3.4 - iPhone 5s - IOS 12.5.5 - Apple TV Système 8.4.2 - Logiciel 7.2.2 - iPad Air 2 - iPadOS 15.4.0 |
|
|
18 Sep 2012, 16:55
Message
#12
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 4 197 Inscrit : 1 Jan 2008 Lieu : Toulouse.cong Membre no 103 942 |
à 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. -------------------- |
|
|
18 Sep 2012, 17:26
Message
#13
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 1 997 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 -> 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. -------------------- |
|
|
18 Sep 2012, 17:51
Message
#14
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 4 197 Inscrit : 1 Jan 2008 Lieu : Toulouse.cong Membre no 103 942 |
.../... 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). -------------------- |
|
|
18 Sep 2012, 18:21
Message
#15
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 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.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
19 Sep 2012, 06:14
Message
#16
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 1 997 Inscrit : 4 Jun 2007 Membre no 87 884 |
.../... 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). 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. -------------------- |
|
|
19 Sep 2012, 07:48
Message
#17
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 4 197 Inscrit : 1 Jan 2008 Lieu : Toulouse.cong Membre no 103 942 |
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... -------------------- |
|
|
19 Sep 2012, 08:54
Message
#18
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 1 997 Inscrit : 4 Jun 2007 Membre no 87 884 |
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! -------------------- |
|
|
19 Sep 2012, 13:02
Message
#19
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 4 197 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. -------------------- |
|
|
21 Jan 2013, 18:19
Message
#20
|
|
Adepte de Macbidouille Groupe : Membres Messages : 120 Inscrit : 21 Sep 2007 Lieu : Paris Membre no 95 273 |
à 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 ! -------------------- Macbook Pro 15" 2.3 GHz Intel Core i7 - OS 10.9.5
iPhone 5S |
|
|
22 Jan 2013, 01:07
Message
#21
|
|
Macbidouilleur d'Or ! Groupe : Membres Messages : 4 197 Inscrit : 1 Jan 2008 Lieu : Toulouse.cong Membre no 103 942 |
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 ? -------------------- |
|
|
Nous sommes le : 18th April 2024 - 03:07 |