Bonjour James,
Merci beaucoup pour ton retour. J'apprécie beaucoup le formattage du fichier.
Dans la cellule B4, il y a un menu deroulant et je souhaite que quand je choisi une équipe sa formule s'applique dans la cellule G4.
Exemple:
SI B4 = "IT" on a la formule ci-dessous dans G4
IT
((NETWORKDAYS(E4,F4,feries)*(M4-L4)+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(INT(E4)&":"&INT(F4))))=7)*1)*(O4-N4))-IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>O4),0,IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>=L4),E4-INT(E4)-L4))-IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<N4),O4-N4,IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<O4),O4-(F4-INT(F4))))-IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>M4),0,IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>=L4),E4-INT(E4)-L4))-IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<L4),M4-L4,IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<M4),M4-(F4-INT(F4)))))*1440
SINON SI B4 = "COMPTA" on a la formule ci-dessous dans G4
COMPTA
((NETWORKDAYS(E4,F4,feries)*(M7-L7)+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(INT(E4)&":"&INT(F4))))=7)*1)*(O7-N7))-IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>O7),0,IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>=L7),E4-INT(E4)-L7))-IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<N7),O7-N7,IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<O7),O7-(F4-INT(F4))))-IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>M7),0,IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>=L7),E4-INT(E4)-L7))-IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<L7),M7-L7,IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<M7),M7-(F4-INT(F4)))))*1440
SINON SI B4 = "TELECOM" on a la formule ci-dessous dans G4
TELECOM
((NETWORKDAYS(E4,F4,feries)*(M6-L6)+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(INT(E4)&":"&INT(F4))))=7)*1)*(O6-N6))-IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>O6),0,IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>=L6),E4-INT(E4)-L6))-IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<N6),O6-N6,IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<O6),O6-(F4-INT(F4))))-IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>M6),0,IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>=L6),E4-INT(E4)-L6))-IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<L6),M6-L6,IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<M6),M6-(F4-INT(F4)))))*1440
SINON SI B4 = "Finance" on a la formule ci-dessous dans G4
Finance
((NETWORKDAYS(E4,F4,feries)*(M5-L5)+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(INT(E4)&":"&INT(F4))))=7)*1)*(O5-N5))-IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>O5),0,IF(AND(WEEKDAY(E4,2)=6,E4-INT(E4)>=L5),E4-INT(E4)-L5))-IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<N5),O5-N5,IF(AND(WEEKDAY(F4,2)=6,F4-INT(F4)<O5),O5-(F4-INT(F4))))-IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>M5),0,IF(AND(WEEKDAY(E4,2)<6,E4-INT(E4)>=L5),E4-INT(E4)-L5))-IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<L5),M5-L5,IF(AND(WEEKDAY(F4,2)<6,F4-INT(F4)<M5),M5-(F4-INT(F4)))))*1440
SINON SI B4 = "RECOUVREMENT" on a la formule de recouvrement dans G4
Mais j'ai de la difficulté à calculer le temps travaillé pour l'équipe RECOUVREMENT vu qu'il travaille que du lundi au vendrredi mais 8AM à 6PM (du lundi - Mercredi) et 8AM à 8PM (Jeudi- Vendredi) et fermer les weekend.
Merci beaucoup pour ton aide,
Cordialement,