🔝To translate this blog post to your language, select it in the top left Google box.
"El copiar y pegar se va a acabar", le dije hace poco a un amigo funcionario, al verle usar Excel como un principiante.
Copiaba la hoja de febrero y creaba la de marzo, y editaba manualmente las filas de marzo copiando y pegando filas de dos en dos. Lo hacía en un archivo que le servía para revisar su Control Horario de los días de un mes. Según me decía, le costaba menos de 5 minutos copiar y pegar un nuevo mes.
Hace unos días le mandé un Control Horario con el que crear un nuevo mes en segundos, sin copiar ni pegar nada. ¡Y sin equivocarse!
¡Lo automaticé usando únicamente fórmulas de Excel!
¡Sin necesidad de programar nada de código VBA!
En esta imagen animada se ven las hojas de febrero de mi amigo, con su versión "con la que hay que copiar y pegar", y con mi versión "con la que el copiar y pegar se va a acabar".
ATENCIÓN: Este Control Horario no lo he hecho en plan profesional sino didáctico, pues con cada uno de los cambios podrás aprender varias técnicas con las que dominar las hojas de cálculo, ¡como un usuario avanzado!
¡¡¡ Sigue leyendo si quieres dejar de ser un usuario principiante en Excel !!!
Algunos funcionarios españoles ya hacen la jornada reducida de 7:30 horas pero, de todos modos, al indiferente se le aplicará la legislación vigente de 8 horas de jornada diaria, mientras no entre en vigor la jornada reducida en España.
La empresa será la responsable del Control Horario, y será la que pagará las multas si se cometen irregularidades en su registro:
Se quiere establecer en toda España un nuevo Control Horario digitalizado:
Todas las empresas, sin importar su tamaño, estarán obligadas a adoptar un sistema de registro horario digitalizado. La Inspección de Trabajo y la Seguridad Social podrá acceder en remoto y en tiempo real, a cualquier hora del día o de la noche, para intentar detectar irregularidades en el Control Horario de los trabajadores por cuenta ajena de cualquier empresa en España, con lo que "el copiar y pegar se va a acabar"
Aunque creo que, de momento, los funcionarios se libran de este nuevo control, ¡aunque ya hagan la jornada reducida!
Por lo que todo lo que diré a continuación solamente vale como registro auxiliar y personal. Y como hace mi amigo funcionario, servirá para comparar las horas reales con las registradas en el torno de acceso a la oficina. ¡Por si se le olvida fichar alguna entrada y/o salida! ¿O por si se lo salta?
Descarga los cambios en mi Control Horario y pruébalo, tanto en Excel para escritorio como en Excel para la Web.
- Google Drive: Control Horario - PW1.xlsx
- Microsoft OneDrive: Control Horario - PW1.xlsx
Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.
Las hojas están protegidas sin contraseña para que sea fácil analizar las fórmulas y los formatos condicionales.
ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:
Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0
En este vídeo explico los cambios más importantes entre una hoja de cálculo con muy pocas fórmulas, que no calcula casi nada, y una hoja de cálculo con bastantes fórmulas, para calcular casi todo, por lo que, si usas este Control Horario, "el copiar y pegar se va a acabar".
Aunque será mucho mejor si aprendes las técnicas para hacerlo por ti mismo.
Partiendo de la hoja de la izquierda he creado la hoja de la derecha con unos cuantos cambios funcionales y de apariencia que enumeraré a continuación, usando únicamente fórmulas de Excel.
Lo que no voy a cambiar es la costumbre de los funcionarios de no anotar las salidas de media hora para tomar el café mañanero. ¡Que no se entere nadie! 🤫
TRUCO 1: El cambio en el formato de fechas permite automatizar el resto de la hoja, por lo que es el cambio principal para diseñar cualquier calendario u hoja con fechas.
¡Nunca escribas fechas en Excel con números sin formato de fecha!
En la hoja original de mi amigo, que se ve a la izquierda, en el rango A3:A30 están los 28 días del mes de febrero, escritos como números del 1 al 28.
Siempre he propuesto, en los ejemplos que he subido a mi blog, que los números de los días sean definidos con un formato de fecha.
En la hoja de la derecha se muestra el cambio 1 en el rango B6:B33. El cambio de formato se hace seleccionando en la cinta de opciones: Inicio > Número > Categoría Personalizada > Tipo: d
Con la letra d se ve únicamente el día de la fecha, aunque el contenido de las celdas es la fecha completa. Por ejemplo, en B6 está el 1 de febrero de 2025.
La celda B6 contiene el primer día del mes, con la fórmula: =miDía1
La celda B7 contiene el segundo día del mes, con la fórmula: =B6+1, arrastrada a las celdas B8:B36 para obtener hasta 31 días de un mes.
En la hoja de la izquierda no se sabe qué día de la semana son los días de la columna A.
La celda C6 contiene la fórmula: =B6, arrastrada en el rango C7:C36, mostrando el día de la semana de forma abreviada, con el formato de número personalizado: ddd
Este cambio ayuda a rellenar los registros de entrada y salida de un día concreto de la semana pues, por ejemplo, se sabe que el 1 de febrero es sábado.
TRUCO 2: Automatiza el título de la hoja para que sea dinámico e incluso informe de posibles errores.
Si se escriben el mes y el año como textos estáticos, ¡hay que editarlos de nuevo en cada hoja!
En la hoja original, mi amigo ha escrito en A1: FEBRERO y en B1: 2025, por lo que cuando cree una hoja distinta tendrá que cambiar manualmente el mes y/o el año.
En la hoja de la derecha se define el título de la hoja en la celda D2 con la fórmula:
Siendo definidos con el Administrador de nombres:
miFechaError: =FECHA(2000;1;1)
miDía1: =SI.ERROR(FECHA(--IZQUIERDA(Nombre_Hoja;4);--DERECHA(Nombre_Hoja;2);1);miFechaError)
Más adelante explicaré como está definido el Nombre_Hoja.
NOTA: He elegido arbitrariamente el 1 de enero de 2000, pues supongo que nadie querrá registrar las horas de hace 25 años.
Con este título dinámico se puede saber:
-
Si el nombre de la hoja es erróneo, pues debe estar escrito en un formato
especial: con las 4 cifras del año y con las 2 cifras del mes, por
ejemplo: 202502, para febrero de 2025. Avisa del error con este mensaje:
TRUCO 3: Este es mejor truco de todos los cambios hechos en este Control Horario, ¡que el nombre de la hoja indique claramente el año y el mes!
-
Si hay que filtrar el mes para mostrar solamente los días con el mensaje:
Por ejemplo, filtrar febrero para ocultar las 3 filas del rango 34:36.
- Si el mes y año son los de la hoja, por ejemplo:
Con lo que al cambiar el nombre de la hoja, cambiará automáticamente el mes y año.
TRUCO 4: Para obtener el título correcto del mes, la celda D2 contiene el día primero del mes con un formato de número personalizado con texto entre comillas: "Control Horario de "mmmm aaaa
ATENCIÓN: No recomiendo usar celdas combinadas. Siempre que se pueda, hay que evitar combinar un rango de celdas.
TRUCO 5: No se ha combinado el rango de celdas D2:J2, sino que se ha alineado horizontalmente, centrado en la selección. Para ello, seleccionar el rango y seleccionar en la cinta de opciones: Inicio > Alineación > Alineación del texto > Horizontal: Centrar en la selección
En la hoja de mi amigo solo se podían registrar 2 horas de entrada y 2 horas de salida, con lo que solamente se podía registrar el descanso de la comida pues, como hemos dicho, los funcionarios no registran las pausas para tomarse el café. 🤫
Con la hoja de la derecha se pueden registrar dos descansos: uno para tomar café y otro para comer, para lo que hacen falta 3 horas de entrada y 3 horas de salida, en el rango D6:I36.
El formato de estas celdas es personalizado, con horas y minutos: h:mm
En la hoja de la izquierda se pueden escribir horas en sábados, domingos, etc., excepto en la celda B3, que oculta las horas con el color de la fuente de la celda igual al color amarillo del fondo de la celda. Igual es el efecto que se quiso conseguir para los fines de semana, pero alguien se olvidó copiar esta fila en el resto de filas del finde.
Eso sí, si hacemos alguna hora en días festivos o de ausencia, no se sumarán en las columnas F ni G, con las horas reales y el saldo respectivamente, ya que no existe ninguna fórmula en esas columnas.
En algunos casos hay que trabajar en días festivos, en fines de semana o aún teniendo permiso, bien teletrabajando o haciendo horas extraordinarias por causa de fuerza mayor. Eso nunca lo ha contemplado mi amigo funcionario, ni durante la pandemia...
¡Que se lo digan a los valencianos! ¡Que 4 meses después de la DANA, aún siguen haciendo horas sin conocimiento para intentar recuperar la normalidad!
En la hoja de la derecha se pueden introducir horas extras en días en los que no hay obligación de hacer ninguna hora, pues son cero las horas de la jornada. O sea, se pueden registrar horas cualquier día que, en principio, no hay que trabajar.
Como ejemplo se han añadido 3 horas a los 4 sábados, a los 4 domingos y al día con permiso, lo que suma 9x3 = 27 horas más al saldo de horas.
En la hoja de la izquierda hay errores en la suma de las horas reales y en el saldo, pues alguna fórmulas no contemplan nada más que la primera hora de entrada y de salida.
Seguramente se deberá a haber creado las hojas copiando y pegando filas sin excesivo cuidado.
En la hoja de la derecha, las fórmulas de totales suman las 3 horas de entrad y salida.
En la hoja de la izquierda el SALDO de la columna G es un texto imposible de sumar.
En la hoja de la derecha, el rango L6:L36 se ha arrastrado la fórmula:
=REDONDEAR((J6>0)*(MAX(J6;K6)-MIN(J6;K6));5)
Obteniendo la diferencia de horas como valor positivo, con un formato de celdas como número personalizado: h:mm;;;
Para conseguir el signo negativo delante de las horas de saldo diario, se ha empleado un formato condicional.
Se compara si el valor del saldo es negativo con la fórmula:
=O(Y(0<$J6;$J6<$K6);$J6>MAX($D6:$J6)-MIN($D6:$J6))
Y en ese caso se la aplica el siguiente formato de número personalizado: "-"[h]:mm
Lo mismo se hace con el saldo total de horas en la celda L38, con lo que se consigue que aparezcan y se sumen las horas negativas.
En la hoja de la izquierda el SALDO de la columna G es un texto imposible de sumar, con horas positivas y negativas, por lo que el saldo de horas se suma en la columna I auxiliar.
En la columna I las horas negativas se muestran como un error con el símbolo de almohadilla (#).
El saldo total de la celda I31 también será erróneo si las horas son negativas.
En la hoja de la derecha no hace falta una columna auxiliar para obtener el saldo de horas diarias y mensuales.
En la hoja de la izquierda se marcan en color amarillo los sábados y domingos, y en fondo verde las ausencias, copiando y pegando manualmente las filas según los días del mes.
En la hoja de la derecha los fines de semana se marcan automáticamente en fondo de color rojo, y los días de ausencia se marcan en fondo de color verde, usando formatos condicionales, con lo que se consigue automatizar los días sin ninguna hora en la jornada.
La fórmula para los fines de semana es:
=Y($B6>0;O(DIASEM($B6)=1;DIASEM($B6)=7))
La fórmula para los días de ausencia por cualquier motivo es:
=SI.ERROR(COINCIDIR($M6;$P$6:$P$9;0);0)>0
Con esta fórmula se comprueba si el valor de una celda de la columna M es uno de los motivos de ausencia en la lista del rango P6:P9
Para ver los formatos condicionales aplicados a la hoja, seleccionar en la cinta de opciones: Inicio > Formato condicional > Administrar reglas y en la ventana que aparece, seleccionar en el desplegable de Mostrar reglas de formato para: Esta hoja
En la hoja de la izquierda febrero tiene 28 días, pero marzo tiene 3 días más, por lo que habrá que añadir 3 filas más manualmente, lo que puede generar errores. Es lo que ocurre si se te olvida añadirlos o si te equivocas al copiar y pegar los días 29, 30 y 31 de marzo...
En la hoja de la derecha he añadido un filtro en la celda C4 para filtrar automáticamente los días del mes de la hoja, pues siempre hay 31 días para cada mes.
Por ejemplo, hay 28 días de febrero y 3 días de marzo que no aparecen, por estar desmarcado el mes de marzo. Si se marca marzo en la hoja de febrero, el título avisa en color de fondo rojo que hay que:
TRUCO 6: Usar filtros para mostrar u ocultar filas en una hoja.
Y si has llegado a leer hasta aquí te hago un resumen:
- Habrás podido descargar el archivo con todos los cambios.
- Habrás podido visualizar el vídeo explicando los cambios.
- Habrás leído las explicaciones detalladas de los 10 primeros cambios que he hecho.
- Te enterarás que le mandé a mi amigo el archivo para que lo probara, y aún estoy esperando respuesta desde hace una semana. ¿Será porque febrero es muy corto y no le ha dado tiempo de probarlo? ¡Aunque las semanas de cualquier mes son de 7 días! ¿no?
- Si mi amigo me pasa algún día sus registros de horas de marzo, comprobaré que sigue usando nada más que una hora de entrada y una hora de salida pues, como buen funcionario, no descuenta nunca las horas de las pausas: ni para tomar el desayuno, ni el almuerzo, ni la comida. 🤫
- Solamente registra la primera hora de entrada a la oficina y la última hora de salida, con lo que seguro que me pide una versión reducida, ¡que no le voy a hacer en la vida! ¡Que borre columnas manualmente y revise si se rompe alguna fórmula en las celdas y/o en los formatos condicionales! ¡Y, si le sigue funcionando el Control Horario, habrá aprendido algo de estos cambios!
Como este artículo ya es demasiado largo, escribiré un par de artículos más explicando el resto de los cambios.
Continuará con más cambios...
