Calculer les heures de nuit
bonjour, je suis tout nouveau.
j'ai cherché dans les différentes questions deja posées, mais je n'ai pas trouvé mon bonheur. Si vous avez une solution à mon problème, c'est volontier que j'accepte votre aide.
En A1 je rentre l'heure de ma prise de service (21:50).
En A2 je rentre l'heure de ma fin de service (01h15).
les heures de nuit sont de 21heures à 6heures.
je voudrais calculer en A3 le nombre d'heure de nuit que j'ai fait.
(à savoir que chaque heure entamée est du, c'est à dire de :
de 21h50 à 22h00 = 1 heure de nuit
de 22h00 à 23h00 = 1 heure de nuit
de 23h00 à 24h00 = 1 heure de nuit
de 00h00 à 01h00 = 1 heure de nuit
de 01h00 à 01h15 = 1 heure de nuit
au total j'ai fait 5 heures de nuit.)
j'ai essayé avec beaucoup de =SI() ET() OU()
mais j'en perds mon latin.
merci d'avance.
Cordialement.
Tri
Bonsoir
Si l'heure de début est en A1, et l'heure de fin en A2
Formule en A3 à incrémenter vers la droite
=ARRONDI.SUP((A2*24)-(A1*24);)
Format de cellule Nombre.
Cordialement
Salut Amadéus.
ta réponse est presque correcte, mais elle m'a mis sur la voie pour trouver la bonne solution.
- dans un 1ier temps le problème est si je commence avant une heure pleine,
(c'est à dire 21:55 par exemple) et que je fini à 23:00, l'heure entre 21:55 et 22:00 ne sera pas comptée.
donc grace à ta réponse j'ai trouvé la formule suivante.
Heure de début en A1
Heure de fin en A2
En A3: =ARRONDI.SUP((A2*24)-(ARRONDI.INF((A1*24);));)
- dans un 2ème temps, si je fini de travailler après minuit il faut faire attention à l'heure qu'on entre (si je fini à 01:30 du matin, il ne faut pas taper "01:30" mais "25:30").
En tout cas, merci beaucoup.
des fois on a beau chercher on trouve pas,
et puis un petit coup de pouce nous fait trouver la solution.
c'est pour ca que votre site est trés bien.
Continuez.
à bientôt. cordialement.
Tri
Je vais compléter la solution, car après réflexion cette formule calcul le nombre d'heure travaillé en arrondissant et non pas le nombre d'heure de nuit (compris entre 21heure et 6heure).
pour une question de compréhension voilà un fichier joint :
https://www.excel-pratique.com/~files/doc/feuille_heure_de_nuit.jpg
en A4 il faut mettre la formule suivante, elle est un peu longue avec beaucoup de parenthèses, mais c'est garantie elle marche.
=(SI(ET(A2<A7;A3<=A7);(ARRONDI.SUP((A3*24)-(ARRONDI.INF((A2*24);));));(SI(ET(A2<A7;A3>A7);((A7*24)-(ARRONDI.INF((A2*24);)));(SI(ET(A2<A8;A3>A8;A3<=A9);((ARRONDI.SUP((A3*24);))-(A8*24));(SI(ET(A2>=A8;A2<A9;A3>A8;A3<=A9);(ARRONDI.SUP((A3*24)-(ARRONDI.INF((A2*24);));));(SI(ET(A2>=A8;A2<A9;A3>A9);((A9*24)-(ARRONDI.INF((A2*24);)));(SI(ET(A2<A8;A3>A9);((A9*24)-(A8*24));0))))))))))))
voilà.
à bientôt
cordialementTri
C'est encore moi.
des smiles sont venues se mettre au milieu.
Désolé.
En A4 il faut mettre la formule suivante:
=(SI(ET(A3<A8;A4<=A8);(ARRONDI.SUP((A4*24)-(ARRONDI.INF((A3*24);));));(SI(ET(A3<A8;A4>A8);((A8*24)-(ARRONDI.INF((A3*24);)));(SI(ET(A3<A9;A4>A9;A4<=A10);((ARRONDI.SUP((A4*24);))-(A9*24));(SI(ET(A3>=A9;A3<A10;A4>A9;A4<=A10);(ARRONDI.SUP((A4*24)-(ARRONDI.INF((A3*24);));));(SI(ET(A3>=A9;A3<A10;A4>A10);((A10*24)-(ARRONDI.INF((A3*24);)));(SI(ET(A3<A9;A4>A10);((A10*24)-(A9*24));0))))))))))))
bye!
Tri
Tristan,
Je n'ai pas bien pu suivre ta dernière proposition de forumule, puisque tu sembles t'éloigner de ta première question (uniquement
1 heure en A1 et 1 heure en A2) et que le fichier que tu joins n'est pas un fichier Excel, mais une simple saisie d'écran sans formule.
J'avais moi-même essayé la formule proposée par Amadéus et j'avais des problèmes avec les heures après minuit (voir le fichier ci-joint). Par contre je te propose une autre formule qui semble moins compliquée que la tienne :
=SI(ET(HEURE(A2)<6;HEURE(A1)>6);(HEURE(A2)+25)-HEURE(A1);HEURE(A2)+1-HEURE(A1))
https://www.excel-pratique.com/~files/doc/Heures_de_nuit.xls
Est-ce que ça t'aide ?
Meilleures salutations.
salut!
c'est vrai que ma derniere explication n'est des plus claire.
je vais essayer de faire mieux.
En A1 je rentre l'heure de ma prise de service.
En A2 je rentre l'heure de ma fin de service (attention! si je fini après minuit, ex 1h15, il faut rentrer 25:15 (c'est à dire24h00 + 1h15) comme ca pas de problème pour le passage de minuit.
En A3 je rentre ma super formule un petit peu compliquée, c'est vrai.
Moi dans mon boulot, les heures de nuit commencent à 21heures et finissent à 6h00.
Ma formule fait référence à ces heures la, et comme ce n'est pas pareille dans tout les boulots, je préfère faire référence à des cellules dans lesquelles on précise les heures de nuit.
Pour régler le problème du passage de minuit, j'ai besoin de faire référence à 6heures le lendemain de ma prise de service, c'est à dire à 30heures (24h00 + 6h00) (minuit + 6heure)
donc dans mon exemple :
En A6 je rentre la 1ère heure de référence,
En A7 je rentre la 2ème heure de référence,
En A8 je rentre la 3ème heure de référence.
(si le fait de faire référence à c'est 3 cellules vous gêne, rentrez dans la formule et remplacez:
- A6 par 0,25 (en langage excel ca veut dire le 1/4 d'une journée, soit le 1/4 de 1)
- A7 par 0,875 (en langage excel ca veut dire 7/8 d'une journée, soit 7/8 de 1)
- A8 par 1,25 (en langage excel ca veut dire 1 jour + 6 heures))
j'ai essayé tout les cas de figure possible et tout marche. sauf si la prise de service est avant 6 heures et la fin de service après 21 heures.
Mais bon dans ce cas la, ca fait au minimum une amplitude de travaille de 15 heures. interdit par la législation du travail, et donc impossible.
Pour expliquer ma formule:
-Le 1ier "SI" correspond au cas :
-Prise de service entre 0heure et 6heures
-Fin de service entre 0heure et 6heures
-Le 2ème"SI" correspond au cas :
-Prise de service entre 0heure et 6heures
-Fin de service entre 6heure et 21heures
-Le 3ème "SI" correspond au cas :
-Prise de service entre 6heure et 21heures
-Fin de service entre 21heure et 6heures (le lendemain donc 30heures)
-Le 4ème "SI" correspond au cas :
-Prise de service entre 21heure et 6heures (le lendemain donc 30heures)
-Fin de service entre 21heure et 6heures (le lendemain donc 30heures)
-Le 5ème "SI" correspond au cas :
-Prise de service entre 21heure et 6heures (le lendemain donc 30heures)
-Fin de service après 6heures (le lendemain donc 30heures)
-Le 6ème et dernier "SI" correspond au cas :
-Prise de service entre 6heure et 21heures
-Fin de service après 6heures (le lendemain donc 30heures)
Voilà je pense cette fois avoir été relativement claire, pas évident je sais mais bon ca marche pour tout les cas.
ci-joint le fichier
https://www.excel-pratique.com/~files/doc/calculer_les_heures_de_nuit.xls
A bientôt
Tristan
Salut Tristan,
J'ai fait un essai avec ta feuille de calcul et j'obtiens le résultat suivant :
21:50 Prise de service
01:15 Fin de service
0 Total heure de nuit
Pour faire mes calcul avec si(), et(), ou()
06:00 fin heure de nuit
21:00 debut heure de nuit
06:00 fin heure de nuit si j'attaque à travailler la veille (30:00)
alors que tu désires un total de 5 heures de nuit.
As-tu regardé la formule que je te proposais dans mon dernier courrier ? Elle indique dans un tel cas un total de 5 heures de nuit. Et sans devoir faire référence à des cellules externes, ce qui simplifie quand même la formule, non ?
J'avais un doute au sujet du début et de la fin des heures, c'est pourquoi j'avais introduit des exemples extrêmes dans ma feuille : Est-ce bien correct qu'un début de travail à 23:00 par exemple avec une fin à 0:00 indique deux heures commencées ? D'après moi oui c'est correct. Si tu considères que ça ne correspond qu'à une seule heure, je pense que tu devrais introduire 23:00 - 23:59.
Dans l'espoir que tu jettes quand même un coup d'oeil à mon travail bénévole, je te présente mes meilleures salutations.
Salut Yvouille !
pour ma formule:
c'est parce qu'à ta fin de service, il ne faut pas rentrer 1:15, mais 25:15 (ce qui veut dire 24h00 + 1h15), et dans ce cas la ca marche.
pour ta formule à toi:
dans mon boulot, si je travaille de 00:00 à 01:00, on me compte 1 heure de nuit. si je travaille de 00:00 à 01:01, on me compte 2 heures de nuit.
Le problème avec ta formule, c'est qu'en fait ce qu'on cherche, ce sont les heures de nuits et non pas les heures travaillées. dans ton premier exemple, prise de service à 14:59 et fin de service à 15:15: le resultat est 2 alors qu'il n'y a aucune heure de nuit.
C'est pourquoi dans ma formule je fais référence à des cellules avec inscrit dedans 06:00 et 21:00.
J'espère avoiir répondu à tes questions.
à bientôt.
Cordialement
Tristan
Tristan,
Partant du principe qu’une tabelle que l’on doit bidouiller – comme de devoir inscrire 25:15 lorsque l’on veut écrire 01:15, voir les cellules marquées en bleu dans la feuille ci-jointe – n’est pas une bonne solution, j’ai continué à plancher sur ton problème. Imagine-toi en effet que tu voudrais faire profiter tes collègues de ton travail, il vaudrait mieux que son utilisation soit le plus simple possible.
Voici donc ma proposition :
https://www.excel-pratique.com/~files/doc/L3i3uHeures_de_nuit_8302_bis.xls
Je n’ai retenu que 3 cas de figure [ma formule si(… ;… ;si(… ;… ;…)] qui selon moi couvrent tous les possibilités :
1) Tu commences à travailler entre minuit et 6 heures (et donc tu devrais avoir fini avant 21 heures, sinon nous devons tous deux corriger nos formules).
2) Tu commences avant minuit – mais après 6 heures, selon la condition ci-dessus – et tu finis après minuit (il faut alors que la formule corrige les heures de fin).
3) Les autres cas.
J’ai aussi tenu compte de la logique des heures entamées de la manière suivante (A propos, travailles-tu dans un corps de police ?) :
de 21:00 à 21:01 = 1 heure (Minimum de 1 minute par heure entamée)
de 21:00 à 21:59 = 1 heure ( Maximum de 60 minutes par heure entamée)
de 21:00 à 22:00 = 2 heures (Il ne peut pas y avoir 61 minutes dans une heure entamée, donc on doit commencer à compter une deuxième heure entamée)
de 21:59 à 22:00 = 2 heures (Deux heures entamées, celle de la 21ème et celle de la 22ème heures)
de 21:00 à 05:59 = 9 heures ( Le maximum)
de 21:00 à 06:00 = 9 heures (Le maximum est déjà dépassé)
Je trouve ainsi illogique que ton calcul renvoi, par exemple, seulement 1 heure pour un temps de travail de 21:59 à 22:00 (cellules marquées en rouge dans la feuille ci-jointe).
La suite de ce travail serait éventuellement d’y inclure le calcul des heures de jour ou de l’utiliser pour une fiche mensuelle ou annuelle (ou une combinaison des deux).
Avec mes cordiales salutations
Bonjour, ou bonne nuit pour ce topic
Juste une remarque sur la manière de travailler :
plutôt que de rédiger une longue formule difficile àmaîtrtiser, utilisons les immenses ressources d'Excel !
à savoir possibilité d'insérer des colonnes, qui contiendont des résultats intermédiaires de calcul, plus faciles à visualiser et contrôler en cas c'erreur.
Exemple ici il faudra sans doute une colonne :
SI(ET(A3<A9;A4>A10);((A10*24)-(A9*24) ou du même genre
et quelques autres encore et tout redevient limpide.
Tristan,
A peine j'ai posté mon sujet que je me suis rendu compte que ma logique des heures entamées devait être assez .... illogique !!!
Je pense toutefois que tu devrais consulter le reste de ma formule. Si elle te plait, nous pourrons toujours - toi ou moi - corriger ce problème de minutes par la suite.
Encore un bon dimanche
Salut!
je te joint un fichier pour te montrer exactement comment j'ai fais.
Dans mon boulot, (SNCF) la première heure de la journée commence à 00:00 et finie à 01:00 et ainsi de suite.
Rapport à tes exemples, le troisième en particulier, parlons en minutes.
Debut 21:00
Fin 22:00
1ière minute, de 21:00 à 21:01
2ème minute, de 21:01 à 21:02
3ème minute, de 21:02 à 21:03
4ème minute, de 21:03 à 21:04
59ème minute, de 21:58 à 21:59
60ème minute, de 21:59 à 22:00
Donc quand je travaille de 21:00 à 22:00 je travaille 60 minutes donc 1 heure.
Ensuite pour régler le problème de la saisie après 24:00, j'ai fais une petite formule toute simple qui renvoie à une autre feuille de calcul.
j'ai une feuille 'Saisie et résultat!' (la où je rentre mes heures, et le résultat s'affiche)
Et une autre Feuille 'Calcul!' (où j'ai mis mes formule de 2 facons différentes:
- Soit tout les cas sont imbriqués dans la même formule(difficile à lire)
- Soit cas par cas et ensuite je fais la somme (en cas d'erreur c'est plus facile à corriger)
Dans l'exemple que je joins, dans la feuille de 'calcul!' je répète :
- en A1 l'heure que j'ai saisie dans la 1ère feuille pour la prise de service,
- en A2 l'heure que j'ai saisie dans la 1ère feuille pour la fin de service mais si la fin de service et inférieure au début de service je rajoute 24h.
Voilà problème de saisie de la 25ème heure résolu.
https://www.excel-pratique.com/~files/doc/exemple_calcul_heure_de_nuit.xls
Je pense avoir réglé tout les problèmes.
à bientôt
Tristan
Salut Tristan,
Entre deux chars de Carnaval, j'ai moi aussi planché sur ton problème.
En utilisant en partie l'idée d'Amadéus afin de corriger les erreurs de la dernière formule que je t'ai proposée, je te soumets la formule suivante qui permet d'effectuer - en une seule opération et sans passer par une feuille intermédiaire - le même calcul que le dernier que tu as réalisé :
=SI(ET(HEURE(A1)>=0;HEURE(A1)<=6);MIN(6;ARRONDI.SUP(A2*24;0))-ARRONDI.INF(A1*24;0);
SI(A2<A1;MIN(30;ARRONDI.SUP(A2*24;0)+24)-MAX(21;ARRONDI.INF(A1*24;0));ARRONDI.SUP(A2*24;0)-MAX(21;ARRONDI.INF(A1*24;0))))
J'ai comparé cette formule à ton calcul dans le fichier ci-joint :
https://www.excel-pratique.com/~files/doc/Heures_de_nuit_8302_quater.xls
En plus de devoir passer par une feuille intermédiaire, tu as aussi quand même un peu compliqué ta formule, notamment avec le nombre de cas étudiés (tes 6 SI) - par exemple tes possibilités 4 et 5 se retrouvent, morcelées, dans d'autres cas de figure - ou encore en utilisant des parenthèses inutiles, en tout cas la première et la dernière.
J'espère de tout coeur avoir pu t'aider et te souhaite une excellente soirée.
[color=blue]Edition :
Je me suis permis d'éditer ce message en plaçant un retour à la ligne dans la formule afin d'en améliorer la lisibilité
dré
salut yvouille!
bravo, c'est vrai qu'elle est beaucoup plus simple que la mienne.
je ne comprend pas beaucoup comment marche la fonction " MIN() " dans ce cas la, mais bon ca marche.
si tu sais me traduire litteralement la fonction " MIN(6;ARRONDI.SUP(A2*24;0)) " je suis preneur.
en tout cas merci pour ton acharnement à trouver la solution.
à bientôt
Tristan
Re-Salut.
Après reflexion, je viens de comprendre comment lire cette fonction.
en fait elle veut dire trouve le nombre le plus petit en "6" et ARRONDI.SUP(A2*24;0).
Je croyais qu'avec cette fonction il fallait préciser une plage de calcul.
Encore Merci.
à bientôt.
Tristan
Bonjour,
J'ai testé la dernière formule de yvouille.
Il me semble qu'un cas extrême ne soit pas résolu : début et fin en heures de nuit.
Une proposition d'une autre formule, un peu plus longue, mais qui évite ce piège.
Voir la pièce jointe :
https://www.excel-pratique.com/~files/doc/tristan.xls
dré
Bonjour, salut dré
Mais là, c'est plus du boulot, c'est de l'esclavage!
Je ne pense pas que 16h30 d'affilés existent dans l'entreprise citée plus haut. Il me semble du reste que ce cas avait été volontairement laissé de côté.
Cependant , une fois de plus, tu as raison, il faut être maximaliste, au moins dans le raisonnement et les formules.
Cordialement
Salut Amadéus,
La question concerne des heures de nuit, mais cette notion pourrait être extrapolée à des heures supplémentaires.
Si l'horaire normal est de 9:00 à 17:00, avec une coupure d'une heure, soit 7 heures de travail effectif, le calcul pourrait devoir s'effectuer pour une prestation de 8:00 à 19:00.
dré
Salut Tristan, Amadéus, Dré, jmd,
Tristan : N'oublie pas que tu trouves énormément d'information à propos des formules directement dans les boîtes de dialogue (clic sur le nom de la formule désirée dans la barre de formule puis sur "fx" (ou "X", je crois, selon les versions) à gauche de cette barre de formule. Puis encore sur "Aide sur cette fonction", lorsque tu es dans la boîte de dialogue.
Dré : J'avais effectivement indiqué à Tristan dans un précédent courrier :
1) Tu commences à travailler entre minuit et 6 heures (et donc tu devrais avoir fini avant 21 heures, sinon nous devons tous deux corriger nos formules).jmd : Evidement que des colonnes intermédiaires peuvent faciliter le travail de préparation, mais ces colonnes inutiles peuvent (ou doivent) aussi être annulées lorsque la formule finale fonctionne. Elles ne feraient autrement qu'alourdir inutilement une feuille de calcul.
Et mille excuses à tous d'avoir rouvert ce fil marqué depuis longtemps comme "Résulu".
Cordiales salutations