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

lunes, abril 13, 2015

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 comments:

Daniel,  13 abril, 2015 18:37  

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.

Anónimo,  13 abril, 2015 20:27  

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

Jorge Dunkelman 13 abril, 2015 20:31  

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.

Jorge Dunkelman 15 abril, 2015 11:30  

Hola Daniel,

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP