Calcul intervalle de temps

tipoone

XLDnaute Nouveau
Bonjour,

Je souhaiterais calculer un intervalle de temps en jours et en heures : exemple le temps passé entre le 15/03/2012 à 17H30 et le 17/04/2012 12H30. Mais pas que... Le problème c'est qu'il faudrait que excel me calcul aussi le nombre d'intervalle passé entre 11h30 et 13h30 et entre 18h30 et 20H30.

Exemple : départ le 17/04/2012 à 8H00 arrivée le 19/04/2012 à 20 H 30

Ici je voudrais que excel me dise qu'il y à 2 jours, 2 périodes entre 11H30 et 13H30 et 2 période entre 18h30 et 20h30.

Etant très nul sur excel j'implore votre aide :) merci à à vous !!
 

Pièces jointes

  • calcul d'intervalles de temps.xlsx
    26.5 KB · Affichages: 195

Victor21

XLDnaute Barbatruc
Re : Calcul intervalle de temps

Bonsoir, et bienvenue sur XLD.

Je subodore une question à tiroir. Alors avant que nous ne commencions, une question : Quid des WE et des fériés, s'ils existent, entre les deux dates ?

Et une petite deuxième : quid des périodes partielles ?
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Calcul intervalle de temps

Bonsoir Tipoone et Victor31,

Comme Victor31, je crains que la question ne soit à rallonge...

Exemple : départ le 17/04/2012 à 8H00 arrivée le 19/04/2012 à 20 H 30

Ici je voudrais que excel me dise qu'il y à 2 jours, 2 périodes entre 11H30 et 13H30 et 2 période entre 18h30 et 20h30.
Il me semble que pour cet exemple, il y a 3 périodes de chaque type ?

En tout cas, un essai dans le fichier joint qui ne comptabilise que les périodes complètes [11H30,13H30] et [18h30,20h30] entre les deux dates.

Nombre de jours et heures entre les deux dates:
Code:
=ENT(F5-D5) & " jour(s) et " & TEXTE(MOD(F5-D5;1);"hh:mm") & " heures(s)"

Nombres de périodes complètes [11H30,13H30] entre les deux dates:
Code:
=SOMMEPROD(   --(ENT(D5)-1+LIGNE(INDIRECT("1:" &1+ENT(F5-D5) ))+TEMPSVAL("11:30")>=$D$5)*(ENT(D5)-1+LIGNE(INDIRECT("1:" &1+ENT(F5-D5) ))+TEMPSVAL("12:30")<=$F$5))

Nombres de périodes complètes [18h30,20h30] entre les deux dates:
Code:
=SOMMEPROD(   --(ENT(D5)-1+LIGNE(INDIRECT("1:" &1+ENT(F5-D5) ))+TEMPSVAL("18:30")>=$D$5)*(ENT(D5)-1+LIGNE(INDIRECT("1:" &1+ENT(F5-D5) ))+TEMPSVAL("20:30")<=$F$5))
 

Pièces jointes

  • calcul d'intervalles de temps v1.xlsx
    9.2 KB · Affichages: 197
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Calcul intervalle de temps

(re)Bonsoir Victor21 :D

Je suis confus :mad: de t'avoir fait déménagé de Bourgogne en Midi-Pyrénées et qui plus est en pleine nuit!

Je t'en prie garde ton chapeau tel qu'il est. D'ailleurs les couvre-chefs bourguignons (video 1 Ce lien n'existe plus) gagneraient à être diffusés plus largement!

Chapeau-Dijon.png
 
Dernière édition:

tipoone

XLDnaute Nouveau
Re : Calcul intervalle de temps

Bonjour merci beaucoup pour votre aide. Je suis désolé de répondre si tard mais je n'avais plus d'ordinateur...
Par contre, lorsque que la durée est supérieur à un mois excel ne décompte plus les intervalles de temps (11h30/13h30 et 18h30/20h30...

Merci à vous
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Calcul intervalle de temps

Bonjour tipoone,

Pourriez vous joindre un exemple car je n'arrive pas à reproduire l'erreur ?

NB: à la relecture du fil, dans les formules jointes, remplacer $D$5 et $F$5 par D5 et F5; mais je ne sais pas si l'erreur provient de là.
 

tipoone

XLDnaute Nouveau
Re : Calcul intervalle de temps

Salut,

Peux tu m'expliquer cette formule : =SOMMEPROD((ENT(D5)-1+LIGNE(INDIRECT("1:" &1+ENT(F5-D5) ))+TEMPSVAL("11:30")>=$D$5)*(ENT(D5)-1+LIGNE(INDIRECT("1:" &1+ENT(F5-D5) ))+TEMPSVAL("12:30")<=$F$5))

Merci beaucoup
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Calcul intervalle de temps

Bonsoir tipoone,

Exercice pas forcement facile de retrouver et d'expliquer le raisonnement qui a conduit à la formule après six mois d'abandon.

Le principe:
Tester pour chaque jour compris entre la date de début D5 et la date de fin F5 si l'intervalle de temps 11h30 <=> 12h30 est bien compris entre D5 et F5. Si oui, on incrémente le compteur de 1 unité sinon on n'incrémente pas le compteur.

Exemple: D5=17/04/2012 08:00:00 et F5=19/04/2012 20:30:00.

On va tester si la période du 17/04/2012 de 11h30 à 12h30 est comprise entre 17/04/2012 08:00:00 et 19/04/2012 20:30:00 -> c'est le cas, on a donc trouvé 1 période.

Puis on va tester si la période du 18/04/2012 de 11h30 à 12h30 est comprise entre 17/04/2012 08:00:00 et 19/04/2012 20:30:00 -> c'est le cas, on a donc trouvé 2 périodes.

Puis on va tester si la période du 19/04/2012 de 11h30 à 12h30 est comprise entre 17/04/2012 08:00:00 et 19/04/2012 20:30:00 -> c'est le cas, on a donc trouvé 3 périodes.

On remarque que si on teste un jour avant ou bien un jour après, on trouve encore le même résultat.
Si on teste la période du 16/04/2012 de 11h30 à 12h30 on voit qu'elle n'est pas comprise entre 17/04/2012 08:00:00 et 19/04/2012 20:30:00 donc le compteur de période n'est pas incrémenté.

Si on teste la période du 20/04/2012 de 11h30 à 12h30 on voit qu'elle n'est pas comprise entre 17/04/2012 08:00:00 et 19/04/2012 20:30:00 donc le compteur de période n'est pas incrémenté.

Trouver les jours à tester:
La partie entière d'une date+heure donne le jour (la partie après la virgule représente l'heure).
On part du jour précédent le premier jour soit ENT(D5)-1 et on va jusqu'au jour de fin F5. Pour cela on va ajouter successivement au jour de départ 1 puis 2 puis 3 .. jusqu'à ce qu'on égale ou dépasse le jour de fin.

Combien doit on ajouter de jour ?
C'est à peu près le problème des intervalles et des arbres. On va rajouter de 1 jour à la (différence des jours +2) jours.
exemple: du 17/04 au 19/04, on rajoute (19-17+2) jours soit 4 jours. donc si on part du jour précédent (le 16), on va considérer les jours 16+1, 16+2, 16+3, 16+4 soit les jours 17,18, 19 et 20.
(*) je viens de me rendre compte que suivant les heures, mon comptage du nombre de jours peut ne pas être suffisant. Il vaut mieux ajouter un jour en plus. C'est pourquoi le 1+ de la formule v1 a été modifiée en 2+ dans la formule v2

Comment faire référence dans une formule à l'ensemble {1,2,3,4} ?
On utilise une astuce qui consiste à utiliser une fonction dont le résultat peut être transformé en matrice. La fonction utilisée est la fonction LIGNE() associée à la fonction INDIRECT. ex: INDIRECT("1:4") retourne une référence représentant les lignes 1 à 4.
En toute logique, =LIGNE(INDIRECT("1:4")) retourne 1. Cependant si on valide cette formule matriciellement, elle retourne non plus un nombre unique mais une matrice égale à {1,2,3,4}.

En fait dans la formule finale, c'est SOMMEPROD qui transformera la formule en formule matricielle.

Donc pour résumer:
On commence par déterminer une matrice {1,2,3,..n} -> c'est le rôle de LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))
On détermine les jours à examiner, c'est le rôle de ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ) (en matricielle)
On y rajoute le début de la période: ENT(D5)-1+LIGNE(INDIRECT("1:" &2+ENT(F5-D5) ))+TEMPSVAL("11:30"))
on obtient les 4 dates suivantes: 17/04/2012 11h30, 18/04/2012 11h30, 19/04/2012 11h30, 20/04/2012 11h30

On fait de même pour la fin de période, et on obtient: 17/04/2012 12h30, 18/04/2012 12h30, 19/04/2012 12h30, 20/04/2012 12h30

Il faut ensuite déterminer si ces quatre périodes sont incluses dans la période D5 <=> F5. C'est le but des comparaisons:
(ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("11:30")>=$D$5) et
(ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("12:30")<=$F$5)

Les deux comparaisons doivent être simultanément vérifiées. Pour cela, on utilise le produit de comparaison qui est équivalent à la fonction ET.
(ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("11:30")>=$D$5) * (ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("12:30")<=$F$5)

Il faut que chacune des conditions soit VRAI pour que le produit des comparaisons le soit. Quand une valeur VRAI intervient dans un calcul, elle est transformée en un 1; quand une valeur FAUX intervient dans un calcul, elle est transformée en un 0.

Donc (ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("11:30")>=$D$5) * (ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("12:30")<=$F$5) vaut 1 si les deux conditions sont vérifiées sinon 0.

On applique SOMMEPROD qui convertit la formule en formule matricielle et qui somme le résultat de chaque comparaison (1 ou 0)
Ce qui donne (les -- sont inutiles):
Code:
=SOMMEPROD( (ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("11:30")>=$D$5)*(ENT(D5)-1+LIGNE(INDIRECT("1:" & 2+ENT(F5-D5) ))+TEMPSVAL("12:30")<=$F$5))

nb: la version v2 avec correction du nombre de jour.
 

Pièces jointes

  • calcul d'intervalles de temps v2.xlsx
    9.3 KB · Affichages: 110
Dernière édition:

tipoone

XLDnaute Nouveau
Re : Calcul intervalle de temps

Salut,

Tout d'abord je tiens à m'excuser pour le temps que j'ai mis à te répondre j'en suis vraiment désolé ...
En tout cas ta réponse et très complète du coup j'ai tout compris :) ça à l'air compliqué en lisant la formule mais finalement c'est assez logique si on se concentre bien :p. Je vais rajouter d'autres formules pour compléter mon outil :).
Encore merci pour ton aide et pour le temps que tu as passé sur cet outil. J'espère être aussi calé que toi un jour ^^
Bonne journée.
 
Dernière édition:

Discussions similaires

Réponses
15
Affichages
355

Statistiques des forums

Discussions
311 720
Messages
2 081 889
Membres
101 831
dernier inscrit
gillec