El tiempo de seguimiento es fácil en Excel. Siga estos pasos para crear una hoja de horas básica que puede adaptar a sus necesidades o descargue y personalice nuestra hoja de muestra.
Es casi imposible construir una hoja de control de horas que sirva para todos los casos. Sin embargo, ciertos principios y características están presentes en la mayoría de las aplicaciones de cronometraje. En este artículo, le mostraré una construcción simple para el seguimiento de las horas para una sola semana de trabajo. Es lo suficientemente flexible como para utilizarlo como plantilla de semana a semana y de año a año. Si una sola semana no es suficiente, puede adaptar la hoja para que sea quincenal o incluso mensual.
En nuestro ejemplo de hoja de horas, el lunes es el primer día de cada semana de trabajo. Las horas extras se acumulan diariamente después de ocho horas porque es la más fácil de acomodar en una estructura simple. Puede ajustar fácilmente la hoja para que se adapte a sus necesidades, ya que probablemente no será exactamente lo que necesita.
Más información sobre Office
Estoy usando Excel 2019 (escritorio) en un sistema Windows 10 de 64 bits. Puede utilizar versiones anteriores de Excel. Los usuarios también pueden actualizar su hoja de horas utilizando la edición del navegador de Excel. Usted puede crear la hoja de horas desde cero o descargar nuestra hoja de muestra. Este artículo asume que usted sabe cómo realizar funciones básicas, como introducir y copiar fórmulas, aplicar formatos básicos y aplicar reglas de formateo condicionales.
1: Determine sus necesidades
Al preparar una plantilla para su distribución, debe tener en cuenta los siguientes aspectos:
- Cómo validar la entrada para eliminar errores tipográficos y otros datos no válidos.
- Cómo accederán los usuarios a la plantilla. Puede distribuirlo o permitir que accedan a la hoja a través de un navegador o una aplicación basada en la web. Este artículo no se extiende más allá de un archivo local o del servidor.
- Cómo proteger la plantilla de un mal uso.
- Cómo proteger la información confidencial, como los números de seguro social, si es necesario. Nuestro ejemplo no contiene datos confidenciales, por lo que este paso no es necesario.
2: Introducir etiquetas
La muestra una estructura semanal simple. Aunque la cáscara permite siete días, nuestro ejemplo asume una semana laboral de lunes a viernes. Como tal, trabajar horas extras trabajando más de cinco días no es una posibilidad. Es un ejemplo limitado, pero es un buen punto de partida. Nos basaremos en este sencillo ejemplo en los próximos meses.
Esta estructura simple hace un seguimiento de las horas de trabajo durante una sola semana.
Su hoja contendrá parte de la siguiente información:
- Información del empleado, como nombre, número de seguro social o número de identificación del empleado.
- Información de la empresa, como el departamento y el gerente.
- Un período de tiempo, que puede ser el primer día de la semana laboral o el primer día de un mes fiscal. Sin embargo, sus fórmulas de fecha dependerán de la consistencia.
- Las horas apropiadas, que incluyen las horas de entrada y salida y otras horas reportadas, como las horas de enfermedad, las horas de vacaciones y las horas extras.
- Subtotales y totales generales.
- Firma(s) de aprobación.
El primer paso es agregar etiquetas significativas que describan los valores de entrada y los totales y aplicar formatos simples, como negrita, centro y bordes. Añadiremos fórmulas y aplicaremos características a medida que avanzamos.
3: Automatizar las fechas
El ejemplo utiliza el primer día de cada semana, lunes, para identificar el período de pago. Para evitar errores de entrada, utilizaremos un control de validación de datos que acepta sólo las fechas de los lunes de la siguiente manera:
- Seleccione B2.
- Haga clic en la ficha Datos, haga clic en Validación de datos en el grupo Herramientas de datos y, a continuación, seleccione la opción Validación de datos de la lista desplegable.
- En el cuadro de diálogo resultante, seleccione Personalizado en el menú desplegable Permitir.
- En el control de fórmulas, introduzca la siguiente fórmula ): =DÍA DE LA SEMANA($B$2,2)=1
- Puede definir un mensaje de entrada y una alerta en este punto, pero omitiremos esas opciones para simplificar el ejemplo. Haga clic en Aceptar.
Utilice una fórmula que acepte sólo las fechas de los lunes.
La muestra el resultado de ingresar una fecha que no cae en un lunes. Si desea obtener más información sobre el uso de la validación de datos con fechas, lea Cuatro formas de especificar fechas mediante la validación de datos de Excel. También puede limitar las opciones de usuario a nombres de empleados, departamentos y gerentes específicos que utilicen la validación de datos, pero no lo haremos en este artículo.
La regla de verificación de datos rechaza una fecha no Lunes.
A continuación, añadiremos fórmulas para devolver los valores de fecha y día en las columnas A y B. Primero, seleccione A7 e introduzca la siguiente fórmula:
=IF(B2<>""",B2,"")
Luego ingresar la siguiente fórmula en la celda A8 y copiarla a las celdas A9:A13:
=IF(A7<>""",A7+1,"")
No es necesario indicar el día de la semana, pero sus usuarios probablemente lo encontrarán útil. Ingresar la expresión simple =A7 en B7 y copiarla a las celdas B8:B13
Para ver cómo funcionan las fórmulas, introduzca una fecha (lunes) en B2. Si es necesario, aplique el formato de fecha corta a A7:A13. Luego, aplique el formato personalizado dddd para mostrar el día de la semana en la columna B, como se muestra en la . Como puede ver en la , las fórmulas hacen referencia a la fecha en B2 y devuelven el valor de una semana de fechas y días.
Aplique un formato personalizado para mostrar el día de la semana.
Las columnas A y B muestran las fechas y los días de la semana para el período de pago.
4: Fórmula de tiempo real
Usted podría requerir que sus empleados hagan un seguimiento de sus propias horas extras de acuerdo con sus reglas, pero eso es pedir muchas repeticiones. En nuestro ejemplo, el tiempo recto se aplica a las primeras ocho horas trabajadas en un solo día. (Es la regla más fácil de implementar en una estructura tan sencilla.)
Ingresar la siguiente fórmula en G7 y copiarla a las celdas G8:G13:
=SI ((D7-C7)+(F7-E7)*24>8,8,((D7-C7)+(F7-E7))*24)
Si el total de horas trabajadas en un día es superior a 8, la fórmula devuelve 8. Si el total es igual o menor a 8, la fórmula devuelve esa cantidad. No se preocupe por perder horas extras; nosotros nos encargaremos de eso a continuación.
5: Fórmula de horas extras
Ahora vamos a introducir una fórmula que calcula las horas extras en un solo día. Introduzca la siguiente expresión en H7 y cópiela en H8:H13:
=SI (((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)
6: Formatear valores de tiempo de entrada y salida
Los usuarios entrarán y saldrán valores, lo que significa que estos valores son propensos a errores. Afortunadamente, existen herramientas para ayudar a mantener las cosas en orden. El primer paso es aplicar el formato de hora. Especificaremos el formato de 12 horas, como se muestra en la . Si sus usuarios están familiarizados con el reloj de 24 horas, puede utilizar el formato hr/mm en su lugar.
Aplicar un formato de tiempo para los valores de tiempo de entrada y salida.
Considere la posibilidad de introducir valores de tiempo predeterminados antes de distribuir el archivo. Una vez que un usuario cambia un valor predeterminado, éste desaparece, por lo que debe recibir una formación rudimentaria sobre cómo introducir valores de tiempo para evitar la frustración.
Tal como está, no hay nada que impida que un usuario introduzca un valor de salida que sea menor que su valor correspondiente. Otra regla de validación de datos puede ayudar. Selecciona D7:D13 y aplica una regla personalizada (como hiciste antes) usando la siguiente
fórmula, =D7>C7, como se muestra en la .
Utilice la validación de datos para proteger la validez de los valores de tiempo de salida.
Para proteger la segunda lista de valores de salida, seleccione F7:F13 y utilice la expresión =F7>E7 como regla de validación. Como se puede ver en la , he añadido un mensaje de error significativo a la regla de validación.
Figura
H
La validación de datos requiere que un valor de salida sea mayor que su valor correspondiente.
También se puede añadir una regla de validación a los valores de las columnas C y E. Específicamente, combine un enlace temporal con un ajuste Entre que acepte un valor de tiempo completo de 24 horas al día.
Si lo hace, rechazará cualquier otra cosa que no sea un valor de tiempo. Los usuarios todavía pueden introducir la hora equivocada, pero al menos será un valor de tiempo.
7: Formatear el tiempo de enfermedad y vacaciones
Los usuarios introducirán las horas de enfermedad y de vacaciones como dígitos. Aplique el formato Numérico con dos decimales para acomodar horas parciales o 0 si las horas deben ser en unidades enteras. Además, establezca una regla de validación de datos que permita a los usuarios introducir valores no superiores a su jornada laboral habitual .
Limite el número de horas que un empleado puede reclamar por enfermedad o vacaciones en un solo día.
8: Totales diarios
Todas las celdas de entrada están listas para funcionar. Ahora, introduzcamos una fórmula que calcula los totales diarios. Para ello, introduzca la siguiente función en K7 y cópiela en K8:K13:
=SUMA(G7:J7)
9: Totales semanales
Lo más probable es que tenga que calcular los totales semanales para cada categoría (derecho, horas extras, enfermedad y vacaciones. Para ello, introduzca la siguiente función en la celda G14 y cópiela en las celdas H14:J14:
=SUMA(G7:G13)
Todavía no hay fórmula en el K14. Este valor debe ser el mismo tanto si se suman los valores de la columna K como los de la fila 14. Usted puede detectar errores cruzando las dos sumas. Verifica doblemente los totales comparando los subtotales de las columnas y las filas. Para validar la integridad de sus datos, utilice la siguiente expresión en K14:
=SI(SUM(G14:J14)=SUMA(K7:K13),SUM(G14:J14),"Error!")
La única manera de que esta fórmula devuelva el mensaje de error es si alguien cambia accidentalmente una de sus fórmulas. Eso no puede pasar si proteges tu sábana. Sin embargo, no hay nada malo en incluir esto en su hoja, porque no siempre será así.
10: Algunos extras
En este punto, su hoja es funcional, pero hay algunas cosas que usted podría querer agregar. Por ejemplo, las celdas en blanco pueden ser problemáticas para su sistema contable. Si es así, introduzca una regla condicional utilizando una fórmula en el siguiente formulario para resaltar celdas en blanco:
=ISBLANK(referencia de celda)
Si sigue esta ruta, cambie el formato de hora para las columnas de entrada y salida al reloj de 24 horas para que también pueda introducir valores de hora de 0:00. El formato am/pm no soporta valores de tiempo de 0:00.
Algunas compañías no le permiten usar el tiempo de enfermedad o de vacaciones para ganar horas extras en el mismo día. La regla de formato condicional
=Y(SUMA(G7:J7)>8, O(I7>0,J7>0)))
resaltará los totales diarios que sean superiores a 8 si el valor de enfermedad o vacaciones es superior a 0.
Hay muchas reglas de negocio que puede incorporar en esta hoja. Por ejemplo, este ejemplo no tiene en cuenta el tiempo de compensación. Tampoco identifica el pago de los días feriados. El ejemplo no es adecuado para turnos de dos días. Como puede ver, un ejemplo simple puede volverse complejo con bastante rapidez.
11: Proteger las fórmulas
Una vez que la hoja esté lista para su distribución, proteja sus fórmulas de un mal uso. Para proteger las células, haga lo siguiente:
- Haga clic con el botón derecho en una celda de entrada (una celda que no desea proteger.
- Haga clic en la ficha Protección (si es necesario.
- Desmarque la opción Bloqueado y haga clic en Aceptar.
- Haga clic en la ficha Revisar y, a continuación, en Proteger hoja.
- En el cuadro de diálogo resultante, introduzca una contraseña.
- Marque sólo un elemento: Seleccione Celdas desbloqueadas .
- Haga clic en Aceptar.
Proteja su sábana.
Para simplificar el proceso, puede crear una selección de varios rangos y desbloquear todas las celdas de entrada al mismo tiempo. Simplemente mantenga presionada la tecla Ctrl mientras hace clic en las celdas. Nuestra hoja de horas de muestra no está protegida. Una vez habilitada la protección, los usuarios pueden seleccionar sólo las celdas de entrada.
Uso de la hoja
Cada período de pago, el usuario abrirá el archivo original y lo renombrará para reflejar el período de pago actual. No es la única manera, por supuesto, pero es la más fácil. Los empleados pueden presentar una copia impresa o el archivo electrónico.
La regla de las horas extras de ocho horas es la más fácil de implementar en una estructura simple.
En los próximos meses, veremos algunos ejemplos más que utilizan una estructura diferente y aplican diferentes reglas de horas extras.
Boletín semanal de Microsoft
Conviértase en un experto en Microsoft de su empresa con la ayuda de estos tutoriales de Windows y Office y de los análisis de nuestros expertos sobre los productos empresariales de Microsoft. Entregado Lunes y Miércoles
Envíeme su pregunta sobre Office
Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. No envíe archivos a menos que se le solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se ponga en contacto conmigo, sea lo más específico posible. Por ejemplo, «Please troubleshoot my workbook and fix what’s wrong» probablemente no obtendrá respuesta, pero «Can you tell me why this formula is’ t returning the expected results? Por favor, menciona la aplicación y la versión que estás usando. ConsejoTecnologico.com no me reembolsa por mi tiempo o experiencia en ayudar a los lectores, ni tampoco pido una cuota a los lectores a los que ayudo. Puede ponerse en contacto conmigo en susansalesharkins@gmail.com.