× Aidez la recherche contre le COVID-19 avec votre ordi ! Rejoignez l'équipe PC Astuces Folding@home
 > Tous les forums > Forum Bureautique
 faire la différence entre heures de jour et heure de nuitSujet résolu
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
manekineko
  Posté le 11/05/2013 @ 00:52 
Aller en bas de la page 
Petite astucienne

Bonsoir,

Je bloque sur un problème depuis hier. Je n'ai malheureusemant pas trouvé la réponse sur le forum. J'espère que quelqu'un pourra m'aider.

Je cherche la formule à inclure dans ma formule pour une recherche selon que l'heure renseignée est comprise entre 7h et 19h (nuit ou pas (jour). Pour la formule de recherche, ça va, je sais faire, mais dès que je tente d'ajouter le paramètre "heure", je bloque !!!

Il ne s'agit en aucun cas de calculer des heures de travail avec une opération Heure Fin - Heure Début, mais de trouver le tarif qui correspond au tarif jour ou au tarif nuit selon l'heure de la prestation.

Merci à tous ceux qui partagent leurs connaissances et aident les bricoleuses de mon genre !

Publicité
panda-geant
 Posté le 11/05/2013 à 06:34 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Nouvel astucien

Bonjour

Essaie avec cette formule

=SI(ET(HEURE(C11)>7;HEURE(C11)<19);1;0)

ferrand
 Posté le 11/05/2013 à 09:15 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Si "Jour" correspond à >=07:00 et=19:00 et

Pour tester si une heure figurant en A1 appartient à la plage horaire "Jour" ou à la plage horaire "Nuit", la formule suivante :

=ARRONDI(MOD(A1+5/24;1);0)

renverra 1 si l'heure est en "Jour" ou 0 si l'heure est en "Nuit".

Si l'on applique à la cellule qui contient la formule le format personnalisé suivant : "Jour";;"Nuit" la formule affichera Jour à la place de 1 et Nuit à la place de 0.

NB: Bien entendu, l'heure inscrite en A1 doit être au format horaire d'Excel.



Modifié par ferrand le 11/05/2013 09:18
manekineko
 Posté le 11/05/2013 à 13:39 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

{#}

Super, merci infiniment :

la première formule fonctionne excepté pour 07:00 (le résultat est nuit)

la seconde est parfaite !!!

Elle est tellement bien et plus simple que je ne l'aurais imaginée ; du coup j'ai peur de l'imbriquer dans ma formule de recherche de tarif. Je pense donc faire une colonne intermédiaire masquée.

A moins d'une solution d'insertion à ma formule, si ce n'est pas trop demandé ?

Bon weekend à tous

ferrand
 Posté le 11/05/2013 à 13:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

On peut voir ça ! Mais quelle est ta formule ?

manekineko
 Posté le 11/05/2013 à 14:13 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Voici la formule de recherche :

SI(H5=1;INDEX(résultatj;EQUIV(F5;COLONNEJ;0);EQUIV(E5;LIGNEJ;0));INDEX(résultatn;EQUIV(F5;COLONNEN;0);EQUIV(E5;LIGNEN;0)))

H5 est la cellule contenant la formule indiquant "jour/nuit" (j'ai créé une colonne intermédiaire); H5 est au format personnalisé"JOUR""NUIT".

ferrand
 Posté le 11/05/2013 à 15:10 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

A vue de nez, tu peux simplement substituer ma formule à : H5=1, soit :

SI(ARRONDI(MOD(A1+5/24;1);0);INDEX(résultatj;EQUIV(F5;COLONNEJ;0);EQUIV(E5;LIGNEJ;0));INDEX(résultatn;EQUIV(F5;COLONNEN;0);EQUIV(E5;LIGNEN;0)))

NB: y rajouter =1 est en principe inutile, la formule ne renvoyant que 1 ou 0, 0=FAUX et 1=VRAI.

manekineko
 Posté le 11/05/2013 à 15:26 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Oui, merci, je m'y perdais parmi toutes les virgules.

Il faut dire que tu as résolu mon problème en quelques minutes alors que je m'essouffle dessus depuis des heures !

merci infiniment Ferrand.

manekineko
 Posté le 11/05/2013 à 15:29 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

{#}une autre question... j'abuse, je sais mais je tente...

Je voudrais que la formule considère les dimanche et les jours fériés comme des nuits, et donc que le tarif recherché soit trouvé dans la plage de données des tarifs de nuit ?

Publicité
ferrand
 Posté le 11/05/2013 à 16:09 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

manekineko a écrit :

{#}une autre question... j'abuse, je sais mais je tente...

Je voudrais que la formule considère les dimanche et les jours fériés comme des nuits, et donc que le tarif recherché soit trouvé dans la plage de données des tarifs de nuit ?


Sans voir ton tableau, un peu difficile de faire une réponse précise !

manekineko
 Posté le 11/05/2013 à 16:47 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

En fait il faudrait ajouter à la formule donnée plus haut un élément qui prend en compte la date dans la colonne A, et peut importe alors l'heure s'il s'agit d'un dimanche ou d'un jour férié, la solution sera le tarif nuit. Alors que la journée, le paramètre heure est pris en compte pour choisir entre le tarif jour et le tarif nuit.

manekineko
 Posté le 11/05/2013 à 16:49 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

En fait je pense que c'est un peu trop tordu ce que je demande. Je pense que je vais utiliser la formule et corriger manuellement les tarifs si c'est un dimanche ou un jour férié.

En tout cas merci beaucoup !

ferrand
 Posté le 11/05/2013 à 17:50 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

manekineko a écrit :

En fait je pense que c'est un peu trop tordu ce que je demande. Je pense que je vais utiliser la formule et corriger manuellement les tarifs si c'est un dimanche ou un jour férié.

En tout cas merci beaucoup !

C'est toi qui décide C'est tout à fait faisable mais je ne peux pas le matérialiser de façon adéquate sans un modéle permettant de voir comment ton système de calcul est organisé... Il m'a semblé aussi que tes deux tableaux tarifs pourraient être fusionnés en un mais c'est une hypothèse...

manekineko
 Posté le 11/05/2013 à 18:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Une amélioration par un pro de ton niveau me tente énormément, mais comment te montrer mon tableau sans qu'il ne soit visible par tous ?

{#}

Anonyme
 Posté le 12/05/2013 à 09:06 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Nouvel astucien
manekineko a écrit :

Une amélioration par un pro de ton niveau me tente énormément, mais comment te montrer mon tableau sans qu'il ne soit visible par tous ?

{#}

manekineko

pour celà tu peux utiliser "un faux tableau" et le poster directement ici et visible par tous ou alors,

Tu le déposes sur "cjoint.com" tu choisis "privé" et 4 ou 21 jours et tu cliques sur "créer lien" et tu envoies en MP(message privé) le lien a ferrand.

Son MP se trouve " à gauche,en dessus de son pseudo,et ressemble à une envellope".



Modifié par Anonyme le 12/05/2013 09:07
ferrand
 Posté le 13/05/2013 à 21:56 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Résumons-nous : nous avons deux tableaux identiques de tarifs (Nuit et Jour), on recherche le tarif applicable dans le tableau selon l'origine et la destination :

L'origine est portée dans la zone bleue, la destination dans la zone verte, le tarif est la zone jaune. En nommant ces zones ces zones Orig, Destin et Tarif, sur la feuille de calcul où l'on veut déterminer le tarif à partir d'une origine figurant en colonne E et d'une destination en colonne F, une formule classique associant INDEX et EQUIV permet d'extraire le tarif :

=INDEX(Tarif;EQUIV(F2;Destin;0);EQUIV(E2;Orig;0))

Mais nous avons deux tarifs, Jour et Nuit, le tarif Jour s'appliquant à partir de 07h00 et le tarif Nuit prenant le relais à partir de 19h00. Il convenait de savoir en fonction de l'heure lequel appliquer. Pour cela nous avons défini (plus haut) une formule renvoyant 1 si l'heure de départ est en Jour et 0 si elle est en Nuit. Je la rappelle (l'heure testée étant en colonne G) :

=ARRONDI(MOD(G2+5/24;1);0)

Il s'agissait de compléter cette définition du tarif à appliquer avec la règle complémentaire que s'il s'agit d'un dimanche le tarif Nuit s'applique quelle que soit l'heure, et de même s'il s'agit d'un jour férié. Compte tenu de la formule précédente établie, on notera que si l'on a une formule renvoyant 0 pour un dimanche et 1 pour un autre jour de la semaine d'une part, et une autre renvoyant 0 s'il s'agit d'un férié et 1 dans le cas contraire, si l'on fait :

=[Form.=>0siFérié//1siNonFérié]*[Form.=>0siDI//1siLUàSA]*[Form.=>0siHeureNuit//1siHeureJour]

Le résultat sera 1 si chaque formule élémentaire renvoie 1, et le tarif Jour devra être appliqué.

Le résultat sera 0 si l'une ou l'autre des formules élémentaires donne 0, et le tarif Nuit devra être appliqué.

Reste donc à déterminer les deux autres formules élémentaires pour en faire une formule globale de détermination du tarif.

(à suivre)

ferrand
 Posté le 13/05/2013 à 22:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Commençons par le Dimanche. On sait que la fonction JOURSEM renvoie 1 pour le dimanche et 2 à 7 pour les autres jours du Lundi au Samedi. Si on fait -1 sur le résultat de cette fonction, on aura 0 pour le dimanche. Reste à obtenir 1 pour les autres jours.

On utilisera une astuce comparable à la formule pour déterminer l'horaire Jour/Nuit. Sachant que le quotient d'un entier positif avec celui immédiatement supérieur donnera un résultat inférieur à 1 mais toujours >= 0,5 ( 1/2 = 0,5 ), et que 0/1 donnera naturellement 0 :

=ARRONDI((JOURSEM(B2)-1)/JOURSEM(B2);0)

La date figurant en colonne B, cette formule renverra 0 pour le dimanche et 1 pour les autres jours.

Reste maintenant à faire la même chose pour les jours fériés. Il faut d'abord monter une table des fériés pour pouvoir s'y référer.

On passe sur le calcul des fériés fixes qui ne pose aucun problème à partir de l'année en haut de colonne, et aussi sur celui des fériés mobiles qui consiste à calculer la date de Pâques et à déduire les autres à partir de cette denière... On étend le tableau sur l'année suivante car l'utilisation du fichier peut au moins déborder d'une année sur l'autre ou être à cheval, et on peut étendre le tableau si l'utilisation est pluri-annuelle.

On nommera Fériés la plage jaune (pour une utilisation plus aisée) [seulement celle-là] et An la cellule verte contenant l'année correspondante.

La recherche d'une date de 2013 avec EQUIV dans la plage Fériés, pour savoir si elle s'y trouve, renverra le rang de 1 à 13 si la date cherchée est effectivement un férié et provoquera l'erreur N/A! dans le cas contraire.

Ce que l'on veut obtenir c'est 1 au lieu de N/A! et 0 au lieu d'un nombre de 1 à 13. On peut l'obtenir en testant le résultat renvoyé par EQUIV :

=ESTNA(EQUIV(B2;Fériés;0))

va renvoyer VRAI (assimilable à 1 dans les calculs) si la date n'est pas un férié et provoque N/A! et renvoyer FAUX (assimilable à 0) s'il s'agit d'un férié.

Mais cette formule ne va fonctionner correctement que pour 2013. Si la date est en 2014 ou au-delà (on aura étendu le tableau dans ce cas), il faudra décaler la plage Fériés en colonne de l'écart d'années avec 2013 pour appliquer la formule sur la bonne année :

=ESTNA(EQUIV(B2;DECALER(Fériés;;ANNEE(B2)-An);0))

(à suivre)

Publicité
ferrand
 Posté le 13/05/2013 à 23:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Avant de fusionner les 3 formules élémentaires contribuant à la détermination du tarif et intégrer leur produit dans la formule de recherche indiquée au début, il convient de considérer que l'on peut simplifier la recherche en fusionnant les tableaux de tarifs Jour et Nuit en un seul, leur structure étant identique :

On a donc la partie de tarif Nuit en jaune qu'on prolonge par la partie Jour en orange. L'ensemble de ces deux partie forme la plage Tarif dans laquelle on cherchera le tarif à appliquer.

On note qu'entre chaque tarif Nuit et le tarif correspondant Jour il y a un écart de 5 colonnes. On a définit une formule qui renvoie 0 pour l'application du tarif Nuit et 1 pour le Jour. Si l'on multiplie ce résultat par 5, la formule renverra 0 ou 5 soit l'écart en colonnes lorsque le tarif Jour est à appliquer.

Donc la formule de recherche :

=INDEX(Tarifs;EQUIV(F2;Destin;0);EQUIV(E2;Orig;0)+ESTNA(EQUIV(B2;DECALER(Fériés;;ANNEE(B2)-An);0))*ARRONDI((JOURSEM(B2)-1)/JOURSEM(B2);0)*ARRONDI(MOD(G2+5/24;1);0)*5)

renverra directement le résultat cherché.

On aura intérêt si la formule est positionnée avant les éléments date et heure à la mettre sous condition que le dernier de ces éléments qu'on saisira soit servi, pour éviter qu'elle n'affiche une erreur. Par exemple :

=SI(G2<>"";formule;"")

Pour se simplifier la mise en place, on pourrait également mettre la partie bleue en formule nommée et la remplacer dans la formule ci-dessus par le nom qu'on lui a donné. Dans ce cas, veiller à ce que dans la formule nommée les références de cellules soient sous forme colonne absolue, ligne relative [exemple : $G2] pour que le nom renvoie la bonne valeur.

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Les bons plans du moment PC Astuces

Tous les Bons Plans
11,78 €Adaptateur USB 3.0 Ethernet Gigabit TP-Link UE300 à 11,78 €
Valable jusqu'au 25 Octobre

Amazon fait une promotion sur l'adaptateur USB 3.0 Ethernet Gigabit TP-Link UE300 qui passe à 11,78 € au lieu de 20 €. Cet adaptateur vous permettra de rajouter une prise Ethernet Gigabit à votre ordinateur portable (ou votre tablette via un adaptateur OTG) qui en est dépourvu.


> Voir l'offre
89,14 €Smartphone 6.53 pouces Xiaomi Redmi 9C (2 Go / 32 Go) à 89,14 € (stock Europe)
Valable jusqu'au 25 Octobre

Banggood fait une promotion sur le smartphone 6.53 pouces Xiaomi Redmi 9C qui passe à 89,14 €. Ce smartphone dispose d'un écran 6.53 pouces HD, un processeur 8 coeurs Helio G35, 2 Go de mémoire, de 32 Go d'espace de stockage extensible par microSD, d'un triple APN 13 MP, du bluetooh, du Wifi. Il est compatible avec les fréquences 2G, 3G et 4G. Le tout tourne sous Android 9 avec une surcouche Xiaomi que vous pouvez mettre en français dès le premier démarrage. La batterie est de haute capacité 5000 mAh. Un portable d'entrée de gamme complet idéal pour un pré ado.

Le téléphone est expédié depuis les entrepôts espagnols de Banggood et vous sera donc livré rapidement avec aucun risque de douane.


> Voir l'offre
134,29 €SSD Corsair Force MP510 960Go (NMVe M.2) à 134,29 €
Valable jusqu'au 24 Octobre

Amazon fait une promotion sur le SSD Corsair Force MP510 960 Go (NMVe M.2) qui passe à 134,29 € livré gratuitement alors qu'on le trouve ailleurs à partir de 160 €. Ce SSD utilise une interface M.2 NVMe PCIe Gen3 x 4 pour une connexion simple et des performances exceptionnelles : jusqu’à 3480 Mo/s en lecture séquentielle et jusqu’à 3000 Mo/s en écriture séquentielle. Le SSD est doté de la technologie 3D TLC NAND haute densité offrant une endurance d’écriture durable et assorti d’une garantie de cinq ans. 


> Voir l'offre

Sujets relatifs
Différence entre deux moments (date et heure)
la différence entre microsoft office 2010 et service Office 365
quelle différence entre office 365 et office 2013
Avec Excel = Somme jour+Heures+minutes
Calcul HS jour, HS nuit
Calcul différence entre 2 horaires
Différence entre 2 chiffres avec toujours un chiffre positif
Calculer la différence entre 2 chiffres en %
Quelle différence entre...
Difference entre deux dates
Plus de sujets relatifs à faire la différence entre heures de jour et heure de nuit
 > Tous les forums > Forum Bureautique