Hoy vamos a aprender a hacer un bonito sistema de registro de vacaciones para poder construir un calendario a lo largo del año en el cual podremos fácilmente ver quién está disponible y quién no, y así facilitarnos la planeación a futuro de cualquier actividad o proyecto en nuestro equipo de trabajo.
Preparamos nuestro registro
Lo primero que haremos será preparar una sencilla forma de solicitud de registro de fechas de vacaciones en Google Forms, que compartiremos con nuestro equipo. Tendrá simplemente el nombre, la fecha de inicio y la fecha de fin de sus vacaciones. Es posible hacer varios registros para la misma persona y nuestro sistema deberá contemplarlo.
Esta forma de registro la deberemos conectar con una Hoja de Cálculo de Google en la sección de Respuestas – Crear hoja de cálculo.
Ya que hayamos recibido varias solicitudes, agregamos una columna llamada Aprobadas e insertamos casillas de verificación para indicar manualmente si estarán aprobadas o no. También vamos a renombrar la hoja como ‘Vacaciones’ para ayudarnos a identificarla mejor.
Preparamos la hoja del calendario
Ahora vamos a preparar la hoja donde tendremos nuestra vista de calendario, y la llamamos, claro, Calendario.
Aquí, primero le damos un poco de formato y estructura a todo. Tenemos unas celdas donde configuramos en qué día deberá iniciar y terminar nuestro calendario.
Luego, un apartado para listar los días festivos o inhábiles que habrá en el año. A la derecha dejamos una columna libre para que haya un poco de espacio y no confundamos conceptos. Podríamos poner esto en otra hoja, pero es más claro tenerlo aquí mismo.
Y ahora para el apartado principal, tenemos un lugar donde irán los nombres de las personas que han registrado sus vacaciones. Las referenciamos desde la otra hoja con la fórmula UNIQUE para no traernos registros repetidos de los que hayan llenado más de una vez el formulario.
=UNIQUE(Vacaciones!B2:B)
Por último, preparamos nuestro calendario, pensando en que las fechas deberán irse desplegando hacia la derecha. En una fila tendremos el mes, en otra la semana, en otra el día de la semana, y por último la fecha.
Comencemos por el de la fecha, y vamos a resolver todas las fechas con la útil fórmula SEQUENCE, que ya hemos visto antes, y que nos permite enumerar un intervalo. En este caso la secuencia es de una sola fila. Para saber cuántas columnas necesitamos en el argumento de columnas, simplemente restamos la fecha de fin del calendario y la del inicio del calendario. Le pedimos que inicie en la fecha que marcamos, y establecemos 1 como incremento. Puede ser que tengas que agregar más columnas a la derecha de tu hoja para que no te de error la fórmula, tómalo en cuenta.
=SEQUENCE(1,A4-A2,A2,1)
Esto nos llenará fechas hacia la derecha. Te recomiendo dar un poco de formato para que todo se vea más limpio. Lo que yo hice fue solamente mostrar el día con Formato – Número – Fecha y hora personalizados. También ajustamos el ancho de las columnas para que se vea más compacto todo. Para eso, solamente elegimos las columnas, damos click derecho sobre la selección y usamos la opción Cambiar el tamaño de las columnas. Un ancho razonable para lo que haremos es como de 35, pero esto dependerá de tus preferencias. También te recomiendo fijar las columnas y filas hasta este punto con el menú Ver – Inmovilizar para que al desplazarte siempre veas los nombres y las fechas.
Para el día de la semana simplemente referenciamos a nuestra fila de fechas usando ArrayFormula y TEXT con el formato "ddd", que significa que queremos mostrar el día de la semana. ArrayFormula se encargará de llenar las columnas hacia la derecha por nosotros. Nota que estamos agregando también un condicional para que deje vacía la celda si no hay fecha que convertir.
=ArrayFormula(IF(E4:4,text(E4:4,"ddd"),""))
Hacemos algo parecido para el número de semana del año en que nos encontremos en cada día, usando la fórmula ISOWEEKNUM combinada también con ArrayFormula
=ArrayFormula(IF(E4:4,ISOWEEKNUM(E4:4),""))
Y para el mes, hacemos algo un poco diferente para solamente ver el nombre del mes en el primer día de ese mes, pero nada complicado, solamente es una condición IF para asegurarnos de que el día es igual a 1. Si lo es, lo mostramos con la fórmula TEXT y el formato "mmm", que significa las primeras 3 letras del mes.
=ArrayFormula(IF(DAY(E4:4)=1,TEXT(E4:4,"mmm"),""))
Cruzar la información de vacaciones con el calendario
Muy bien, ahora lo que resta es cruzar la información que tenemos sobre los nombres de las personas que registraron vacaciones, las fechas que eligieron y si las aprobamos o no, con cada fecha de nuestro calendario.
Vamos ahora sí a crear nuestra fórmula maestra. Te la voy a compartir primero y luego la analizaremos:
=ARRAYFORMULA(IFERROR(MATCH(1,IF(E$4>=Vacaciones!$C$2:$C,1,0)*IF(E$4<=Vacaciones!$D$2:$D,1,0)*IF($C5=Vacaciones!$B$2:$B,1,0)*IF(Vacaciones!$E$2:$E,1,0),0))>=1)
Primero enlistamos todas las condiciones que necesitamos que se cumplan:
- Que la fecha que estamos probando sea mayor o igual a la fecha de la columna de inicio de vacaciones
- Que la fecha que estamos probando sea menor o igual a la fecha de la columna de fin de vacaciones
- Que el nombre que estamos evaluando coincida con la lista de nombres de la lista de vacaciones
- Que la columna de Aprobadas tenga un valor de VERDADERO
Podemos pensar en estas condiciones como una tabla de verdad, que si la juntamos con el operador de asterisco, nos resultará en VERDADERO solamente en donde todas las condiciones se cumplan.
Esto resultará en un arreglo de unos o ceros dependiendo de las condiciones cumplidas. En este arreglo usamos MATCH para buscar un 1, y lo encapsulamos en IFERROR para evitar el error de cuando no encuentre. Realmente aquí lo que devolverá MATCH será la posición en este arreglo temporal de este 1, que podría ser 2 o 4 o lo que sea.
Y finalmente, encapsulamos todo en ArrayFormula para poder aplicarle a todo el resultado una condición de mayor o igual a 1, porque no nos interesa la posición que regresó MATCH, sino solamente VERDARERO o FALSO si es que hubo un valor ahí.
Ahora arrastramos esta fórmula e insertamos casillas de verificación para que sean más fáciles de visualizar.
Así tenemos nuestro resultado, si la fecha es un día solicitado y aprobado de vacaciones, aparecerá marcado en la casilla.
Contar cuántos días efectivos se están aprovechando
Lo siguiente que le vamos a agregar a nuestro calendario, es el cálculo del número de días efectivos que la persona está aprovechando de sus vacaciones. Esto es muy útil para ser justos y no descontar días adicionales solo porque sus vacaciones coincidieron con días que ya eran inhábiles desde el principio.
Para esto usaremos la fórmula NETWORKDAYS, que vimos ya en guías anteriores. El truco aquí es que tenemos que contar todos los días hábiles primero y luego usar sus propios días de vacaciones como días festivos para saber la diferencia. Fíjate también cómo tuvimos que usar TRANSPOSE, fue necesario para hacer coincidir el arreglo de días festivos (que es un arreglo vertical) con el arreglo filtrado de solo los días que tienen palomita, que es un arreglo horizontal. Esto lo hicimos con la fórmula FILTER. No te culpo si solamente quieres copiar y pegar esta fórmula 😉
=NETWORKDAYS($E$4,MAX($E$4:$4),$A$6:$A)-NETWORKDAYS($E$4,MAX($E$4:$4),{$A$6:$A;TRANSPOSE(FILTER($E$4:$4,$E5:5))})
Formato condicional para mejorar el calendario
Finalmente, vamos a aplicar un poco de formato condicional para que sea más fácil identificar los días relevantes en el calendario y poder planear alrededor de ellos.
Seleccionamos todas nuestras celdas de casillas de verificación y vamos a Formato – Formato condicional. Aquí aplicaremos 3 reglas.
La primera es simplemente si el texto tiene el valor de TRUE o VERDADERO, y la pintamos de un color. Luego usamos el método de ‘La fórmula personalizada es’ en las reglas de formato, y utilizamos esta fórmula para colorear solamente si es un fin de semana:
=IF(OR(WEEKDAY(E$4,2)>5,0),1,0)
Y finalmente nuestra tercera regla será colorear los días festivos, haciendo referencia al intervalo que tiene la lista de días festivos:
=IF(ISNUMBER(MATCH(E$4,$A$6:$A,0)),1,0)
Puedes acomodar la prioridad de estas reglas como te convenga más, por si te interesa destacar antes los días festivos o los días de vacaciones o los fines de semana.
¡Si contamos los cuadros que quedaron verdes en este caso, veremos que coincide con el número de días que calculamos! Lo mejor de todo es que todo esto se llenará solo automáticamente mientras se vaya completando el registro que enviamos al principio.
¡Y listo! Ya tenemos un muy completo calendario en el que podemos ver muy fácilmente quiénes estarán de vacaciones y en qué fechas.
Plantilla terminada para descargar
Guía en video
Si tienes dudas puedes ver esta misma guía explicada en video:
Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.
Si te interesa aprender más sobre Google Sheets, checa nuestro Curso: Aprende Sheets en el que Alekz te guiará para elevar tu aprendizaje de hojas de cálculo al nivel experto.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
