Crear una lista de días laborables en Excel

Transcripción del video

En esta clase te mostraré algunas técnicas que nos ayudarán a crear un listado de días laborables en Excel lo cual será muy útil para crear un calendario de actividades, o para hacer validaciones de fechas, así como otras tareas que podemos realizar al tener un listado de los días laborables de nuestra empresa o lugar de trabajo.

Para crear nuestro primer listado, iré a la celda A1 e ingresaré la fecha 1 de enero de 2018 que es un día lunes.

Para generar la serie de días laborables, haré clic en el controlador de relleno de la celda A1 y lo arrastraré hacia abajo y al soltar el ratón se mostrará el botón de opciones de Autorrelleno y debemos hacer clic sobre él y elegir la opción Rellenar días laborables de la semana.

De inmediato Excel modificará todas las fechas para mostrarnos solamente los días lunes a viernes.

Para facilitar la visualización de los días, cambiaré el formato de las celdas haciendo clic en la lista de formatos de la pestaña Inicio y eligiendo la opción Fecha larga y de esta manera podremos ver que la serie de fechas solo tiene los días lunes a viernes.

Si quieres generar una serie de días laborables diferente, por ejemplo, de lunes a sábado, entonces deberás utilizar otro procedimiento.

En la celda B1 ingresaré de nuevo la fecha 1 de enero de 2018 y arrastraré el controlador de relleno para tener las fechas de los primeros seis días.

En seguida activaré la celda B7 e ingresaré el símbolo igual y la referencia a la celda B1 que contiene la primera fecha y le sumaré el valor 7 y pulsaré la tecla Entrar y de esta manera obtenemos el inicio de la siguiente semana laboral.

En seguida copiaré la fórmula hacia abajo y tendré la serie de fechas que estoy buscando.

Para ver los días de la semana, seleccionaré todo el rango de fechas recién creadas y cambiaré el formato de las celdas a Fecha larga y podrás ver que nuestro listado de días laborables de lunes a sábado.

En este momento, la celda B7 y todas las celdas inferiores tienen una fórmula con una fecha válida en Excel, pero si queremos deshacernos de las fórmulas y dejar solamente los valores de fecha, debemos seleccionar el rango completo de fechas y copiarlo con el atajo Ctrl + C.

En seguida iré al botón Pegar y abriré el menú para elegir la opción “Pegar valores”, y ya que estamos ejecutando este comando sobre el mismo rango de celdas, hemos removido todas las fórmulas y ahora solo tenemos los valores de fecha como lo puedes ver en la barra de fórmulas.

Con estas dos técnicas que hemos utilizado podrás generar cualquier serie de fechas laborables pero estos dos métodos no consideran los días festivos.

Si en la generación de los días laborables quieres tomar en cuenta los días festivos o las vacaciones, entonces es necesario utilizar algunas funciones de Excel.

Activaré la hoja 2 de este libro y en la columna D puedes notar que tengo el encabezado Festivos y un par de fechas.

Antes de continuar con nuestro ejemplo debo decirte que Excel no tiene una base de datos de días festivos, y eso quiere decir que no es posible que Excel reconozca automáticamente los días festivos del país donde vives.

Por esta razón las funciones que veremos a continuación necesitan un rango de celdas que contenga aquellas fechas que serán excluidas de los cálculos.

La primera función que revisaremos será la función DIA.LAB.

Esta función nos ayuda a obtener la fecha después de un numero especificado de días laborables.

Es decir, si quiero contar 15 días laborables después del primero de enero, la función DIA.LAB nos devolverá la fecha de ese día.

Esta función tiene 3 argumentos.

El primero de ellos es la fecha inicial y el segundo es el número de días laborables que la función va a contar a partir de la fecha de inicio.

El tercer argumento es opcional, pero nos permite indicar un rango de celdas con fechas que serán excluidas del cálculo.

Será en este argumento en donde indicaré el rango de celdas con los días festivos.

En la celda A1 ingresaré el símbolo igual y la función DIA.LAB y como primer argumento colocaré la fecha 1 de enero de 2018 encerrada en comillas dobles.

Ahora quiero que pongas atención a lo que voy a decir sobre el segundo argumento ya que dicho valor es lo que me ayudará a generar el listado de días laborables.

Necesitamos que el segundo argumento vaya aumentando automáticamente conforme copiamos la fórmula hacia abajo.

Nuestra primera fórmula tiene que devolver la fecha 1 de enero y para eso necesito que el segundo argumento de la función DIA.LAB sea el valor cero.

Para obtener el 2 enero, necesito que el segundo argumento sea el número 1, para obtener el 3 de enero necesito el número 2 y así sucesivamente.

Como puedes ver, cada vez que copiamos la fórmula hacia abajo, necesito que el segundo argumento se incremente en uno.

Algo que cambia automáticamente al copiar una fórmula hacia abajo es el número de fila, por lo que puedo utilizar la función FILA en este segundo argumento para que me devuelva el número de la fila actual.

Si estoy en la celda A1, la función FILA devolverá el valor 1, pero yo necesito que la primera fórmula tenga el valor cero en su segundo argumento así que puedo restar el valor 1 al resultado devuelto por la función FILA.

De esta manera, para la primera fórmula obtendremos el valor 0, para la segunda fórmula el valor 1 y así sucesivamente.

Coloquemos este cálculo en nuestra fórmula, ingresaré la función FILA y sus paréntesis y en seguida restaré el número 1.

Solo para probar el funcionamiento de este segundo argumento, cerraré los paréntesis de la función DIA.LAB y pulsaré la tecla Entrar.

En este caso he obtenido el valor numérico de la fecha pero puedo ir al listado de formatos de celda y elegir la opción Fecha larga y observarás la fecha 1 de enero de 2018.

En seguida copiaré la fórmula hacia abajo y algunas celdas mostrarán el símbolo numeral, para decirnos que el contenido de la celda no cabe en el ancho de la columna.

Haré doble clic en el borde derecho del encabezado de la columna A y se mostrará el contenido de todas las celdas.

Observa que todos los días se van incrementando uno a uno considerando solamente los días lunes a viernes.

Ahora que hemos comprobado que el segundo argumento de la función DIA.LAB funciona correctamente, voy a modificar la fórmula de la celda A1 para pedirle a la función que descarte los días festivos que tengo en el rango D2:D3.

Antes de hacer la modificación, quiero que observes que el listado que acabamos de generar contiene el día 15 de enero y el día 22 de enero que son festivos y que vamos a eliminar de la lista con el siguiente cambio en la fórmula.

Editaré la celda A1 y en el tercer argumento de la función ingresaré la referencia al rango de días festivos que es la referencia al rango D2:D3.

Pero necesito que esta referencia sea absoluta para que se mantenga fija al momento de copiar la fórmula hacia abajo.

Seleccionaré con el ratón la referencia y pulsaré la tecla F4 para convertirla en una referencia absoluta y finalmente pulsaré la tela Entrar.

En seguida arrastraré el controlador de relleno de la celda A1 hacia abajo para copiar la fórmula y podrás notar que tenemos nuestro listado de días laborables, que excluye el día 15 de enero y el día 22 de enero.

De esta manera, la función DIA.LAB nos ha ayudado a crear un listado de días laborables de lunes a viernes que excluye los días festivos que hemos indicado.

Todo parece funcionar correctamente, pero la limitante que tiene esta fórmula es que no funcionará si comenzamos nuestra lista en una fila diferente a la fila 1 y eso te lo demostraré a continuación.

Activaré la celda A1 y copiaré el texto de la barra de fórmulas y en seguida activaré la celda F5 y pegaré el texto de la fórmula.

Al pulsar la tecla Entrar obtendré el número de la fecha así que cambiaré el formato de la celda a Fecha larga, pero en lugar de obtener el 1 de enero, nuestra fórmula comenzará con el 5 de enero y eso se debe a que la función FILA devuelve el número 5 que corresponde a la fila de la celda F5.

Para resolver este problema, editaré la fórmula para pedirle a la función FILA que comience el cálculo con la celda A1.

Y para eso debemos colocar dicha referencia como el argumento de la función FILA y al pulsar Entrar obtendremos el 1 de enero.

Copiaré la fórmula hacia abajo, y de nuevo haré doble clic en el borde derecho del encabezado de la columna F para ajustar automáticamente el ancho de la columna y verás que el listado de fechas se ha generado correctamente excluyendo el día 15 y 22 de enero.

Esta última modificación a la fórmula solo es necesaria en caso de comenzar el listado de fechas en una fila que no sea la fila 1.

La función DIA.LAB nos ha ayudado a generar el listado de días laborables de lunes a viernes, pero si queremos utilizar otro tipo semana laboral tenemos que utilizar la función DIA.LAB.INTL que nos permitirá elegir los días que son considerados como el fin de semana.

La función DIA.LAB.INTL trabaja de una manera muy similar a la función DIA.LAB y solo tiene un argumento adicional, pero revisemos con detenimiento cada uno de sus argumentos.

El primer argumento es la fecha inicial a partir de la cual se iniciará el cálculo de fechas.

El segundo argumento es el número de días laborables que serán contados a partir de la fecha inicial y el tercer argumento es el que nos permitirá elegir los días de la semana que son considerados como el fin de semana.

La función DIA.LAB no nos dejaba hacer esta elección, sino que siempre considera el sábado y el domingo como el fin de semana, sin embargo, la función DIA.LAB.INTL nos ofrece diferentes opciones y las cuales puedes observar en pantalla en este momento.

Nosotros debemos ingresar el número que corresponde a la opción que necesitamos.

Si quiero generar un listado de fechas laborables donde solo se considere el día domingo como el fin de semana, entonces debo elegir el número 11.

El cuarto argumento de la función DIA.LAB.INTL es el rango de celdas que contiene los días festivos y será aquí donde ingresaré la referencia al rango D2:D3.

Pero regresemos a nuestra hoja para crear la fórmula.

Recuerda que voy a crear un listado de fechas laborables que considera el día domingo como el fin de semana y además debo excluir los días 15 y 22 de enero que son los días festivos.

En la celda B1 ingresaré el símbolo igual y enseguida la función DIA.LAB.INTL y como primer argumento ingresaré la fecha del 1 de enero del 2018 entre comillas dobles.

El segundo argumento es el número de días a contar a partir de esta fecha, y utilizaré de nuevo la función FILA y le restaré el número 1, tal como lo hicimos en nuestro ejemplo anterior.

Al momento de ingresar el tercer argumento, se muestra el listado de opciones disponibles para el fin de semana que será considerado por la función y elegiré el número 11 para pedirle que se considere el día domingo.

Como último argumento de la función ingresaré la referencia al rango D2:D3 que contiene los días festivos y de nuevo seleccionaré el rango y pulsaré la tecla F4 para convertir la referencia en absoluta.

Finalmente cierro el paréntesis y pulso la tecla Entrar y obtengo el número de serie de la fecha. En seguida iré a la lista de formatos y elegiré la opción Fecha larga.

Copiaré la fórmula recién creada hacia abajo y haré doble clic en el borde derecho del encabezado de la columna B para ajustar el ancho y mostrar correctamente las fechas.

En este listado puedes notar que no se incluyen los domingos y además se ha excluido el día 15 y el día 22 de enero.

De esta manera puedes generar rápidamente el listado de días laborables que mejor se ajuste a tus necesidades, ya sea utilizando la función DIA.LAB o la función DIA.LAB.INTL