2do Ejemplo: Calculando fechas en base a días hábiles con dia.lab

Para la mayoría de las empresas, es muy necesario hacer operaciones con los días laborables de un mes por diferentes razones; puede ser vencimientos de facturas, pago de bonos por días trabajados, fechas tope de créditos, entre muchas mas. Para ésto no hace falta tener un calendario y un lapicero para contar todos los días laborables que hubo en un mes o en un periodo delimitado, como demostraré a continuación (Compatible con Excel 2007 en adelante, si posee Excel 2003 o necesita realizar cálculos en base a 6 días laborales por semana en Excel 2007 siga este vinculo para ver como hacerlo).

Antecedente: tenemos nuestra empresa recuperadora de créditos, debemos elaborar un cronograma correcto de los clientes a los que debemos cobrar de la manera mas eficiente posible, realizando cobros luego de 5 días hábiles de vencer el crédito, luego 3 días hábiles y por ultimo 1 día hábil después de la última cobranza, sin perder las gestiones realizadas por no tomar en cuenta días festivos o fines de semana.

Ya tenemos la base de datos de los nombres y la fecha en que venció el crédito del cliente, por lo que vamos a empezar a calcular cuando debemos hacer los cobros.

Nota: puedes ver el archivo en la nube a medida que lees el post para mayor entendimiento haciendo clic aquí

Lo primero es colocar los días feriados que hay en un periodo, si sabemos que puede abarcar parte de dos meses es necesario colocar los feriados de ambos meses para no afectar el resultado de los cálculos, en éste caso tomamos los días feriados en Chile para el mes de septiembre, puesto que son varios días seguidos en que celebran la independencia.

Vamos a la celda C2 para empezar a calcular la primera fecha, son 5 días hábiles luego del vencimiento por lo que escribimos la siguiente fórmula:

=DIA.LAB(B2;5;$G$2:$G$4)

Esta fómula esta compuesta por 3 partes, la primera es donde indicamos la fecha inicial, en este caso cuando vence el crédito (B2), luego del punto y coma colocamos la cantidad de días feriados que vamos a sumar a la fecha inicial, como el primer aviso es a los 5 días, colocamos 5, luego vamos a colocar el rango donde se encuentras los días feriados, en este caso es desde G2 hasta G4, podemos hacerlo mas amplio para usar la misma tabla en el futuro, los simbolos $ son para hacer fija una referencia y cuando extendamos la formula a las celdas adyacentes no nos cambie dicha referencia, pues los dias feriados siempre van a estar en el mismo rango.

Nota: Si en vez de mostrar una fecha, muestra un número entero, debemos hacer clic derecho en la celda, vamos a formato de celdas, seleccionamos fecha y aceptamos.

Ahora para el segundo aviso, hacemos la misma formula, pero sustituimos la casilla de la fecha inicial que en este caso seria C2, la fecha que ya calculamos y en la segunda porción de la formula sustituimos el 5 por 3, puesto que éste aviso se realiza al tercer día, el rango de los días feriados no va a variar asi que colocamos el mismo.

=DIA.LAB(C2;3;$G$2:$G$4)

Realizamos de la misma manera la ultima fórmula, en la que sustituimos la fecha inicial y los días a sumar nuevamente por lo que quedaría de la siguiente forma

=DIA.LAB(D2;1;$G$2:$G$4)

Si tenemos un calendario a la mano, rectificamos y verificamos que las fechas sean correctas, en este primer caso los días feriados no nos afectan en nada, por lo que no hay mayor inconveniente. Vamos a extender las formulas hasta cubrir a todos los clientes de la base de datos seleccionando desde C2 hasta E2 y extendiendo hasta la fila 7

Ya tenemos todo el cronograma listo, podemos verificar por ejemplo con Pedro Peña, el primer aviso son exactamente 5 días hábiles, sin tomar en cuenta los sábados y los domingos, luego el segundo aviso debería ser el día 18, pero como éste es feriado lo lleva al siguiente día hábil que seria el lunes 23 y el último aviso, seria un día después.

Así entonces respetamos todos los días feriados y no habrá posibilidades de fallar por algún error involuntario, siempre y cuando las fechas iniciales y los días feriados estén correctos.

Nota: si en la fórmula vamos a utilizar directamente una fecha inicial y no una celda con la fecha almacenada, debemos colocar comillas a la fecha, por ejemplo:

=DIA.LAB("27/07/2013";1;$G$2:$G$4)

Si ademas queremos agregar un poco de interactividad a nuestro cronograma, podemos agregar un formato condicional desde C2 hasta E7 para que nos resalte en un color llamativo el campo que coincida con el día en curso, por ejemplo si el 16 de septiembre se abre éste cronograma, nos resalta todos los cobros que se deben realizar ese dia. Para esto seleccionamos el rango desde C2 hasta E7 y buscamos en "Estilos" de la pestaña inicio el formato condicional para establecer una nueva regla

Seleccionamos la segunda opción (Aplicar formato únicamente a las celdas que contenga), dejamos la opcion "valor de la celda" y en el siguiente campo que por defecto dice "entre" cambiamos a "igual a" y en el campo siguiente escribimos

=HOY()

Luego en el botón debajo del campo de la formula establecemos el formato de la celda, en este caso la rellenaremos de amarillo (Formato > Relleno > seleccionamos el color) y aceptamos.

De ésta manera le decimos a Excel que si una fecha es igual al dia de hoy, la casilla se rellene de amarillo para tener mas vistosidad, esto varía de acuerdo al día en curso en que ejecutemos el archivo.

 Con ésto podemos dar por finalizado el cronograma y empezar a utilizar ésta valiosa herramienta.

Para descargar éste archivo desde SkyDrive, hacer clic en Archivo > Guardar Como > Descargar
Ejemplo: Cronograma en Excel SkyDrive
Ejemplo: Cronograma en Excel Google Drive