Ambos casos serían muy sencillos de calcular si las funciones DIA.LAB o DIA.LAB.INT contaran con la opción "0" (los siete días de la semana son laborales).
Como esa opción no existe, combinamos las funciones MAX, FILA y DIASEM en esta fórmula matricial
=MAX((C5+C4+1-FILA(INDIRECTO("1:"&C4)))*(ESERROR(COINCIDIR(C5+C4+1-FILA(INDIRECTO("1:"&C4));Feriados;0)))*(DIASEM(C5+C4+1-FILA(INDIRECTO("1:"&C4));2)<6))
donde C4 contiene la cantidad de días corridos al vencimiento y C5 la fecha inicial
El funcionamiento de la fórmula está explicado en la primer nota de la serie. Básicamente, creamos un vector de fechas partiendo de la fecha de vencimiento hacia atrás, donde las fechas de los fines de semana o de feriados reciben el valor 0; luego MAX extrae el valor mayor, que es el primer día hábil "hacia atrás".
Volviendo a la consulta de nuestro lector, queremos encontrar el primer día hábil posterior al vencimiento si este cae en día no laboral. Para encontrar la fecha vamos a aplicar una lógica similar: creamos un vector de fechas "hacia adelante" (los días posteriores a la fecha de vencimiento antes de las correcciones); las fechas que caen en días no laborales (fin de semana o feriado) reciben el valor cero. Ahora tenemos que encontrar la fecha hábil más cercana, es decir la menor, lo que podemos hacer con la función MIN (recordemos que en Excel las fechas son miembros de un serie de números enteros). Pero aquí nos encontramos con el problema que el vector de fechas incluye ceros.
Para encontrar el menor de los valores positivos de una serie (en nuestro caso, la primer fecha hábil posterior al vencimiento) excluyendo los ceros usaremos la función K.ESIMO.MENOR combinada con SUMAPRODUCTO en esta fórmula matricial
=K.ESIMO.MENOR(($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6),SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1)
Para ahorrar a mis estimados lectores los mareos (y talvez ligeras náuseas) que pueda causar la lectura de semejante fórmula, vamos a explicarla. Podemos dividir esta fórmula en dos partes
La expresión
$C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6
crea un vector de fechas donde el primer miembro es la fecha de vencimiento (fecha inicial + más diás corridos al vencimiento) y los restantes son los números de serie de las fechas siguientes o cero si la fecha cae en día no laboral. "Feriados" es un nombre definido que se refiere al rango que contiene las fechas de los feriados.
Para facilitar la lectura podemos definir un nombre que se refiere a esta fórmula (vector_fechas)
La expresión
SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1
calcula cuantos ceros hay en el vector creado por la expresión anterior que hemos encapsulado en el nombre "vector_fechas".
Si usamos el nombre "vector_fechas" en nuestra fórmula, obtenemos una versión mucho más fácil de leer y entender:
=K.ESIMO.MENOR(vector_fechas,SUMAPRODUCTO(--(vector_fechas=0))+1)
Otra ventaja de esta sintaxis es que no hay que ingresar la fórmula en forma matricial.
Veamos este ejemplo
Le fecha de vencimiento, antes de las correcciones necesarias, cae el 15/03/2015 que es un domingo; en la tabla de feriados vemos que el lunes y martes siguientes son feriados, de maner que el resultado de la fórmula es el miércoles 18/03/2015.
También podemos usar esta variante:
=K.ESIMO.MENOR(vector_fechas,INDICE(FRECUENCIA(vector_fechas,0),1)+1)
donde el número de ceros en el vector es calculado con la función FRECUENCIA. También en este caso no hace falta ingresar la fórmula en forma matricial.
Una tercera variante es usar la función MIN combinada con SI, en esta fórmula matricial
=MIN(SI(vector_fechas=0,"",vector_fechas))
El cuaderno con el ejemplo puede descargarse aquí.
Gracias por la solución Jorge, efectivamente la fórmula produce mareos, me quito el sombrero.
ResponderBorrarA mi se me ocurrió otra solución, bastante más "rústica" pero que funciona.
Simplemente hay que generar un rango auxiliar con los días hábiles y ordenarlos de forma descendente. A partir de aquí, encontrar al siguiente día hábil a una fecha es fácil con esta fórmula (B1 es el día de partida y B2 los días adicionales).
=INDICE(Habiles;COINCIDIR(B1+B2;Habiles;-1))
Con una fórmula parecida se pueden calcular plazos de días hábiles (p.ej. 10 días hábiles desde una fecha), algo que los abogados hacemos con frecuencia. Sería esta:
=INDICE(Habiles;COINCIDIR(B1;Habiles;-1)-B2)
Donde B1 es el día de partida y B2 los días hábiles del plazo.
Otra vez más, enhorabuena por tu Blog.
Daniel
Daniel, hay soluciones "rústicas" y soluciones "elegantes", pero lo importante es si las soluciones funcionan. A posteriori descubrí que la mía es una solución "complicada" (innecesariamente). Fijate en esta fórmula basada en DIA.LAB (opción que yo había descartado sin investigar a fondo) que me sugirieron en un foro:
ResponderBorrar=DIA.LAB(C5+C4-1,1,Feriados)
Mucho más elegante, sin duda.
ResponderBorrarY con esta otra fórmula excluyes los domingos:
=DIA.LAB.INTL(C4+C5-1,1,"0000001",Feriados)
La cadena de texto es una máscara con la que excluyes uno o más días de la semana, los señalados con "1". El primer carácter es el lunes y el último el domingo.
Curioso que tengas que restar un día (C4+C5-1) y hacer que cuente siempre un día. Si no lo haces así la fórmula falla.
Saludos cordiales