[Résolu] Calcul du nombre de plages horaire entre deux dates

[Grég]

Bonsoir,

je cherche à calculer le nombre de fois que des plages horaire se trouve entre deux dates.

Pour le calcul des indemnités j'aimerai automatiser un fiche de remboursement de frais.

Le problème est le suivant:
ne sont remboursés les déjeuner que si la plage horaire 12h à 14h est entièrement couverte, de même pour les dîners sur la plage horaire 18h à 20h et enfin pour les nuitées la plage horaire 22h à 07h.

Est-il possible en entrant les dates et heures de début et de fin de mission de remplir automatiquement le nombre de déjeuner, de dîner et de nuitée.

Exemple: du samedi 10h au dimanche 15h, on à 1 déjeuner le samedi; 1 dîner le samedi et 1 nuitée du samedi au dimanche et 1 déjeuner le dimanche

Merci à tous

[DLE]

Bonjour,

Dans le fichier les cellules suivantes sont nommées pour plus de facilité d'écriture:
E16 = DateD
E21 = DateF
M8 = dm début de mission date + heure
M9 = fm fin de mission date + heure
Le tableau L3:Q6 concatène la date et l'heure pour chaque plage utilisée dans les formules des cellules E26, E28, E30, à masquer si besoin.
A+

[Grég]

Bonsoir

Bravo DLE pour ta solution qui convient parfaitement à mon document.

Je t'avoue ne pas avoir tout saisis mais je vais étudier tes formules pour les comprendre surtout grâce à tes explications.

Merci encore à DLE en particulier et j'espère que ce post servira à beaucoup d'autres et à une prochaine question sur ce site vraiment très très bien

Grég

[Grég]

Re Bonjour à tous et en particulier à DLE,

le post était noté résolu mais ... comme souvent il y a un mais.

Le problème se pose sur plusieurs jours, le cumul ne se fait pas.

Exemple du 07/10 /2017 au 09 / 10 / 2017 les calculs ne sont plus bons.

J'ai de mon côté essayer avec des NB.SI. ENS car oui je ne vous laisse bosser sans chercher de mon côté en attendant une réponse toute faite

J'ai essayé de modifier certaines plages ou formules mais rien ne fonctionne.

C'est pour cela que je reviens vers vous.

Amicalement

PS :Et merci aux modérateurs pour leur travail

[DLE]

Bonjour,

En N8 la formule calcule le nombre de jours entre les bornes, le résultat est ajouté aux cellules E26, E28 et E30.
A valider.

[Grég]

Bonsoir DLE,

je retrouve le même problème.

Lorsque je rentre du 07/10 21h au 08/10 14h ça calcule 1 déjeuner 1 dîner 1 nuitée
alors qu'il devrait y avoir: 1 nuitée et 1 déjeuners mais pas de dîner car pas le créneau 18h - 20h

Et je n'arrive pas à trouver la solution

ça ne parait pourtant pas si compliqué

[Jeff]

Bonjour,

Je n'ai pas trop le temps de me pencher sur le sujet... qui me semble plus complexe qu'il n'y parait, en effet :

• L'heure de la date initiale est inférieure à midi, et l'heure et la date finale sont supérieure à la date initiale 14:00, le premier déjeuner est comptabilisé.
• L'heure de la date initiale est inférieure à 18:00, et l'heure et la date finale sont supérieure à la date initiale 20:00, le premier repas est comptabilisé.
• L'heure de la date initiale est inférieure à 22h, et l'heure et la date finale sont supérieure à la date initiale + 1 jour 7:00, la première nuitée est comptabilisée.

Il faut également appliquer le raisonnement ci-dessus (s'il est juste ) pour le dernier jour.

Ensuite seulement comptabiliser les jours intermédiaire s'il y en a.

Autant dire

Bon courage, mais je ne suis pas sûr qu'un tableur soit le plus adapté pour cet exercice

[DLE]

Bonsoir,
Nouveau tableau à valider, j'ai décomposé les calculs dans le tableau "L12:O17".
En E26, E28 et E30 on reprend les totaux du tableau ci-dessus.

Bonne soirée.

[Grég]

Bonsoir DLE,

Toujours la même chose entre le 07/10 20h et le 08/10 08h il ne devrait y avoir qu'une nuitée et pourtant ça note 1 1 1 (1 déjeuner; 1 dîner et 1 nuitée)

je te remercie d'essayer de résoudre mon problème mais je pense que le souci vient du fait que la nuitée est sur 2 jours.

Il faudrait peut être essayer en décomposant la nuit en 22h - 00h et 00h - 07h

Dès que j'ai le temps je vais essayer ...

Encore un grand merci et au pire je laisse tomber

Cordialement

Grég

[DLE]

Bonsoir,

Nouveau classeur à valider, j'ai simplifié le tableau "L13:O15" et ajouté le tableau "L18:N21" pour les calculs hors plage.

Bonne soirée.

Cdlt.

[Grég]

Bonjour et comme toujours à toi DLE,

je te remercie pour ton travail et j'en suis désolé ne fonctionne pas pour tous les cas .

Je ne peux suivre ce dossier plus longtemps en ce moment donc je suis désolé de t'avoir "embêter" (DLE) avec mon sujet, qui je pensais, serait simple à résoudre mais non.

Je vais continuer à faire à la main et au crayon.

Je vais continuer si j'ai un peu de temps et pourquoi pas trouver une solution .

Encore un grand merci et spécialement à toi DLE pour tes propositions qui m'ont permis d'explorer de nouvelles fonctions.

Grég

[Noonours]

Bonjour

Peut-être après la bataille une proposition de fichier avec formules alambiquées qui semblerait marcher :

Greg_Remboursement_V5.ods

Inconvénient: Pour que les calculs soient justes, il ne faut pas que la durée de la mission dépasse trois jours
Pas trop le temps de m'y pencher plus ce weekend, mais il doit y avoir plus élégant avec formules matricielles que je ne maîtrise pas, ou avec macros, mais là, les questions seront à poser dans la section macros

COOordialement,

[Grég]

Bonjour Noonours et DLE en particulier et à tous,

Merci Noonours, non la bataille n'était pas finie mais elle l'est maintenant de part ta proposition, bien qu'il existe sûrement d'autres façons de faire.

Peu importe (pour moi) le côté alambiqué, seul le résultat compte (pour moi aussi).

Je pense que DLE sera preneur car il a travaillé dessus via une autre façon de faire peut être plus difficile à mettre en formule .

Comme Jeff le précise, c'est un sujet plus compliqué que prévu, cela change apparemment selon le point de vue de la méthode.

Merci à Noounours , DLE ainsi qu'à tous et j'espère que ce post pourra servir à beaucoup de monde, car si le but premier, je ne le nie pas, est personnel je pense à la communauté.

[zeguedon]

Bonjour,

Sujet très intéressant et bien que marqué résolu pour la deuxième fois, une autre proposition sans tableau intermédiaire sauf celui des plages horaires de début et fin des repas et nuitées. Cela permet de les modifier sans avoir à modifier les formules.

Greg.png

Pour simplifier la lecture et la compréhension, les cellules sont nommées de façon à éviter les explications. Pour ceux que ce sujet intéresse :

Inconvénient: Pour que les calculs soient justes, il ne faut pas que la durée de la mission dépasse trois jours

Je suis parti du principe que tous les jours inclus entre le premier et le dernier comptent pour un déjeuner, un dîner et une nuitée :

Code : Tout sélectionner

SI(Nbj>=2;Nbj-1) 

auquel on ajoute 1 si le début du déjeuner est supérieur à l'heur de début de mission

Code : Tout sélectionner

+(DebDej>=Hdeb)

et +1 si la l'heure de fin du déjeuner et inférieure ou égale à l'heure de fin de mission.

Code : Tout sélectionner

+(FinDej<=Hfin) 

ceci est valable pour tous les cas sauf si il n'y a pas de différence entre le jour de début et de fin de mission. Dans ce cas il faut retrancher 1 :

Code : Tout sélectionner

-(Nbj=0)

Au final pour les repas :

Code : Tout sélectionner

=(DebDej>=Hdeb)+(FinDej<=Hfin)+SI(Nbj>=2;Nbj-1)-(Nbj=0)

et pour les nuitées le raisonnement est identique, sauf que si il n'y à pas de différence entre le jour de début et de fin de mission, alors il n'y a pas de nuitée. Il faut renvoyer 0 (zéro) dans ce cas :

Code : Tout sélectionner

*(Nbj>0)

Code : Tout sélectionner

=((Hdeb<=DebNuit)+(Hfin>=FinNuit)+SI(Nbj>=3;Nbj-2)-(Nbj=1))*(Nbj>0)

Et le fichier qui va avec

[Grég]

Bonsoir Zeguedon,

merci pour ta proposition qui même "après la bataille" est bienvenue.

C'est toujours bien d'avoir d'autres façons de résoudre un problème.

Je pense que toute proposition même incomplète ou avec des erreurs (dédicace à DLE) permet d'avancer.

Encore merci à tous.

Et cette fois ci je ne me suis pas trompé en marquant comme résolu ce sujet