Calcular fecha de vencimiento en día hábil

Como muchas otras, también esta nota se originó en la consulta de un lector. Éste necesitaba computar fechas de vencimiento pero de manera tal que si la fecha de vencimiento caía en un feriado, la fecha fuera corregida al día hábil anterior (para el día posterior, ver esta nota). En sus propias palabras:

supongamos que la fecha inicial es 1 de abril del 2015, y en 30 días la persona tiene que pagar, esto quiere decir el 1 de mayo de 2015, PERO la fecha de pago no puede caer ni en dias festivos ni en fines de semana. se que existe la formula DIA.LAB.INTL pero no me sirve porque me omite todos los fines de semana o todos los domingos, para esto la fecha de pago tiene que ir antes (en caso de ser festivo o fin de semana); supongamos, si cae en domingo la fecha pasa para el viernes de esa misma semana, no para el lunes

Como se trata de días corridos, el proceso de cálculo tiene que ser el siguiente:

1. partimos de la fecha inicial y le sumamos 30;
2. evaluamos el resultado para saber si cae en un fin de semana o día festivo (para los días festivos necesitaremos un rango que los contenga);
3. si el resultado cae en día hábil, el cálculo queda concluido;
4. si el resultado cae en feriado, calculamos cuál es el día hábil anterior más cercano.

Este último punto es el más problemático ya que puede darse la situación de dos o más días festivos corridos.

Consideremos este ejemplo:

• En el rango F4:F8 tenemos la lista de feriados (arbitrarios, a los únicos efectos del ejemplo); 
• en la celda C4 el período en días corridos al vencimiento; 
• en la celda C5 la fecha inicial; 
• en la celda C6 el cálculo de la fecha de vencimiento sin correcciones que calculamos con la trivial fórmula =C5+C4. 
• Para mayor comodidad he definido un nombre que contiene los días de la semana lo que permite calcular el día de semana de cada fecha, como en la celda D5 con esta fórmula =INDICE(rngSemana,DIASEM(C5,2)).

Como podemos ver el vencimiento cae un sábado, por lo cual debemos corregir al viernes anterior, es decir, un día atrás. Si este fuera siempre el caso nuestra fórmula sería relativamente sencilla: si el día de la semana es sábado, restar un día del resultado; si es domingo, restar dos.

El cálculo se complica si el resultado coincide con un feriado. En este caso debemos encontrar la primer fecha anterior que no es ni feriado ni fin de semana.

Consideremos ahora este caso:

La fecha inicial es el 13/05/2015 y por lo tanto la fecha de vencimiento tendría que ser el 12/06/2015 que es viernes. Pero como podemos ver en la lista de feriados, el viernes 12/06/2015 y también el jueves 11/06/2015 son feriados, por lo que el resultado de nuestro cálculo deberá ser el miércoles 10/06/2015.

La fórmula en la celda C7, donde obtenemos el resultado correcto, es ésta:

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

Vamos a explicar esta fórmula paso por paso.

C5+C4 - La fecha inicial más 30 días;

{7;6;5;4;3;2;1;0} - un vector vertical cuyos miembros son números enteros del 0 al 7. Dependiendo de las definiciones del sistema, en lugar del punto y coma (;)  tendremos que usar \ (por ejemplo: {7\6\5\4\3\2\1\0}). También podemos crear el vector vertical usando comas y la función TRANSPONER, por ejemplo: TRANSPONER({7,6,5,4,3,2,1,0}).

C5+C4-{7;6;5;4;3;2;1;0} - esta expresión crea un vector de fechas, en nuestro caso del 05/06/15 al 12/06/15. Podemos visualizarlo usando la auditoría de fórmulas, donde las fechas aparecen con el correspondiente número de serie

(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0))) - crea un vector del mismo tamaño con valores VERDADERO si la fecha no se encuentra en la tabla de los feriados (COINCIDIR dá en ese caso el error #N/A) y FALSO si se trata de un feriado.

(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6) - crea un vector de valores VERDADERO si la fecha cae entre lunes y viernes (incluidos) y FALSO si cae en el fin de semana.

Al multplicar los últimos tres vectores entre si creamos un vector de valor 0 (cuando alguno de los valores es FALSO) o el número de serie de la fecha (cuando los tres valores correspondientes son VERDADERO). Podemos visualizar estas operaciones en esta tabla que muestra en cada fila un elemento de cada vector

Finalmente, la función MAX extrae el valor máximo del producto de los tres vectores (los valores que aparecen en la columna H en la imagen), que es la fecha del primer día hábil anterior a la fecha de vencimiento sin correcciones.

El archivo con el ejemplo puede descargarse aquí

Ver post con una versión mejorada.