Calculer des heures comprises dans des intervalles
Bonjour à tous,
Voila plusieurs jours que je suis bloqué sur un document Excel que je dois produire.
Pour faire simple, je veux réaliser un tableau de pointage hyper simple d'utilisation pour un de mes collègue. L'objectif de ce document c'est de remplir juste 2 colonnes pour la personne qui va pointer et de calculer tout le reste automatiquement. Pour faire simple la personne qui va rentrer les heures tapera en colonne B (Heure arrivé) le nombre 9 par exemple (cellule en format texte) , puis en colonne C 'heure départ par exemple 23 (cellule en format texte). Ensuite dans mes colonnes D et E respectivement l'heure de départ et l'heure d'arrivée mais cette fois ci automatiquement transformé en heures (Donc en colonne D 9:00 et en colonne E 23:00, les 2 en format heure cette fois ci). Ensuite en colonne F j'ai le calcul de la différence des heures entre les 2 , tel que en F2 =MOD(E2-D2;1) .
Jusqu'ici tout vas bien!
Seulement maintenant ce que j'aimerais c'est que automatiquement il me calcul (ou il recherche) dans cette différence les heures qui sont effectués le jour (Chez nous entre 6h et 22h) en colonne G et les heures qui sont effectués de nuit ( entre 22h et 6h) en colonne H.
Juste pour signaler la colonne A représente le nom de l'individu! Dans mon exemple le gas travaille de 9h à 23h je devrais donc avoir en colonne G = 13 h et en colonne H = 1h.
La ou je bloque c'est pour les 2 dernières colonnes! Si quelqu'un peut me donner une réponse ou m'expliquer comment faire ca serait top !
Pour récapituler :
Colonne A = Nom de l'individu / Colonne B = Heure arrivé en format texte / Colonne C = Heure départ en format texte /Colonne D = Heure arrivé en format Heure /Colonne E = Heure Départ en format Heure / Colonne F = Différence heure entre colonne E-Colonne D/ Colonne G = Nombre d'heure travaillé entre 6h et 22h / Colonne H = Nombre d'heure travaillé de nuit.
En vous remerciant par avance
Thomas
Bonjour et bienvenue sur le forum
Bonjour à tous
Autres formules.
Bye !
Bonjour GMB
Il me semble une petite erreur pour MR H dans le calcul des heures.
De mon côté, idem, car j'ai pris comme début H. de nuit à 23h00 au lieu de 22h00
Bref, il suffit juste de modifier dans le gestionnaire de formule "FinN"
Et il faut quand même citer que la formule n'est pas de moi, mais d'AMADEUS que je salue (Je l'avais mise de côté)
Bonjour thomaspap,
Bravo à M12 pour la formule...
Si tu connais VBA, je te propose une alternative :
Tu copies le code suivant dans le VBA :
Function CalcHeuresNuit(zFromHour As Date, zToHour As Date) As Long
Const cFromNorm = 6
Const cToNorm = 23
Dim lFromHour As Long, lToHour As Long
Dim lCalc1 As Long, lCalc2 As Long
lFromHour = Hour(zFromHour)
lToHour = Hour(zToHour)
If lFromHour < cFromNorm Then
lCalc1 = cFromNorm - lFromHour
End If
If lToHour < cFromNorm Then
lToHour = lToHour + 24
lCalc2 = lToHour - cToNorm
End If
CalcHeuresNuit = lCalc1 + lCalc2
End Functionet en H2 tu colles "=CalcHeuresNuit(D2;E2)" que tu tires vers le bas.
NB: Du fait du VBA, tu devras sauvegarder ton classeur avec l'extension .xlsm
Et il faut quand même citer que la formule n'est pas de moi, mais d'AMADEUS que je salue (Je l'avais mise de côté)
Bonjour,
j'avais zappé ce sujet
le calcul des heures de nuit est en effet un vrai casse-tête, il y a 8 cas à considérer ...
je vous fournis aussi une autre formule un peu plus ramassée ci-joint
=SI([@[Dep.]]<[@[Arr.]];MAX(0;MIN(([@[Dep.]])+1;FinN+1)-MAX([@[Arr.]];DebN))+MAX(0;FinN-[@[Arr.]]);MAX(DebN;[@[Dep.]])-MAX(DebN;[@[Arr.]])+MAX(MIN(FinN;[@[Dep.]])-MIN(FinN;[@[Arr.]]);0))que j'ai comparée à celle d'Amadeuss/M12
=SI(ET([@[Arr.]]<DebN;[@[Arr.]]>=FinN);SI([@[Dep.]]>[@[Arr.]];SI([@[Dep.]]<=DebN;0;MOD([@[Dep.]]-DebN;1));SI([@[Dep.]]>=FinN;MOD(FinN-DebN;1);MOD([@[Dep.]]-DebN;1)));SI([@[Arr.]]>=DebN;SI(OU([@[Dep.]]>[@[Arr.]];[@[Dep.]]<=FinN);MOD([@[Dep.]]-[@[Arr.]];1);SI([@[Dep.]]<=DebN;MOD(FinN-[@[Arr.]];1);MOD(FinN-[@[Arr.]];1)+MOD([@[Dep.]]-DebN;1)));SI(OU([@[Dep.]]<[@[Arr.]];[@[Dep.]]>DebN);MOD(FinN-[@[Arr.]];1)+MOD([@[Dep.]]-DebN;1);SI([@[Dep.]]<FinN;MOD([@[Dep.]]-[@[Arr.]];1);MOD(FinN-[@[Arr.]];1)))))Merci de l'info,
Je vais la mettre au chaud dans mes archives
C'est super c'est exactement ce que je voulais!
Un grand merci pour votre réactivité et vos compétences!
Je n'y serai jamais arrivé sans vous, je ne maitrise pas Excel à ce niveau!
Merci a vous et Bonne année!
Re-bonjour à tous et merci de me lire!
je reviens vers vous car j'ai trouvé un erreur dans ces formules! Une erreur pas nécessairement grave mais qui complique un peu les choses!
Dans le document de M12 ou même de GMB lorsque je rentre dans ma colonne B l'horaire d'arrivé d'une personne et que cette personne a commencé à travailler à 22h, dans mes colonnes G et H la référence #NOM? s'affiche!
Comme je ne sais pas du tout comment réparer l'erreur je refait appel à vous pour améliorer le document!
En espérant ne pas vous déranger je vous souhaite une agréable fin de semaine
En vous remerciant
Thomaspap
tu prends ma formule en passant en tableau c'est plus pratique ... j'ai pris les cas où l'heure de départ est "apparemment" avant l'heure d'arrivée car le olendemain
Bonsoir Thomas,
Personnellement, j'ai une règle de base pour EXCEL qui concerne les formules et je me permets de te la livrer:
Si une formule dépasse la ligne, passons au VBA
Les arguments :
- Une formule trop longue est difficile à comprendre par les personnes autres que son auteur.
- Une formule ne permet de commenter ce que l'on fait et pourquoi on le fait. En VBA, on a toute latitude pour le faire (encore faut-il s'y astreindre).
Bien sûr, passer au VBA induit la contrainte de son apprentissage. Mais, le NET offre une multitude de formations à VBA (à commencer par ce site) et toute l'aide nécessaire.
je mettrais bien 2 lignes quand même
j'ai tenté de transformer la formule que je proposais pour le calcul de nuit en macro, hé bien contre toute attente je l'ai in fine trouvcée plus complexe; mais je partage quand même ton point de vue à une ligne près
il reste quand même que dans certaines entreprises les macros sont interdites
Dans le document de M12 ou même de GMB lorsque je rentre dans ma colonne B l'horaire d'arrivé d'une personne et que cette personne a commencé à travailler à 22h, dans mes colonnes G et H la référence #NOM? s'affiche!
C'est quoi siet dans la formule ci-dessous ??
=SI(ET(B3<C3;C3<22);C3-B3;SI(ET(B3<22;C3>B3);22-B3;SI(ET(B3<22;C3<B3);22-B3;siet(B3>22;C3<a)(;0;0))))/24Rebonjour à tous!
Un grand merci a vous pour le moment car ce document est vraiment pratique!
cepandant je rencontre une nouvelle difficulté! Celle ou Monsieur 1 commencerai le boulot à 23h ou 24 h!
En colonne B et C , respectivement, les heures d'arrivées et de départ en nombre. Puis en colonne G et H le nombre d'heures réalisés pendant le Jour ou pendant la nuit!
Seulement dans le cas ou il commence après 22h (uniquement pour les valeurs 23 et 24) il m'affiche en colonne G et H les termes #NOM?
Est ce que quelqu'un saurait me donner un coup de main pour régler ce problème ? je coimmence à comprendre ces formules et je pense que ce problème est liée au fait que les valeurs vont jusque 22 inclus, mais pas au dela!
En vous remerciant !
Thomaspap
De plus j'ai découvert une nouvelle erreur
pour les valeurs inférieur a 6, il comptabilise tout en heure de jour!
Par exemple quelqu'un qui bosse de 1h du matin à 5h du matin aura bosser 4h de jour! ce qui est faux puisque les heures de nuits vont de 22h à 6h du matin !
Pour réparer ca je ne sais pas du tout comment faire pour le coup!
Quelqu'un pourrait il m'aider?
En vous remercier
Excellement votre
Bonjour,
Peux-tu m'expliquer ce que les deux Colonnes B et C contiennent ... ???
Un Nombre d'heures ... qui est une donnée pour faire tes calculs ...et d'éviter de saisir les heures ... ???
Salut Thomas,
Ci-joint une proposition de fichier test ...
En espérant que cela t'aide ...
Seulement dans le cas ou il commence après 22h (uniquement pour les valeurs 23 et 24) il m'affiche en colonne G et H les termes #NOM?
Je crois que tu ne dois pas bien lire ...
C'est quoi
sietdans la formule ci-dessous ??=SI(ET(B3<C3;C3<22);C3-B3;SI(ET(B3<22;C3>B3);22-B3;SI(ET(B3<22;C3<B3);22-B3;siet(B3>22;C3<a)(;0;0))))/24