Si necesitas la exactitud de la hora de apertura del primer día y cierre del último hay que añadir un cálculo más. A la cifra ya calculada hay que restar la diferencia entre la hora de apertura de acción y el inicio de jornada del día inicial y la diferencia entre el fin de jornada y la hora de cierre de acción en el día final.
He añadido lo siguiente:
Columna J “HORA APERTURA ACCIÓN” (se introduce o se calcula a partir del dato de input)
Columna K “HORA CIERRE ACCIÓN” (se introduce o se calcula a partir del dato de input)
Columna N: “HORAS DEDUCIR 1ER DIA” (Fórmula para N4:=(+J4-BUSCARV(L4;Hoja2!$A$3:$H$6;DIASEM(H4;2)+1;0))*24 )
Columna O “HORAS DEDUCIR ULTIMO” (Fórmula para O4: =(BUSCARV(L4;Hoja2!$A$12:$H$15;DIASEM(I4;2)+1;0)-K4)*24 )
Las matrices que referencian las horas de entrada y salida por zona y día están en estas dos tablas en la Hoja2
Se modifica ligeramente la fórmula en M4 para restar los cálculos nuevos añadiendo solamente -N4-O4: =SUMA(INDIRECTO(DIRECCION(COINCIDIR(H4;$A:$A;0);COINCIDIR(L4;$A$3:$F$3;0);;;"Hoja1")&":"&DIRECCION(COINCIDIR(I4;$A:$A;0);COINCIDIR(L4;$A$3:$F$3;0))))-N4-O4
Por último para resolver el problema de memoria por las muchas fórmulas yo haría lo siguiente:
Opción a) tener guardadas las fórmulas en la fila 1 (rango M1O1 en la primera hoja arriba). Cada vez que se vuelcan nuevos datos este rango se copia manualmente para todos los registros, se recalcula y finalmente se convierte en valores (copiar + pegar como valores dejando fuera fila 1) con ello la hoja queda casi libre de fórmulas y no consume memoria.
Opción b) hacer esto mismo con una macro.