Calcul d'écart d'heures

judikael

XLDnaute Occasionnel
Bonjour le Forum

Content de revenir après un long intermède.

J'ai besoin de vos lumières pour résoudre un problème qui me bloque pour un calcul

Je dois calculer la durée en jour et heures entre 2 cellules de format "date heure", mais j'ai besoin d'exclure dans ce calcul toutes les heures des samedis et dimanches, ainsi que celles des jours fériés

Je connais la formule sur le calcul de nombre en jours ouvrés mais cela ne me donne pas le détail des "heures", ce qui est primordial dans le calcul que je dois faire

Si quelqu'un a une aide à m'apporter, je suis preneur avec le plus grand plaisir

Je vous remercie par avance

PP
 

CISCO

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Bonsoir

Cf. en pièce jointe une formule faisant la différence entre deux dates sans les samedis et les dimanches. A vérifier bien sûr.

La formule comprend trois parties :
* la première qui calcule le nombre d'heures du premier jour, s'il ne s'agit pas d'un samedi ou d'un dimanche
* la seconde, qui calcule le nombre d'heures du second jour au dernier, jusqu'à minuit, s'il ne s'agit pas de samedi ou de dimanche. Cette partie ne peut fonctionner qu'avec des jours entiers.
* la dernière, qui soustrait le nombre d'heures prises en trop par la partie précédente, le dernier jour, s'il ne s'agit pas d'un samedi ou d'un dimanche.

Ex du 10/11/2015 10:15 au 15/11/2015 12:00
1ère partie : 10/11/2015 10:15 au 10/11/2015 24:00 en éliminant les heures le samedi et le dimanche
2nde partie : +11/11/2015 00:00 au 15/11/2015 24:00 en éliminant les heures le samedi et le dimanche
3ème partie : - 15/11/2015 12:00 au 15/11/2015 24:00 en éliminant les heures le samedi et le dimanche

Bien sûr, on pourrait procéder autrement, par ex en prenant 10/11/2015 00:00 au 15/11/2015 24:00 et en retirant ensuite les heures en trop le premier et le dernier jour

@ plus
 

Pièces jointes

  • Classeur1.xlsx
    8.3 KB · Affichages: 55
  • Classeur1.xlsx
    8.3 KB · Affichages: 50
Dernière édition:

CISCO

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

re

En pièce jointe, une version un peu plus simple, toujours en éliminant que les samedis et les dimanches.

@ plus
 

Pièces jointes

  • Classeur1.xlsx
    8.3 KB · Affichages: 52
  • Classeur1.xlsx
    8.3 KB · Affichages: 47

job75

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Bonjour judikael, CISCO,

Voyez le fichier joint qui utilise cette fonction VBA :

Code:
Function Minutes&(deb As Date, fin As Date)
Dim j1&, j2&, h1#, h2#, i&, jour&
j1 = Int(deb): j2 = Int(fin)
h1 = deb - j1: h2 = fin - j2
If Weekday(j1, 2) > 5 Or Application.CountIf([Feries], j1) Then h1 = 0
If Weekday(j2, 2) > 5 Or Application.CountIf([Feries], j2) Then h2 = 1
For i = j1 To j2
  If Weekday(i, 2) < 6 And Application.CountIf([Feries], i) = 0 Then jour = jour + 1
Next
Minutes = 1440 * (jour - h1 + h2 - 1)
End Function
J'ai converti en texte la durée en minutes, mais vous pouvez la convertir comme vous le désirez.

A+
 

Pièces jointes

  • Durée(1).xlsm
    26.1 KB · Affichages: 46

job75

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Re,

Voici une solution par formule dans le fichier joint, en C2 à tirer vers le bas :

Code:
=SOMMEPROD((JOURSEM(ENT(A2)+LIGNE(INDIRECT("1:"&ENT(B2)-ENT(A2)+1))-1;2)<6)*NON(NB.SI(Feries;ENT(A2)+LIGNE(INDIRECT("1:"&ENT(B2)-ENT(A2)+1))-1)))-MOD(A2;1)*(JOURSEM(A2;2)<6)*NON(NB.SI(Feries;ENT(A2)))-(1-MOD(B2;1))*(JOURSEM(B2;2)<6)*NON(NB.SI(Feries;ENT(B2)))
La formule fait exactement le même calcul que la fonction VBA précédente.

Au lieu de minutes je lui fais renvoyer des jours décimaux.

A+
 

Pièces jointes

  • Durée par formule(1).xlsx
    19.5 KB · Affichages: 53

Victor21

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Bonjour à tous.

En pas plus simple. Les tests effectués semblent concluants. A vérifier quand même...
=TEXTE(ENT(MIN(1;1-MOD(A3;1))*ET(JOURSEM(A3;2)<6;NB.SI(Fériés;ENT(A3) )=0)+MIN(1;MOD(B3;1))*ET(JOURSEM(B3;2)<5;NB.SI(Fériés;ENT(B3))=0)+MAX(0;NB.JOURS.OUVRES.INTL(A3+1;B3-1;1;Fériés)));"#0")&" jours "&TEXTE(MIN(1;1-MOD(A3;1))*ET(JOURSEM(A3;2)<6;NB.SI(Fériés;ENT(A3) )=0)+MIN(1;MOD(B3;1))*ET(JOURSEM(B3;2)<5;NB.SI(Fériés;ENT(B3))=0)+MAX(0;NB.JOURS.OUVRES.INTL(A3+1;B3-1;1;Fériés))-ENT(MIN(1;1-MOD(A3;1))*ET(JOURSEM(A3;2)<6;NB.SI(Fériés;ENT(A3) )=0)+MIN(1;MOD(B3;1))*ET(JOURSEM(B3;2)<5;NB.SI(Fériés;ENT(B3))=0)+MAX(0;NB.JOURS.OUVRES.INTL(A3+1;B3-1;1;Fériés)));"hh:mm")
(Sans les espaces superflus)
 

Pièces jointes

  • Diff Heures hors Fériés WE.xlsx
    20.8 KB · Affichages: 55

Victor21

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Re,

En plus simple que mon post précédent (début en A3, fin en B3), en c3:
en [h]h:mm:ss :
=MIN(1;1-MOD(A3;1))*ET(JOURSEM(A3;2)<6;NB.SI(Fériés;ENT(A3))=0)+MIN(1;MOD(B3;1))*ET(JOURSEM(B3;2)<5;NB.SI(Fériés;ENT(B3))=0)
+MAX(0;NB.JOURS.OUVRES.INTL(A3+1;B3-1;1;Fériés))
en jours + hh:mm:ss (sur le modèle de Gérard, que je salue :) ), en D3 :
=ENT(C3)&" jour(s) + "&TEXTE(MOD(C3;1);"hh:mm:ss")
 

CISCO

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Bonsoir à tous

Judikael aura le choix...

La même que dans mon post #5, en éliminant aussi les jours fériés (liste à compléter en colonne E).

@ plus
 

Pièces jointes

  • Classeur1.xlsx
    8.4 KB · Affichages: 45
  • Classeur1.xlsx
    8.4 KB · Affichages: 54

Victor21

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Bonsoir, Cisco.

C'est surtout la transformation directe de temps en jours et heures qui complique la formule. Sinon :
On calcule la durée en jours décimaux du 1° jour à prendre en compte (si ni WE, ni férié) :
MIN(1;1-MOD(A3;1))*ET(JOURSEM(A3;2)<6;NB.SI(Fériés;ENT(A3))=0)
On calcule la durée en jours décimaux du dernier jour à prendre en compte (si ni WE, ni férié) :
MIN(1;MOD(B3;1))*ET(JOURSEM(B3;2)<5;NB.SI(Fériés;ENT(B3))=0)
On compte le nb de jours à prendre en compte ((si ni WE, ni férié), en excluant le 1er et le dernier :
MAX(0;NB.JOURS.OUVRES.INTL(A3+1;B3-1;1;Fériés))
et on ajoute le tout.
 

CISCO

XLDnaute Barbatruc
Re : Calcul d'écart d'heures

Bonjour

Et une dernière pour la route
Code:
(ENT(A2+1)-A2)*(JOURSEM(A2;2)<6)*ESTNA(EQUIV(ENT(A2);E11:E16;0))+NB.JOURS.OUVRES(ENT(A2)+1;ENT(B2);E11:E16)-(ENT(B2+1)-B2)*(JOURSEM(B2;2)<6)*ESTNA(EQUIV(ENT(B2);E11:E16;0))

@ plus

P.S : J'avais oublié que la fonction NB.JOURS.OUVRES élimine aussi les jours fériés.
 

Pièces jointes

  • Classeur2.xlsx
    8.4 KB · Affichages: 54
  • Classeur2.xlsx
    8.4 KB · Affichages: 54

Statistiques des forums

Discussions
311 720
Messages
2 081 923
Membres
101 840
dernier inscrit
SamynoT