Bonjour
Je vais essayer de faire court et compréhensible... Pas évident !
LIGNE(INDIRECT(8*60+1&":"&20*60)), en matriciel, liste tous les nombres de 481 à 1200 (8 h = 480 minutes, 20 h = 1200 minutes) (Si tu veux travailler avec d'autres heures d'ouverture, il faut donc remplacer le 8 et le 20 en conséquence).
Excel a pour unité de temps le jour. Donc 1:00 = 1/24. Donc, si tu veux avoir le nombre de minutes correspondant à une heure donnée sous la forme hh:mm, il faut multiplier cette valeur par 24 puis par 60.
E12*24*60 donne donc le nombre de minutes correspondant à la valeur dans E12.
(E12*24*60<LIGNE(INDIRECT(8*60+1&":"&20*60))) renvoie des VRAI pour toutes les minutes de 481 à 1200 strictement supérieures à E12 en minutes, des FAUX dans les autres cas.
(LIGNE(INDIRECT(8*60+1&":"&20*60))<=F12*24*60) renvoie des VRAI pour toutes les minutes de 481 à 1200 inférieures ou égales à F12 en minutes, des FAUX dans les autres cas.
VRAI*VRAI = 1. Tous les autres produits comprenant un FAUX donnent 0.
Nous nous retrouvons donc avec une série de 1 et de 0.
La partie décrite ci-dessus ne concernent que les minutes de présence de la 1ère personne le matin, le responsable hébergement. Il faut faire de même avec les heures correspondant à l'après midi (la ligne 14) puis pour la 2ème personne, la 3ème personne et ainsi de suite (2 lignes pour chaque personne).
Comme nous faisons la somme de tous ces 0 et ces 1, nous nous retrouvons avec une série de nombres >=1 (plusieurs personnes travaillent pendant ces minutes là) et peut être un ou des 0 (Personne ne travaille pendant ce(s) minute(s) là).
Si à la fin, on n'a que des nombres >= 1, c'est que toutes les minutes de 481 à 1200 sont comprises dans les périodes de travail considérées. EQUIV(0;... va renvoyer #NA (Excel ne trouve pas de 0 dans la liste précitée). Et ESTNA(EQUIV(0;... va renvoyer VRAI, donc "OK".
S'il y a au moins un 0, c'est qu'au moins une des minutes de 481 à 1200 n'est pas comprise dans ces périodes, et donc qu'il ni y a personne à ce moment là. Dans ce dernier cas, EQUIV(0;.... va renvoyer un nombre (la position du premier 0 dans la liste) et ESTNA(EQUIV(0;... va renvoyer FAUX, donc "ncp".
Il faut peut être vérifier ce qu'il faut faire avec les bornes des intervalles contrôlés en travaillant avec des < ou des <=. A voir en faisant des tests, en mettant des cas où il ni a personne à 8:00, mais avec quelqu'un à 8:01. Idem avec 19:59 et 20:00.
@ plus
P.S1 : La formule est longue ( 2 lignes par personne * nombre de personnes), et donc peu pratique s'il y a beaucoup d'employés (mais je ne vois pas de solution plus concise pour le moment). Le mieux serait de donner un nom à la partie LIGNE(INDIRECT(8*60+1&":"&20*60)) pour en simplifier l'écriture. En allant à la ligne avec Alt+entrer dans la barre des formules, cela facilite le travail. Reste plus qu'à faire pas mal de copier-coller...
P.S2: Il y a bien sûr d'autres possibilités. Par ex, tu peux mettre,
SI(MIN((E12 à la place de SI(ESTNA(EQUIV(0;(E12 à condition de mettre à la fin <=F34*24*60))<>0;"OK";"ncp") à la place de <=F34*24*60);0));"OK";"ncp")