Cómo construir una hoja de horas simple que acomode proyectos en Excel 2019

Esta hoja de horas es útil si necesita una estructura más flexible y la capacidad de acomodar múltiples proyectos.

3 pasos a seguir antes de crear una hoja de horas en la preparación de Microsoft ExcelProper puede hacer que la creación de una hoja de horas en Excel sea mucho más fácil. Aquí hay tres consideraciones que debe hacer antes de comenzar el proceso.

El artículo Cómo construir una hoja de horas simple en Excel 2019 hace un seguimiento de las horas trabajadas en una estructura tradicional de registro de entrada y salida, un registro por día. Esto no siempre es adecuado, así que en este artículo, le mostraré un modelo diferente que rastrea los días usando una estructura columnar en lugar de la estructura tradicional de filas. Este modelo apoya proyectos, todos los que necesite. Probablemente no encontrará una solución mágica con ninguno de los dos modelos de hojas de horas, pero puede ajustar y personalizar cualquiera de ellos para que se adapte a sus necesidades.

Más información sobre Office

Estoy usando Excel 2019 (escritorio) en un sistema Windows 10 de 64 bits, pero se pueden usar versiones anteriores. Puede utilizar la edición del navegador para introducir sus horas. Para su comodidad, puede descargar los archivos de demostración.xlsx y.xls.

1. Determine sus necesidades

Como con cualquier aplicación de hoja de horas que usted planea distribuir, usted necesita considerar los siguientes asuntos:

• Cómo validar la entrada para eliminar errores tipográficos y otros datos no válidos. Usaremos la validación de datos en dos puntos.
• 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. Debido a que este modelo utiliza un objeto Table para expandir, la protección de Excel no es útil.
• Cómo proteger la información confidencial, como los números de seguro social, si es necesario. Nuestro ejemplo no contiene datos confidenciales.

2. El modelo

La muestra la hoja de horas completada con todos los adornos. Este modelo soporta una semana de siete días, comenzando con el domingo y terminando con el sábado. A diferencia de la hoja de horas del primer artículo, esta estructura le permite hacer un seguimiento de las horas específicas de los proyectos. Usted puede adaptar fácilmente esta estructura para manejar más de una semana.

Vamos a crear esta sencilla hoja de horas que le permite hacer un seguimiento del tiempo por proyectos.

La hoja tiene un área de entrada para la información del empleado, la empresa y el departamento, incluyendo el período de tiempo (pago. Este modelo no te obliga a»fichar». En su lugar, los usuarios introducen las horas por categoría o proyecto. Como tal, el usuario tiene un poco más de responsabilidad en el seguimiento de las horas de trabajo reales a lo largo del día.

VER: Aproveche el poder de la validación de datos en Excel (PDF gratuito) (ConsejoTecnologico.com)

3. Limite el período de tiempo

El ejemplo utiliza el domingo para identificar el período de pago. Puede introducir un control combinado que enumere todos los domingos del año, pero sería engorroso de usar. En su lugar, vamos a un control de validación que sólo acepta fechas dominicales:

1. Seleccione B2.
2. Haga clic en la ficha Datos 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.
3. En el cuadro de diálogo resultante, seleccione Personalizado en el menú desplegable Permitir.
4. En el control Fórmula, introduzca la siguiente fórmula ):=DÍA DE LA SEMANA($B$2,1)=1
5. Si lo desea, haga clic en la ficha Alerta de error e introduzca un mensaje de error significativo, como se muestra en la . Este paso no es necesario para que la hoja de horas funcione, pero sus usuarios apreciarán la información.
6. Haga clic en Aceptar.

Utilice una fórmula que acepte sólo fechas dominicales.

Introduzca un mensaje de error significativo.

La muestra el resultado de ingresar una fecha que no es un domingo. Puede continuar limitando los valores de entrada, pero no lo haremos en este artículo.

El control de validación de datos rechaza las fechas de entrada no válidas.

4. Automatizar las fechas

Este siguiente paso devuelve las fechas en un solo período de pago haciendo referencia a la fecha de entrada en B2.

Para iniciar la secuencia, introduzca la siguiente expresión en B5:

=IF(B2<>""",B2,"")

A continuación, introduzca la siguiente expresión en C5 y cópiela en D5:H5:

=IF(B5<>""",B5+1,"")

Para extender el período de pago, simplemente agregue más expresiones a la fila 5. Por ahora, las expresiones no devuelven nada porque B2 está en blanco. Introduzca una fecha de domingo; inicialmente, las expresiones devuelven valores de serie. Aplique el formato de fecha corta a B5:H5 para mostrar fechas significativas, como se muestra en la .

Formatear las expresiones de la fila 5 para mostrar las fechas.

5. Añadir una lista de proyectos

Los próximos pasos pueden ser confusos a medida que los implementamos, pero al final todo se reunirá, así que no se preocupe. Nuestra estructura soporta proyectos, y usted puede especificar tantos proyectos como sea necesario en una lista simple. Más adelante, añadiremos un control de validación de datos que hace referencia a esta lista.

Muévase a una nueva hoja e ingrese a la lista mostrada en la . Luego, convierta la lista en un objeto Tabla para que no requiera trabajo adicional para actualizarse:

1. Haga clic en cualquier lugar de la lista.
2. Haga clic en la ficha Insertar y, a continuación, en la opción Tabla del grupo Tablas.
3. En el cuadro de diálogo resultante ), indique si su Tabla tiene encabezados – el ejemplo Tabla los tiene.
4. Haga clic en Aceptar.

Convierta la lista en un objeto Tabla para que pueda actualizarla fácilmente.

No se puede hacer referencia directa a una Tabla en un control de validación de datos, así que vamos a asignar un nombre definido a la Tabla. Primero, necesitamos saber su nombre. Haga clic dentro de la Tabla y luego en la pestaña Diseño contextual. Encontrará el nombre del objeto Tabla en el control Nombre de tabla a la izquierda de la cinta, como se muestra en la . (A menudo es más fácil trabajar con un nombre más significativo que el que asigna Excel. Para cambiar el nombre de la tabla, haga clic en el control Nombre de tabla, introduzca un nombre y pulse Intro. No nos molestaremos con este ejemplo.)

Discernir el nombre del objeto de tabla.

Ahora está listo para asignar un nombre definido a la Tabla como se indica a continuación:

1. Haga clic en cualquier lugar dentro de la Tabla.
2. Haga clic en la ficha Fórmulas.
3. En el grupo Nombres definidos, haga clic en Definir nombre.
4. En el cuadro de diálogo resultante, introduzca ProjectList como Nombre.

1. Haga referencia a la tabla utilizando el formato

1. =Tablename[Columnheader]. En este caso, eso es =Tabla10[Proyectos], como se muestra en la .
2. Haga clic en Aceptar.

Asigne a la Tabla un nombre definido.

Parece que has hecho mucho trabajo extra para crear una lista simple, pero vale la pena. Dado que la lista es un objeto Tabla, puede añadir y eliminar proyectos y el control de validación de datos que añadiremos más adelante se actualizará automáticamente, sin necesidad de trabajo adicional por su parte.

VER: 10 Excel time-savers que quizás no conozca (ConsejoTecnologico.com)

6. Agregar categoría al rango de entrada

Con su lista de proyectos en su lugar, usted está listo para comenzar a construir los rangos de entrada de horas de trabajo. Comenzando en A7, y usando la como guía, agregue una fila de etiquetas de encabezado y etiquetas de categoría. Asegúrese de dejar una fila en blanco (fila 6) entre el rango de entrada de información y el rango de entrada de categoría. El espacio en blanco ayudará a

reducir el ruido visual.

Deje una línea en blanco entre la categoría Tabla y las fechas.

A continuación, siguiendo las instrucciones del paso 5, convierta el rango de entrada de la categoría (A7:H12) en un objeto Tabla. No es necesario, pero el formateo y la actualización serán más fáciles. (Sus formatos no coincidirán con la cifra, así que no se preocupe por eso.

Con la categoría Objeto de tabla seleccionada, haga clic en la ficha Diseño contextual y marque la opción Fila total en el grupo Opciones de estilo de tabla. Seleccione Suma en el menú desplegable añadido a B13. Copia esa función a C13:H13. La figura I muestra la categoría terminada Rango de entrada de la tabla.

Al convertir el rango en una Tabla, puede agregar o eliminar fácilmente nuevas categorías, pero cada categoría requiere sólo una fila por período de pago.

Puede agregar una categoría nueva o eliminar una existente, pero no duplicará una categoría.

7. Añadir el rango de entrada de datos del proyecto

Ahora está listo para agregar el rango de entrada de datos del proyecto, que incluye el control de validación de datos del proyecto que mencioné anteriormente. Introduzca los días de la semana, de domingo a sábado, en B15:H15, dejando una fila en blanco entre los dos rangos de entrada. Para añadir el control de validación de datos del proyecto, seleccione A16 y haga lo siguiente:

1. 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.
2. En el cuadro de diálogo resultante, seleccione Lista en el menú desplegable Permitir.
3. En el control Origen, introduzca =ProjectList ), el nombre definido que le dio a la lista de proyectos Objeto de tabla en el paso 5.
4. Si lo desea, haga clic en la ficha Alerta de error e introduzca un mensaje de error significativo. Este paso no es necesario para que la hoja funcione, pero sus usuarios apreciarán la información.
5. Haga clic en Aceptar.

Cree un control de validación de datos que enumere los proyectos.

En el último paso, se convirtió el rango de entrada de la categoría en una Tabla. Haga esto de nuevo, convirtiendo el rango de entrada del proyecto (A15:H16) en una Tabla (con encabezados. Luego, habilite la Fila de Total como lo hizo para la categoría Tabla.

Es posible que necesite o no una fila de proyecto cada semana. Puede tener 0 filas o varias, dependiendo del número de proyectos que haya trabajado en ese período de pago. La lista de proyectos actual tiene sólo dos proyectos, por lo que tendrá hasta dos filas para el período de pago dado. Si agrega un nuevo proyecto la próxima semana, puede tener hasta tres filas en esta Tabla. A medida que agrega proyectos utilizando la lista de validación de datos, la Tabla, mostrada en la , se expande. Como antes, no se preocupe por los formatos de tabla todavía.

Esta tabla se ajustará al número de proyectos en los que trabaje durante el período de pago.

8. Categoría y totales de proyectos

Para añadir una columna de totales de categoría, seleccione I8, haga clic en Autosuma, resalte B8:H8 y pulse Intro. Modifique la cabecera por defecto a Totales de categoría y aumente el ancho de columna. Repita este proceso para agregar los totales del proyecto a la Tabla del proyecto. Asegúrese de resaltar la fila 16 (la Autosuma podría intentar sumar valores en la columna I.

9. Grandes totales diarios

Actualmente, tiene totales semanales de categoría y de proyecto en la columna I y subtotales diarios en las filas 13 y 17. No tienes un total general diario. Insertar dos filas en blanco entre la fila 5 y la 7. Seleccione B7 e introduzca la expresión

=Tabla 4[[#Totales],[Domingo]]+Tabla 3[[#Totales],[Domingo]].

Copiarlo a C7:H7. En lugar de introducir esas referencias largas, puede hacer clic en B15 y B19.

La muestra la hoja de horas después de deshabilitar las líneas de la cuadrícula y aplicar formatos de Tabla consistentemente a ambos objetos de Tabla. Es un poco extraño tener grandes totales en la parte superior de la hoja, pero esta posición permite que la Mesa del proyecto crezca con impunidad.

La hoja de horas completa.

10. Reglas de negocio

En este punto, su hoja es funcional, pero hay algunas cosas que usted podría querer agregar. Si las celdas en blanco son problemáticas para su sistema contable, añada ceros a los rangos de entrada en la categoría y en los objetos de tabla del proyecto. Para ello, introduzca un 0 en una celda vacía y cópielo en el Portapapeles. A continuación, seleccione B10:H14 (el rango de entrada de la categoría) y pulse F5. En el cuadro de diálogo resultante, haga clic en Especial. Seleccione la opción Blancos y haga clic en Aceptar. Presione Ctrl+v para pegar el 0 del Portapapeles en las celdas en blanco seleccionadas. Repita esta tarea para el rango de entrada de la Tabla del proyecto, B18:H18.

Los usuarios pueden borrar los 0 y dejar en blanco accidentalmente, por lo que es posible que desee considerar una regla de formato condicional que resalte las celdas en blanco:

=ISBLANK(inputrange)

A medida que agrega nuevos proyectos a la Tabla de proyectos, la regla resalta toda la nueva fila porque esas celdas están en blanco.

La mayoría de las compañías no permiten horas extras en el mismo día que usted usa el tiempo de compensación, enfermedad o vacaciones.

Puede utilizar la siguiente regla de formato condicional para resaltar los valores en B12:H14 cuando el valor de horas extras en la fila 11 es mayor que 0 y la compensación correspondiente, o el valor de enfermedad o vacaciones es mayor que 0:

=Y(B$11>0, O(B$12>0,B$13>0,B$14>0)))

No solucionará el problema, pero alertará al usuario de que existe un problema.

Uso de la hoja

Cada período de pago, el usuario abrirá el archivo de plantilla 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 Figura M muestra las horas acumuladas para la semana laboral que comienza el 25 de marzo de 2019. Harkins trabajó un total de 44 horas; 31 en categorías fijas y 13 en proyectos específicos. Como puede ver, hay un problema el jueves porque Harkins está reclamando 2 horas extras y 2 horas de tiempo por enfermedad. La única manera de resolver este error es eliminar las horas extras o el tiempo de enfermedad.

Figura M

Usando la sábana.

Hay una limitación que vale la pena mencionar: Si usted trabaja 10 horas en un solo proyecto en el mismo día, no puede identificar dos de esas horas como horas extras. Introduzca 10 en la fila del proyecto y deje que el personal de nómina calcule las horas extras cuando aplique las tasas.

Normalmente protegería la hoja antes de distribuir el libro de trabajo, pero no puede proteger fácilmente una hoja con un objeto Tabla en expansión. Para bien o para mal, tendrá que confiar en que los usuarios sean algo competentes con esta hoja de horas. Siempre tienen la plantilla para empezar de nuevo, si ocurre lo peor.

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.

Ver también: