lunes, abril 13, 2015

Vencimientos en día hábil - versión mejorada

En la nota anterior mostré como calcular fechas de vencimientos que siempre caigan en días hábiles, no en fines de semana o feriados. En caso de caer en día no hábil o feriado, la fecha calculada debía ser el primer día hábil anterior a la fecha original. (Para calcular el día hábil posterior, ver esta nota)

Para hacerlo propuse esta fórmula usando las funciones DIASEM, MAX, ESERROR y COINCIDIR junto con una constante vectorial. La fórmula en la celda C7 es

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6))


Esta fórmula tiene algunas debilidades.
La notación de las constantes vectoriales cambia de acuerdo a las definiciones regionales. Para ciertas configuraciones, el vector debe anotarse de esta manera:

=MAX((C5+C4-{7\6\5\4\3\2\1\0})*(ESERROR(COINCIDIR(C5+C4-{7\6\5\4\3\2\1\0};Feriados;0)))*(DIASEM(C5+C4-{7\6\5\4\3\2\1\0};2)<6))

usando \ en lugar de ; lo cual puede confundir a alguno de mis lectores. Para evitar este problema podemos usar esta sintaxis:

=MAX((C5+C4+1-FILA(1:7))*(ESERROR(COINCIDIR(C5+C4+1-FILA(1:7);Feriados;0)))*(DIASEM(C5+C4+1-FILA(1:7);2)<6))

donde creamos el vector usando la función FILA e ingresando la fórmula en forma matricial (apretando simultáneamente las teclas Ctrl - Mayúsculas - Enter).

Otro problema más serio es que la constante vectorial es exactamente eso, una constante. Arbritariamente uso un vector de 8 miembros para generar un vector de ocho fechas, de la fecha de vencimiento hacia atrás, suponiendo que no hay feriados que duren más de una semana. Pero esta suposición no tiene que ser necesariamente cierta en todos los casos. Lo ideal sería que ese vector de fechas fuera dinámico. Para hacerlo podemos basarnos en la fórmula matricial anterior usando la función INDIRECTO para generar un rango dinámico de fechas. La fórmula es:

=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))

INDIRECTO("1:"&C4) crea un vector de tantos valores como días transcurren de la fecha incial a la fecha de vencimiento (en nuestro ejemplo, 30).

4 comentarios:

  1. Hola Jorge,

    ¿Y como se calcularía el siguiente día hábil si el día de vencimiento es domingo o festivo (feriado)?. He probado a invertir las matrices 0/1/2/3/4/5/6/7 y a cambiar el signo pero entonces habría que cambiar también la fórmula "MAX" por "MIN", que siempre dara cero como resultado.

    Gracias por adelantado.

    ResponderBorrar
  2. Jorge
    Excelente la exposición. Tengo un inconveniente. Tengo Excel 2010 (aquí somos pobres....) y obviamente las formulas presentan disparidad de resultados con tu análisis. No detecto lo que esta dentro del paréntesis como un comando =INDICE(rngSemana,DIASEM(C5,2)). y en la formula de determinación del "MAX((.... no reconoce "Feriados". Podre adaptarlo en todo caso al 2010?
    Un abrazo y muchas gracias
    DC

    ResponderBorrar
  3. Para buscar el día hábil anterior partíamos de la fecha de vencimiento, sin correcciones, e íbamos "hacia atrás" (por eso usamos MAX). Para encontrar el primer día hábil posterior a la fecha sin correcciones, tenemos que "ir hacia adelante" y encontrar el valor mínimo (la primer fecha) que cumple con las condiciones. El problema, como has descubierto, es que en el vector aparecen ceros. Una técnica para calcular el mínimo excluyendo los ceros puedes ver en esta nota del blog.
    Más adelante publicaré un post más detallado con la solución.

    ResponderBorrar
  4. Hola Daniel,

    lo prometido es deuda. Aquí está el post con la solución.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.