Calcul d'intervalles entre des heures

[benadry]

Bonjour,

Je cherche une formule permettant à Excel de compter automatiquement le nombre d'indemnités de repas dues à un salarié en déplacement.

Les indemnités sont dues pour le déjeûner si le salarié est absent de 11h00 à 14h00 et pour le dîner s'il est absent de 18h00 à 21h00.

J'ai un tableau Excel (exemple ci-joint) dans lequel j'entre la date et l'heure de départ en déplacement et la date et l'heure de retour.
Dans mon exemple, le salarié est parti le 02/09/2013 à 10:55 et rentré le 09/09/2013 à 13:45.

Les indemnités n'étant pas dues le week-end, il a donc droit à 5 indemnités de déjeûner (pour les 2, 3, 4, 5, 6 septembre) et autant d'indemnités de dîner (pour les mêmes jours).

Quelqu'un saurait-il comment je peux traduire ça sous Excel ?

J'ai réussi très facilement à compter le nombre d'heures pendant lesquelles le salarié est en déplacement. Mais, compter le nombre d'intervalles de 11h00 à 14h00 et de 18h00 à 21h00 qu'il y a dans ce laps de temps de déplacement, c'est une autre affaire !
En plus, il faut gérer la contrainte des éventuels week-ends et jours fériés (les jours fériés sont joints dans le fichier d'exemple).


Merci d'avance.

Cordialement.

 

[Guest]

Re : Calcul d'intervalles entre des heures

Bonjour,

Voir dans le fichier joint si cela correspond à la demande.

il suffit de compter le nombre de jour ouvrés (fonction nb.jour.ouvres) entre les deux date de multiplier par 2 puis de retrancher 1 si le départ c'est déroulé après 14heures et 1 si le retour c'est effectué avant 11 heures

A+

 

[benadry]

Re : Calcul d'intervalles entre des heures

Bonjour Hasco,

Merci beaucoup pour votre aide.

Malheureusement, il y a quelque chose qui cloche.

Je suis d'accord pour le nombre de jours ouvrés dans mon exemple : il est bien de 6.

En revanche, il n'y a que 10 indemnités de repas dues : 5 le midi et 5 le soir et pas 12. Compte tenu des heures de départ et de retour, 2 indemnités sont dues les 2, 3, 4, 5 et 6 septembre, mais aucune le 9.

J'ai testé votre formule en mettant 11:30 comme heure de départ le 2 septembre et elle reste à 12, alors qu'elle n'est pas due puisque le départ a lieu après 11:00.

Si vous trouvez là où le bât blesse, je ne demande pas mieux !

Merci en tout cas pour votre aide.

Cordialement.

 

[Guest]

Re : Calcul d'intervalles entre des heures

Bonsoir,

Ce qui n'est pas clair pour moi, c'est ta notion d'absence?

Ce que je comprends:

le jour du départ : s'il part après 21 h on ôte 2 indemnités (midi et soir), s'il part après 14 h on en ôte une seule (le midi)
le jour du retour : s'il revient avant 14 h on enlève 2 indemnités (midi et soir), s'il revient avant entre 14 et 18 h on enlève 1 seule.

Est-ce cela?

A+

 

[benadry]

Re : Calcul d'intervalles entre des heures

Re-,

C'est exactement ça !

Parallèlement, j'ai travaillé dessus et je me demande si la formule suivante ne peut pas convenir :

=D4*2-(B2>TEMPS(11;0;0))-(B4<TEMPS(14;0;0))-(B4<TEMPS(21;0;0))

Je pense que c'est bon, mais merci de me donner ton avis.

En tout état de cause, un grand merci pour m'avoir mis sur la piste. Je ne voyais vraiment pas comment me sortir du truc !

Cordialement.

 

[benadry]

Re : Calcul d'intervalles entre des heures

Re-,

En continuant à tester, je me rends compte qu'il ne faut neutraliser les week-ends que s'ils se situent au cours du déplacement.

Si le salarié part le dimanche 1er septembre à 10:50 pour être sur place le lundi 2, il aura deux indemnités de repas. Idem, s'il rentre le dimanche soir après 21:00. En revanche, rien si c'est au milieu, puisqu'on considère qu'il rentre chez lui (nos déplacements ne vont pas loin du tout) et qu'il repartira éventuellement le dimanche soir suivant (auquel cas, cela donnera lieu à une autre saisie de déplacements et on appliquera la solution ci-dessus).

Comment dire ça à Excel ???

 

[Guest]

Re : Calcul d'intervalles entre des heures

Re,

Je dirai quelque chose comme ceci: =D4*2-((HEURE(B2)>14+HEURE(B2)>21)+(HEURE(B4)<11)+(HEURE(B4)<18))

Pour l'heure de retour, ce n'est pas 21 heures qui compte mais 18 s'il revient entre 18 et 21 (18:45) d'après ton énoncé du post 1 tu lui dois une indemnité

Pour le de départ c'est 14 h l'heure maximale du matin et 21 heure l'heure maximal du soir
Avec cette formule j'arrive à 10 indemnités pour un départ à 10:45 et un retour à 13:45
11 indemnités pour un retour à 18 heures exclus
.

A+

 

[benadry]

Re : Calcul d'intervalles entre des heures

Re-,

En fait, on ne s'est pas bien compris : pour avoir droit au repas du soir, il doit être en déplacement de 18h00 à 21h00.

Donc, s'il finit son déplacement à 18h45, pas d'indemnité.

Je vais tester ta formule (je n'ai pas encore eu le temps).

As-tu vu mon post précédent concernant les week-ends ?

A te relire.

A +

PS : je viens de tester et je pense que la formule est bonne.

 

[piga25]

Re : Calcul d'intervalles entre des heures

Bonjour benadry,
Salut Hasco,

Peut être comme ceci :

=(SI(JOURSEM(B1;2)=7;1;0)+SI(JOURSEM(B3;2)=6;1;0)+NB.JOURS.OUVRES(B1;B3;G2:G12))*2-(SI(ET(HEURE(B2)<=11;HEURE(B4)>=14);0;1)+SI(ET(HEURE(B2)<=18;HEURE(B4)>=21);0;1))

 

[benadry]

Re : Calcul d'intervalles entre des heures

Bonjour Piga25,
Bonjour Hasco,


La formule de Piga25 ne fonctionnait pas à 100 %. Avec un départ à 11:01 le salarié bénéficiait d'un déjeûner. En évaluant la formule pas à pas, je me suis rendu compte qu'il considérait que 11:01 était inférieur à 11:00.

J'ai donc fait un mélange des deux solutions proposées en ajoutant le format TEMPS et là ça fonctionne.

Voici ce que ça donne :

=(SI(JOURSEM(B1;2)=7;1;0)+SI(JOURSEM(B3;2)=6;1;0)+ NB.JOURS.OUVRES(B1;B3;G2:G12))*2-(SI(ET(B2<=TEMPS(11;0;0);B4>=TEMPS(14;0;0));0;1)+SI(ET(B2<=TEMPS(18;0;0);B4>=TEMPS(21;0;0));0;1))

Un grand merci en tout cas à tous les deux.

Cordialement.

PS : si quelqu'un peut me dire comment on note "RESOLU", comme ça je pourrais le faire. Merci d'avance.