Administración de horas de trabajo en Excel.

Supongo que si se hiciera una estadística sobre los usos de Excel, las herramientas para la administración de horas de trabajo figurarían entre los primeros lugares.
Excel está "hecho a medida" para estas tareas, pero para construir este tipo de herramientas tenemos que comprender primero como maneja Excel el tema de las fechas y las horas.
En esta nota haremos una revisión integral del tema y daremos algunos ejemplos prácticos.

Excel considera las fechas y las horas como números sucesivos de una serie. Ésta comienza el 1ro. de enero de 1900, fecha a la Excel le asigna el número 1. El 2 de enero está representado por el número 2 y así sucesivamente.
Las horas son la parte decimal del número. Un día completo son 24 horas, de manera que las 12 del mediodía está representado por el número 0.5; las seis de la mañana por el 0.25, las 6 de la tarde por el 0.75, etc. En este momento es el 9 de febrero a las 9:50 de la mañana. El resultado de la función AHORA() es 09/02/2008 09:50:36



Si cambiamos el formato de la celda a "General" veremos el número 39487.4101446759



donde 39487 es el número de días transcurrido desde el 01-01-1900 hasta hoy y 0.4101446759 resulta de dividir 35346 segundos (el equivalente de 9 horas, 50 minutos y 36 segundos) por 86400 (la cantidad de segundos que hay en un día).

Otro aspecto importante a tomar en cuenta, antes de abocarnos a la tarea de construir herramientas para administrar horas, es el formato de números en Excel y el de las fechas y horas en particular. Cambios de formato no alteran el número sino como es representado por Excel en la pantalla. Supongamos una tabla donde ponemos el comienzo de una tarea, las horas a trabajar y el resultado será la hora de finalización



Para ver el resultado, debemos cambiar el formato de la celda, que ha sido "heredado" de la celda A2, por el formato hh:mm



Extraño, ¿no? Para entender este resultado volvemos a cambiar el formato de las celdas a "General"



Sucede que cuando ingresamos 15:30 en la celda A2, Excel la interpreta como el 1ro. de enero de 1900 a las 15:30. Al poner 8 en la celda B2, por ser un número entero, es interpretado como 8 días. El resultado en la celda C2 será el 8 de enero de 1900 a las 15:30. Esto lo podemos ver cambiando el formato de las celdas a dd/mm/yyyy hh:mm



Para evitar resultados erróneos debemos ingresar los datos de horas con formato horario.

En este ejemplo hemos ingresado el dato en la celda B2 como 08:00, lo que Excel interpreta como 8 horas, y por lo tanto el resultado es el esperado



Hay otras cuestiones a considerar cuando trabajamos con horas y fechas en Excel, como el manejo de resultados negativos, suma de horas por encima de 24 horas, distintos sistemas de fechas y más. Todo esto ha sido tratado en distintas notas del blog. Todas las notas sobre fechas y horas pueden verse pulsando el enlace de las etiquetas en la columna izquierda del blog



Consideremos ahora este ejemplo



Todas las celdas de la tabla tienen el formato hh:mm. El Total Bruto es la cantidad de horas transcurridas desde el ingreso hasta la salida, sin tomar en cuenta el descanso. La fórmula de Total bruto es =(E5-B5)+(E5<B5)*24. El objetivo de la expresión (E5<B5)*24 es corregir el resultado cuando la hora del final es menor que la del principio. La explicación completa de esta fórmula la encontrarán en la nota sobre diferencia de horas en Excel. Más adelante veremos que esta fórmula tiene un serio inconveniente.

La fórmula de horas trabajadas es =F5-((D5-C5)+(D5<C5)*24), donde nuevamente usamos la expresión (D5<C5)*24 para corregir el error que se genera cuando la salida sucede al día siguiente de la entrada.
La columna Horas Regulares la calculamos con esta fórmula: =MIN(G5,$E$1) donde $E$1 es la celda que contiene la cantidad de horas regulares (en nuestro caso 8). La función MIN nos da el mínimo entre la jornada regular y las horas trabajadas.
Para calcular las horas extras usamos la fórmula =MAX(0,G5-H5). Es decir, el máximo entre 0 y la diferencia entre las horas trabajadas y las regulares.

Supongamos ahora que queremos totalizar el total de horas del día. Como ya hemos explicado, las celdas del total tendrán el formato [h]:mm.



Como ven, los resultados parecen incorrectos! Esto se debe a que nuestra fórmula de corrección no nos sirve en este caso. En lugar de la fórmula =(E5-B5)+(E5<B5)*24 en Total Bruto, usaremos la fórmula =RESIDUO(E5-B5,1) en la columna de Total bruto y la fórmula =F5-(RESIDUO(D5-C5,1)) en la Horas trabajadas.
Ahora los totales son correctos




Technorati Tags: MS Excel

Comentarios en celdas de Excel sin nombre de usuario.

En una nota anterior sobre comentarios en celdas, mostramos las ventajas de usar la barra de herramientas Revisión.
Al insertar un comentario, Excel pone automáticamente el nombre del usuario en el formulario de la nota. Este nombre es el que figura en la pestaña General del menú Opciones




A veces queremos usar el comentario sin que aparezca el nombre del usuario. Por lo general este dato no tiene ninguna importancia y sólo ocupa lugar en el formulario.
Si quitamos el nombre del usuario en el menú Opciones, descubriremos que Excel trae el nombre de usuario que se ha asignado en Windows.
Borramos el nombre de usuario, dejando la casilla en blanco y pulsamos Aceptar



Al poner un comentario veremos que aparece un nombre de usuario en el formulario



Si abrimos nuevamente el menú de Opciones, veremos que Excel se ha preocupado de insertar el nombre de usuario de Windows.

Lo que podemos hacer es recurrir a una macro. Programamos dos eventos, a nivel del cuaderno (ThisWorkbook). Para hacer el trabajo más eficiente, programamos el evento BeforeDoubleClick, pero a nivel del cuaderno para que esté disponible para todas las hojas

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
   With Target
     .AddComment
     .Comment.Visible = True
     .Comment.Shape.Select
   End With
End Sub

Esta macro abre un comentario en la celda activa al hacer doble clic con el botón izquierdo del mouse.
Para evitar tener que cerrar el comentario manualmente, programamos, también a nivel del cuaderno, el evento SelectionChange,


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub


Los comentarios se abrirán ahora sin nombre de usuario.

Technorati Tags: MS Excel

Agregar datos en la primer fila libre de la tabla

Excel no es la herramienta más adecuada para manejar bases de datos. Pero cuando se trata de cantidades limitadas de datos y sin vínculos complicados, Excel puede ayudarnos.

Por lo general, lo que hacemos es construir en una hoja una tabla (o lista) a la cual iremos agregando datos. También podemos crear una especia de formulario en otra hoja usando controles y también usar validación de datos.

Un ejemplo de este tipo de "aplicación" ya hemos mostrado en la nota Agrupar controles botones de opción en hojas Excel.

Una de las consultas que recibo con cierta frecuencia es: "cómo hago para que al agregar datos a mi base de datos, lo haga en la primer fila libre de la tabla?"

El archivo del ejemplo de la nota mencionada incluye una macro (de hecho dos, una para cada método de crear el formulario) que realiza esa tarea.

La macro es muy sencilla y fácil de adaptar a todo tipo de aplicaciones. Para los lectores sin experiencia en Vba, daremos aquí una explicación de la macro.


Sub clientes_form()
    Dim linea_libre As Long

    Application.ScreenUpdating = False

    Sheets("Clientes").Select
       linea_libre = WorksheetFunction.CountA(Range("A:A")) + 1
       Cells(linea_libre, 1).Value = Sheets("Formularios").[I2]
       Cells(linea_libre, 2).Value = Sheets("Formularios").[I3]
       Cells(linea_libre, 3).Value = Sheets("Formularios").[I4]
    Sheets("Formularios").Select

    Application.ScreenUpdating = True

    MsgBox "Se ha agregado el cliente " & Sheets("Formularios").[I2]

End Sub

Empezamos declarando una variable que contendrá el número de la primer fila disponible

Dim linea_libre As Long

Para calcular cuál es la primer fila libre, seleccionamos la hoja Clientes

Sheets("Clientes").Select

y usamos en el código la función CONTARA de Excel para establecer el valor de la variable

linea_libre = WorksheetFunction.CountA(Range("A:A")) + 1

Luego fijamos los valores de las celdas en clientes

Cells(linea_libre, 1).Value = Sheets("Formularios").[I2]
Cells(linea_libre, 2).Value = Sheets("Formularios").[I3]
Cells(linea_libre, 3).Value = Sheets("Formularios").[I4]


y volvemos a la hoja Formulario

Sheets("Formularios").Select

Para evitar que Excel "parpadee" durante la corrida de la macro, usamos la orden Application.ScreenUpdating = False y al final de la macro, antes de la aparición el mensaje, reponemos Application.ScreenUpdating = True.

Esta macro está ligada al botón "Agregar Cliente" en la hoja Formulario.

Technorati Tags: MS Excel