Calcul heures nuit/jour sur plusieurs jours
Bonjour,
Après maintes réflexions je dois me rendre à l’évidence, je n'arrive pas à faire ce que je veux faire.
Je souhaiterais faire une formule qui me calcule un tarif selon heure de nuit heure de jour.
Mon problème étant qu'il s'agit de réservations, et que celles-ci peuvent s'étendre sur plusieurs jours.
Je vous joins un fichier avec un exemple de réservation sur une journée et un exemple sur plusieurs jours;
Je vous serais grandement reconnaissant si vous pouviez m'aider, pour ma part... "Excel m'a tuer"...
D'avance merci !
Bonne journée
bonjour,
Hum... Ton fichier est vraiment minimaliste pour vraiment avoir une bonne idée de ce vers quoi on veut arriver, mais à première vue moi je partirai sur une idée comme dans le fichier joint.
Après il faudra surement décomposer... Mébon : ce n'est qu'une idée de départ !
A+
Bonjour,
C'est pourtant assez simple ... mais vérifie quand même tous les cas de figure !
=MAX(0;(1+FinN-DebN)*([@[Fin réservation réelle]]-[@[Début réservation réelle]]-1))+SI([@[Heure fin]]<[@[Heure début]];MAX(0;MIN(([@[Heure fin]])+1;FinN+1)-MAX([@[Heure début]];DebN))+MAX(0;FinN-[@[Heure début]]);MAX(DebN;[@[Heure fin]])-MAX(DebN;[@[Heure début]])+MAX(MIN(FinN;[@[Heure fin]])-MIN(FinN;[@[Heure début]]);0))=[@[Fin réservation réelle]]+[@[Heure fin]]-[@[Début réservation réelle]]-[@[Heure début]]-[@[H_nuit]]edit : fichier corrigé plus loin dans le fil de discussion
Bonjour,
En fait mon fichier original comporte ces durées totales.
Cependant ce que je n'arrive pas à faire, c'est séparer nombre d'heures de nuit et nombre d'heures de jour pour arriver à une facturation finale...
Par exemple -> Réservation de 15h à 20h = 4h de jour et 1h de nuit soit 4*2.5 = 10€
Mon problème étant pour les réservations de plusieurs jours passant successivement en heures de nuit et heures de jour...
Bonjour,
C'est pourtant assez simple ... mais vérifie quand même tous les cas de figure !
=MAX(0;(1+FinN-DebN)*([@[Fin réservation réelle]]-[@[Début réservation réelle]]-1))+SI([@[Heure fin]]<[@[Heure début]];MAX(0;MIN(([@[Heure fin]])+1;FinN+1)-MAX([@[Heure début]];DebN))+MAX(0;FinN-[@[Heure début]]);MAX(DebN;[@[Heure fin]])-MAX(DebN;[@[Heure début]])+MAX(MIN(FinN;[@[Heure fin]])-MIN(FinN;[@[Heure début]]);0))=[@[Fin réservation réelle]]+[@[Heure fin]]-[@[Début réservation réelle]]-[@[Heure début]]-[@[H_nuit]]
Aouch, je me sens nul dit comme ça
Ca m'a l'air top, faut vraiment que je devienne autonome là dessus, je vais tenter plusieurs cas de figure pour voir !
Un grand merci !!
je le mets en réserve aussi, je te dirai demain si c'est ok ou pas
il y a 8 ou 9 cas de figure à tester selon que le début ou la fin se trouve de jour ou de nuit, le même jour, le jour suivant ou celui d'après
je le mets en réserve aussi, je te dirai demain si c'est ok ou pas
il y a 8 ou 9 cas de figure à tester selon que le début ou la fin se trouve de jour ou de nuit, le même jour, le jour suivant ou celui d'après
Je regarde un peu, ça m'a l'air pas mal.
En tant que super profane, j'ai uen autre question. Je ne peux pas faire mes multiplication simplement du style heure de jour*2.5, ça me donne des choses incohérentes... (1.0015 etc)
Parce que les heures "apparentes" ici au format hh:mm ne sont que des fractions de jour en fait.
Passe en format standrad ou numérique et tu verras !
Excel stocke les jours en nombre entier, et les heures en décimales à raison de 1/24ème (of course) de jour.
Si donc ton taux est un taux / heure, il faut que tu multiplies ce que l'on pense être des heures par 24 ... tu auras alors des heures et des centièmes d'heures.
Ta formule doit donc être heure_de_jour_format_hh:mm * 24 * 2,5 €/h
Parce que les heures "apparentes" ici au format hh:mm ne sont que des fractions de jour en fait.
Passe en format standrad ou numérique et tu verras !
Excel stocke les jours en nombre entier, et les heures en décimales à raison de 1/24ème (of course) de jour.
Si donc ton taux est un taux / heure, il faut que tu multiplies ce que l'on pense être des heures par 24 ... tu auras alors des heures et des centièmes d'heures.
Ta formule doit donc être
heure_de_jour_format_hh:mm * 24 * 2,5 €/h
Exact ! Super
Par contre j'ai remarqué des problèmes sur des paramètres du style :
11/10/2018 14:00:00 12/10/2018 14:15:00
Ca me donne 24h15 de jour et 0 de nuit au lieu de 12h15 de jour et 12h de nuit.
Autre cas de figure :
26/11/2018 18:00:00 27/11/2018 19:52:00
Cela me donne 25h de jour et 52 minutes de nuit
J'ai essayé de bidouiller sans succès...
C'est exact, je le pressentais et je viens de m'en apercevoir.
Quand l'heure de fin est postérieure à l'heure d'arrivée avec un jour d'écart !
Je me souviens avoir travaillé sur ce cas pour un pompier travaillant plus de 24h d'affilée, je vais le corriger.
Voici la correction sur les heures de nuit
=MAX(0;(1+FinN-DebN)*ENT([@[Fin réservation réelle]]+[@[Heure fin]]-[@[Début réservation réelle]]-[@[Heure début]]))+SI([@[Heure fin]]<[@[Heure début]];MAX(0;MIN(([@[Heure fin]])+1;FinN+1)-MAX([@[Heure début]];DebN))+MAX(0;FinN-[@[Heure début]]);MAX(DebN;[@[Heure fin]])-MAX(DebN;[@[Heure début]])+MAX(MIN(FinN;[@[Heure fin]])-MIN(FinN;[@[Heure début]]);0))fichier joint
attention, j'ai modifié les bornes de nuit (22h -> 6h) pour pouvoir tester ...
Voici la correction sur les heures de nuit
=MAX(0;(1+FinN-DebN)*ENT([@[Fin réservation réelle]]+[@[Heure fin]]-[@[Début réservation réelle]]-[@[Heure début]]))+SI([@[Heure fin]]<[@[Heure début]];MAX(0;MIN(([@[Heure fin]])+1;FinN+1)-MAX([@[Heure début]];DebN))+MAX(0;FinN-[@[Heure début]]);MAX(DebN;[@[Heure fin]])-MAX(DebN;[@[Heure début]])+MAX(MIN(FinN;[@[Heure fin]])-MIN(FinN;[@[Heure début]]);0))fichier joint
attention, j'ai modifié les bornes de nuit (22h -> 6h) pour pouvoir tester ...
Super ! Ça marche parfaitement !
Un grand merci pour ça et bonne soirée !
Tu vois, finalement, c'était simple ...