Cargo de horas de trabajo con Excel.

Otras de las tareas usuales con Excel es el cálculo de horas a cargar por un proyecto. Sencillamente, calculamos el total de horas y lo multiplicamos por la tarifa. A pesar de lo obvio que parece, debemos tomar en cuenta cómo Excel considera las horas en los cálculos.
Por ejemplo, si hemos invertido 10 horas de trabajo y la tarifa acordada con nuestro cliente es 50 euros, estaríamos tentado ha hacer este cálculo:"10:00 X € 50.00 = € 500". Pongámoslo ahora en una hoja de Excel



El resultado en la celda C2 es "incorrecto". Como explicamos en la nota anterior, esto se debe a que el valor de la celda A2 es en realidad 0.416666666666667 (10 horas dividido por 24 horas).
Para corregir este "error" (que en realidad no lo es) multiplicamos nuestra fórmula original por 24



En la "vida real" las cosas son más complicadas. Por lo general trabajamos con tarifas horarias diferenciales. Supongamos esta tarifa diferencial para cargar un proyecto



La idea es cargar a nuestro cliente con una tarifa decreciente en función de la cantidad de horas.

Siguiendo las indicaciones de la nota anterior sobre el cálculo con horas, construimos esta tabla con las horas trabajadas en el proyecto



Para cargar a nuestro cliente tenemos que dividir las horas de trabajos en porciones de 8 horas. El modelo propuesto es el siguiente



Todas las celdas en el rango A11:B13 tienen el formato [hh]:mm. La celda A14 tiene el formato [h]:mm +, para que el sino + aparezca, sin convertir el contenido de la celda en texto.
Los intervalos los fijamos en forma dinámica, donde la duración del intervalo está definida por las celdas B11 – A11 (8 horas en nuestro caso). Esta diferencia la ponemos en un nombre (escala):



En la celda A12 ponemos la fórmula =B11, en la celda A13, =B12. En la celda B12 ponemos la fórmula =A12+escala, en la celda B13 =A13+escala. De esta manera nuestro modelo será dinámico y se ajustará automáticamente a los cambios en el tamaño del intervalo.
En la columna Horas, en la celda D11 ponemos la fórmula

=MIN(escala,$D$6)

Usamos esta fórmula en lugar de la función SI para determinar si el total de horas trabajadas es menor que el primer intervalo de nuestra tarifa diferencial.
En las celdas del rango D12:D14 usamos la fórmula

=MIN(escala,$D$6-SUMA($D$11:D11))

La expresión SUMA($D$11:D11) va resumiendo le total de horas que ya hemos cargado. Hay que prestar atención a las direcciones absolutas y relativas en la función SUMA.

Para ver cómo el modelo ajusta automáticamente los intervalos, cambiamos el primer intervalo a, por ejemplo, seis horas

Technorati Tags: MS Excel

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