calculo de horas trabajadas - horas negativas

Tema traumático, si los hay. Las Fechas y Horas en Excel siempre traen problemas al momento de intentar la realización de cálculos matemáticos con ellas. Creo que he hablado sobre este asunto en alguna que otra entrada (y no la encuentro...), pero nunca viene mal refrescar algunos conceptos, para luego solucionar el problema planteado por uno de mis lectores. Aquí veremos como trabajar con una tabla que lleva la cantidad de horas trabajadas, con el agregado de que algunos turnos laborales comienzan un día y terminan otro. Quizás esto último parezca un detalle menor, pero notarán que no es asi, ya que una simple función matemática de resta/suma de horas nos daría resultados negativos, inaceptables en Excel.

Vamos a suponer que ingresamos en A1 una fecha cualquiera, por ejemplo:

arbitrariamente puse la fecha de mi cumpleaños... si alguno se acuerda... vieron, los regalos son tan reconfortnates. ;)

[+/-] Ver el resto / Ocultar

Si observamos la barra de fórmulas: 12/01/1974. O sea que indudablemente Excel ha captado nuestra intención y sabe que ahí hay un 12/01/1974. Mentira, no es así. Bueno, una mentira "a medias"

Ahora en B2 ingresaré la misma fecha, pero con formato numérico:

digamos que ni parecido.

Y, lo mejor de todo, es que en ambas celdas (para Excel) hay fechas. Nuestra amada aplicación no "visualiza" las fechas como nosotros, mortales no binarios, si no que lo hace como diferencia de días, partiendo desde el 01/01/1900. Así, el doce de enero de mil novecientos setenta y cuatro... son 27401 transcurridos desde el 01/01/1900 y el 12/01/1974.

Coloco una fecha mas reciente y observamos los resultados:

si, desde el 01/01/1900 y el 12/10/2010 (día de la raza.. o no se que nombre le pusieron ahora) transcurrieron 40463 días.

Aclarado el tema de las fechas, pasemos a las horas. Excel toma a un día como número... el 1. Cualquier dato de hora que nos muestre estará "fraccionando" ese 1. De esta forma, las 06:00 a.m será interpretado como 0,25 lo cual es, obviamente, un cuarto del día. Las 18:00 p.m como 0,75 o sea, tres cuartos del día.

Mostremos un ejemplo, en el cual combinamos en una columna fechas y horas, y en la otra exactamente los mismos datos, pero con formato numérico:

observan los decimales de la columna C? Bueno, representan la hora expuesta en la columna B

Espero haber expuesto hasta aquí, de forma breve y concisa, como "piensa" Excel con fechas y horas.

Y esto... afecta nuestra labor común y corriente con dichos datos? Si, y bastante.

Mi lector está llevando el control sobre horas trabajadas. Vayamos a la planilla que me envía por mail:

en la columna D observamos una simple resta: el horario de salida menos el de entrada. Veamos la primer fila, columna D: efectivamente, trabajó 7 hs... pero a $12,35 la hora, le estamos abonando un total de... $3,60???

Aunque así estemos maximizando las utilidades de la empresa... algo está andando mal. =)

Iremos corrigiendo la tabla de a poco y llegaremos a buen puerto. 

Recuerdan que las horas son para Excel fracciones de 1? Bien, entonces las 07:00:00 de la columna D (que en realidad son la cantidad de horas trabajadas) en Excel equivalen a: 0,291666666666667. Y si arriba dije que las 06:00 son 0,25, es a simple vista lógico ver el por que las 07:00 representan 0,29.

Si a ese 0,29 le multiplico el valor de la hora ($12,35) obtengo, exactamente: $3,60... que es la miserable y módica suma que le estamos abonando a nuestro empleado por 7 horas de trabajo.

Imagen:

Ya sabemos el por qué, ahora vamos a arreglarlo.

En la columna F ponemos la siguiente función:

=(D2*E2)*24

Sip... así de fácil. Multiplico las horas trabajadas (D2) por el precio de la misma (E2) y a ese resultante, por 24, que son las horas que tiene el día. El orden de los factores no altera el producto, pero incluí paréntesis y un cierto ordenamiento para explicarlo mejor:

Si modifico los valores agregando minutos:

el costo total también se actualiza. convendría quitarles les segundos a la columna D, salvo que necesitemos ese nivel de precisión

Ahora supongamos que el empleado4 inicia en turno noche, por ejemplo a las 23:00 hs y finaliza a las 07:00. Entonces, y valga la aclaración, comienza a trabajar un día y termina el otro. Fijemos como horario de entrada el 12/10/2010 a las 23:00. El horario de finalización caería el 13/10/2010 a las 07:00.
Si intentamos resolver esta cuestión de igual forma que los ejemplos anteriormente vistos, este será el resultado:

cuando resto Fin - Inicio.... arroja ######, que es sinónimo de "aca metí la pata"

Para no perder el hilo, les muestro como queda esta situación en nuestra tabla de horarios:

esto sucede por que el horario de salida es anterior al de entrada.

Existen muchas formas de solucionarlo. Recordemos que Excel nos permite colocar valores fecha/hora en una celda, por lo que podría ser factible hacer esto:

incorporando la fecha y hora en una misma celda se acabaron los errores.

Pero quizás resulte un poco cansador y conlleve errores el hecho de agregar la fecha, aunque es indudable que se lleva mejor registro, mas claro y preciso. Propongo otra alternativa, muy sencilla y que se aplica sobre la columna D:
=(C2-B2)+(B2>C2)
Y si analizamos los resultados, funciona:

La función es:
=(C2-B2)+(B2>C2)
En la parte izquierda del + no modifico nada: sigo restando el horario de salida con el de entrada. La novedad la encontramos a la derecha del signo: una función que retorna un valor lógico: VERDADERO o FALSO, que traducido a Excel (o a la programación en gral) resultan un 1 (uno) o 0 (cero), respectivamente.
En el caso del empleado4 (=(C5-B5)+(B5>C5)), la segunda parte de la función analiza: es mayor B5 que C5 ?? . Como la respuesta es sí retorna VERDADERO, o sea un 1. Digamos, entonces, que internamente la función se transforma a: =(C5-B5)+(1). Y ese uno le agrega un día y por eso deja de tirar error.
Si luego del análisis evalúa que es falso, internamente sería: =(C5-B5)+0. Y como al sumarle 0 no agrego ningún día, el resultado no se altera y por ello funciona correctamente en el resto de las celdas.

Puedo poner un ejemplo muy sencillo para aclarar esto de los ceros y unos, falsos y verdaderos.
Tomamos una hoja cualquier y en A1 ingresamos un numero, por ej 10. En A2 ahora ponemos cualquier función que devuelva un VERDADERO, digamos: =3<4 (tres es menor que cuatro)

En A3 realizaremos la suma de A1+A2:

pueden ver como Excel "transforma" ese VERDADERO en el valor 1

Si, por el contrario, hacemos que la función de A2 arroje un valor FALSO:

el FALSO es sinónimo de cero y el resultado no modifica.

Bueno, igual lógica se aplica al problema de las horas "negativas"
Suerte y espero que puedan aplicarlo a sus proyectos.