Funciones utilizadas: MAX, MIN
Suponiendo que disponemos de un rango de fechas iniciales en A2:A10 y de fechas finales en B2:B10 que construyen intervalos por fila, y un intervalo específico de fechas para un período en F2:G2, planteamos el siguiente método para calcular la superposición del intervalo específico dentro de cada intervalo por fila del rango original:
CALCULAR SUPERPOSICIÓN DE FECHAS EN EXCEL
|
Donde «G2» corresponde a la fecha final del intervalo específico, «B2» corresponde a la primera fecha final del rango original, «F2» corresponde a la fecha inicial del intervalo específico, y «A2» corresponde a la primera fecha inicial del rango original.
Para el primer método exacto utilizamos la función CONTAR.SI y le aplicamos el operador «mayor que» (>) y el valor «0» para convertirlo a resultado lógico.
De esta forma, si encuentra el nombre definido en el argumento al menos una vez, el resultado será mayor que 0, lo cual devolverá VERDADERO.
MÉTODO 2 – BÚSQUEDA EXACTA CON ESNUMERO/COINCIDIR
|
Donde «A1:A20» corresponde al rango de nombres donde buscar, y «Nombre» corresponde al valor a buscar dentro del rango objetivo. Reemplazar dicho valor con el nombre objetivo o la referencia correspondiente.
Combinamos las funciones MAX y MIN para obtener el resultado deseado.
Internamente calculamos las duraciones en días de cada período restando la fecha final de la fecha inicial. Para decidir que fecha utilizar en cada rango de comparación aplicamos la función MIN a la fecha final y la función MAX a la fecha inicial, luego sumamos 1 para considerar el día inicial y englobamos la sintaxis en una función MAX que permite reemplazar valores negativos (donde no hay días superpuestos) por el valor 0 correcto.
Finalmente fijamos las referencias del período de análisis para poder arrastrar la fórmula.
De esta forma logramos obtener la cantidad de días superpuestos entre cada intervalo/período del rango original y el intervalo específico de análisis de forma rápida y fácil en Excel.
Puedes encontrar más fórmulas interesantes en el anuario de Desafiando Excel en el siguiente link o videos con más fórmulas ingeniosas de Excel aquí.
Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.