Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, como podrán comprobar con un clic a la etiqueta "Fechas y horas" en la nube de etiquetas.
En particular el cálculo de duración de jornada laboral, con distintas variantes, en versiones Excel Clásico y también con Power Query.
Un problema que debemos resolver en este tipo de cálculos es cuando la jornada laboral concluye al día siguiente. En esta tabla, por ejemplo, restamos Salida de Entrada para calcular la duración de la jornada
En el caso de Roberto y de Cristina, Excel no puede calcular al diferencia. Excel no puede resolver diferencias de tiempo negativas. Como vimos, la mejor solución es usar la función RESIDUO()
También hemos visto, y recomendado, usar Power Query para este tipo de cálculos (y para muchos otros también). ¿Como solucionamos el problema en Power Query?
Creamos una consulta a la tabla del ejemplo (sin el cálculo de la duración) y restamos [Entrada] de [Salida]
Dándole una releída al post sobre duración de procesos con Power Query, que publiqué hace dos días, detecté lo que no llamaría error pero sí debilidad del modelo. Ese modelo calcula el tiempo que se requerirá para completar un proceso en múltiplos de media hora. Ese parámetro está "hard coded".
Podemos mejorar el modelo dando la posibilidad de definir el grado de precisión del cálculo, por ejemplo 15 minutos o una hora.
Para hacerlo vamos a definir el parámetro precision en una celda, crearemos un nombre definido que se refiera a esa celda y crearemos a continuación una consulta al rango (la celda) de manera que podamos usarla como variable en los cálculos del Power Query.
En un comentario en el post sobre duración de procesos por turnos me pregunta un lector cómo hacer el cálculo tomando en cuenta solamente días laborales y con la posibilidad que la jornada laboral de cada día de la semana pueda ser distinta.
El modelo del post mencionado funciona exclusivamente con fórmulas que, si bien no puede decirse que sean muy complicadas, si requieren una buena medida de experiencia con Excel.
Si a la restricción de los días hábiles le agregamos la posibilidad que cada día tenga una jornada laboral distinta, el grado de complejidad se incrementa exponencialmente. Por lo tanto es importante decidir qué herramienta del arsenal de Excel usar (fórmulas, Vba, Power Query, PowerPivot).
En mi opinión Power Query y PowerPivot han "destronado" al Vba (no digo que se haya vuelto irrelevante pero el nuevo Excel ofrece mejores soluciones para muchos de los problemas que en el pasado solíamos resolver con Visual Basic). Para el caso que nos ocupa voy a utilizar Power Query.
Bien, suficiente introducción; vayamos al grano. Nuestra tarea es:
1 - dadas la fecha y hora del comienzo del proceso y la duración estimada del proceso, en horas;
2 - una definición de días laborales con comienzo y final de cada día (que pueden ser variables)
3 - calcular la fecha de finalización de la tarea/proyecto
Los cálculos de tiempo en Excel tienen sus bemoles como ya hemos visto por ejemplo en el post anterior. Excel (y no sólo Excel) usa una serie de números enteros para representar las fechas. Excel tiene dos años-base distintos para los cálculos de tiempo: 1900 y 1904 de manera que el 1 representa el 01/01/1900 o el 01/01/1904 según el caso.
Fueran cuales fueran las razones (ver el comentario de Jaime en el post mencionado) la base 1900 conlleva un serio problema: Excel considera al año 1900 como bisiesto cuando no lo es (y recordemos que por defecto la base de cálculo es 1900).
Podemos ver este bug haciendo este ejercicio:
creamos una columna con una serie de números enteros del 31 al 61;
copiamos esta serie en la columna contigua.
en la segunda columna cambiamos el formato a Fecha.
Vemos la correspondencia entre el número de serie (31, por ejemplo) y la fecha (31 de enero 1900). Si nos fijamos en el final de nuestra serie vemos el problema: Excel reconoce la fecha 29 de febrero 1900
pero esta fecha ¡no existe! 1900 no es una año bisiesto.
El problema con este bug es que influye en el cálculo de lapsos. Por ejemplo si calculamos
01/02/1900 - 31/01/1900
obtenemos, naturalmente 1; pero si calculamos
01/03/1900 - 28/02/1900
obtenemos, erróneamente, 2.
Es fácil entender por qué Microsoft nunca corrigió este bug.¿Cuántas veces tuvieron que calcular diferencias de fechas que involucren el año 1900?.
Todo empezó cuando un colega en el trabajo copió una tabla de fechas de un cuaderno Excel a otro. Era uno de esos raros días en que su atención esta centrada más en el trabajo que en cosas agradables.
Con sorpresa observó que las fechas cambiaban al ser copiadas de un cuaderno al otro. Esta es la situación (por supuesto que no se trataba de fechas de nacimiento)
Después de rascarse repetidamente el cuero cabelludo, acción que, está por demás decir, no incrementa la capacidad analítica del rascado, decidió consultarme.
Debo reconocer que tuve unos momentos de embarazosa vacilación hasta dar con la clave del misterio. Excel tiene dos bases distintas para los cálculos de fecha: 1900 y 1904.
Como (casi) todos sabemos Excel usa una serie de números enteros para representar las fechas. La base por defecto (para todas las versiones de Excel para Windows) es el 1 de enero del 1900, fecha representada por el número 1. El número 2 representa el 2 de enero de 1900 y así sucesivamente.
Pero sucede que Excel permite también usar como base el año 1904. De manera que el mismo número de serie puede representar dos fechas distintas dependiendo de las definiciones del cuaderno Excel.
De esta manera, al copiar las fechas Excel "traduce" el número de serie a distintas fechas.
En la imagen de arriba podemos ver que la diferencia entre las fechas en los cuadernos es de 4 años; más precisamente 1462 días.
Cambiamos la base en Archivo-Opciones-Avanzadas-Al calcular este libro
En esta captura de pantalla podemos ver cambian las fechas al cambiar el año-base
Volviendo al caso de mi colega, ¿cómo remediar esta situación?
Una posibilidad es cambiar la definición en el cuaderno de mi colega. Esta solución no es recomendable ya que si hay otras fechas en el cuaderno, éstas cambiaran también.
Otra posibilidad es poner el número 1462 en alguna celda vacía y copiarla; luego seleccionar el rango de fechas a modificar y usar Pegado Especial-Valores-Restar para corregir la fechas.
¿Por qué conviven estas dos bases en Excel? Aparentemente por motivos de compatibilidad con las versiones de Excel para Mac. En "Mac" la base es 1904 (la primer versión de Excel fue para Mac).
Como no fuera esta dualidad es problemática y puede conducir a errores importantes.
Como todos sabemos según Excel la historia comenzó el 1 de enero de 1900,. Es decir, Excel nos permite hacer cálculos de fechas sólo a partir de esa fecha. Cualquier intento de ingresar en una celda o hacer cálculos con fechas anteriores resultará en un error.
Hace once años publiqué un post con los posibles remedios a operaciones con fechas anteriores al 01/01/1900. Estas soluciones implican usar macros, como este que ofrece Microsoft para calcular edades con fechas anteriores al 1900, o instalar complementos.
¿Qué sucede en Power Query cuando queremos hacer cálculos con fechas anteriores al 1900? Supongamos esta tabla con las fechas de nacimiento y fallecimiento de hombres y mujeres de ciencia
En Excel las fechas anteriores al 1900 son interpretadas como texto y las posteriores como fechas (las posteriores al 1900 están alineadas a la derecha, como números, mientras que las anteriores a la izquierda).
Nuestra tarea es calcular las edades a las que fallecieron nuestros científicos. Vamos a ver que sucede cuando creamos una conexión a esta tabla en Power Query
Distintos países usan distintas formas de expresar las fechas. En Argentina o en España, por ejemplo, la fecha de hoy se escribe 22/8/2019 (el modelo DD/MM/AAAA); en los Estados Unidos o en Las Filipinas la misma fecha se escribirá 8/22/2019 (MM/DD/AAAA). Ésto se convierte en un problema cuando un argentino o un español tiene que trabajar con datos enviados de los Estados Unidos.
Más precisamente me sucedió al descargar la lista de los suscriptores a este blog. En la lista aparecen el correo electrónico, la fecha de suscripción y el estatus. Las fechas del archivo aparecen con el formato MM/DD/AAAA. Como quería investigar cuantos suscriptores había ganado en cada año tenía que transformar las fechas. Y cuando de transformar datos se trata, echamos mano al Power Query.
En este post voy a mostrar dos métodos distintos de hacerlo. Pero empecemos por visualizar el problema.
Después de crear la consulta al archivo (en este caso .csv) vemos ésto en la ventana del editor
En la primera nota vimos como calcular horas trabajadas por un empleado usando el Power Query. El objetivo del post era mostrar que lo que ya habíamos mostrado como hacerlo con Excel (fórmulas) podía hacerse también con Power Query.
En la segunda nota vimos las grandes ventajas que nos ofrece el uso de Power Query al hacer los cálculos combinando los informes de varios empleados, en forma automática y sin necesidad de recrear las fórmulas con cada nuevo informe.
En ambas notas mostramos el caso sencillo donde no hay que diferenciar las horas por turnos. En este post vamos a recrear el ejemplo del segundo pero agregando la necesidad de calcular cuántas de las horas trabajadas fueron en el turno diurno y cuántas en el turno nocturno.
De hecho vamos a estar recreando en Power Query el modelo que mostré en este post. La lógica aplicada es la misma pero "traducida al idioma" Power Query. Las ventajas, como ya expliqué en las notas anteriores, son:
la robustez del modelo: no hay fórmulas en celdas que puedan ser alteradas desapercibidamente;
la facilidad de combinar un gran número de fuentes de datos y crear informes;
actualización de los datos e informes con sólo apretar un botón.
Para este ejemplo vamos a usar los mismos archivos del segundo post (los informes de los empleados que guardamos en la carpeta Informes).
Al crear este nuevo modelo el primer paso es definir las horas de comienzo y finalización del turno noche. En una hoja de Excel ponemos esta sencilla tabla
Creamos una consulta a la tabla como "sólo conexión"
Podemos ver PASOS APLICADOS que Power Query ha cambiado el tipo de dato de las columnas Comienza y Termina a número decimal (identificado por 1.2 a la izquierda del encabezado de la columna). Ésto sirve a nuestros propósitos así que lo aceptamos.
A partir de esta consulta creamos una nueva con la opción Referencia
Abrimos la nueva conulta y cambiamos el nombre a "noche_comienzo".Con un clic del botón derecho del mouse y apuntando al valor en la fila Comienza y activamos la opción "Rastrear desagrupando datos" (en inglés sencillamente, drill down)
con este resultado
Ahora guardamos esta consulta también como "sólo conexión". Repetimos el proceso para crear una consulta para el valor de "noche_final", esta vez seleccionando el valor de la columna Termina.
Nuestra hoja se ve ahora así
Ahora vamos a aplicar los pasos que aplicamos en la segunda nota para crear una consulta que integre todos los informes (archivos xlsx) guardados en la carpeta Informes. Al final de proceso tenemos esta consulta en la ventana del editor de PQ
Aquí vamos a hacer un pequeño cambio a lo que mostramos en la segunda nota. Vamos a extraer de Entrada y Salida le fecha y dejar sólo las horas. Para hacerlo seleccionamos ambas columnas y aplicamos en Transformar-Columna de Fecha y Hora-Solo hora
ahora tendremos
La próxima transformación que hacemos es transformar las horas de Entrada y Salida al tipo de datos número decimal.
Ahora vamos a aplicar la misma lógica que usamos en la solución con Excel tradicional
# - calculamos las horas trabajadas con una columna personalizada con esta fórmula
=if [Salida]<[Entrada] then
1+[Salida]-[Entrada]
else
[Salida]-[Entrada]
# agregamos dos columnas auxiliares "noche-comienzo"
y "noche-final" de la misma manera . Cambiamos el tipo de datos a número decimal.
# creamos una columna personalizada que llamaremos "Mediana - Entrada". Ésta es una columna auxiliar (con el mismo objetivo de la correspondiente columna en el modelo en Excel ya mencionado). Seleccionamos la columna Entrada y las dos columnas que acabamos de crear y creamos una nueva columna con Agregar Columnas-Estadísticas-Mediana
La columna creada recibe, por defecto, el nombre "Median" que vamos a reemplazar con "Mediana-Entrada". Ésto podemos hacerlo agregando un paso o, como muestro en la imagen abajo, reemplazando el valor en la ventana de las fórmulas
Hacemos lo mismo con Salida-noche_comienzo y noche_final para crear la columna "Mediana-Salida".
# creamos una nueva columna auxiliar, "aux1" con esta fórmula condicional
# ahora calculamos las horas trabajadas en el turno noche con esta fórmula:
# todo lo que nos queda por hacer es calcular las horas diurnas que, lógicamente, son la diferencia entre las horas trabajadas y las horas nocturnas
# Finalmente eliminamos todas las columnas auxiliares que creamos y dejamos solamente aquellas que queremos que aparezcan en nuestro informe
Transformamos las columnas de horas al tipo de dato "Hora" y cargamos la consulta a una hoja del cuaderno
Para crear un informe que resuma los datos por empleados empezamos por crear una nueva consulta que cuyo origen es una referencia a la que acabamos de crear. Cambiamos el tipo de datos de las columnas de horas a número decimal y aplicamos "Agrupar por" de esta manera
Cargamos la consulta a una hoja del cuaderno y cambiamos el formato de las columnas de las horas al tipo personalizado [hh]:mm en el menú de formato de celdas de Excel (ver la explicación en la nota anterior).
Nuestro resumen se verá así
En la nota anterior mostramos como calcular las horas trabajadas por un empleado a partir de un informe en una hoja de Excel usando Power Query. En un caso sencillo como el del ejemplo la ventaja de usar Power Query en lugar de Excel tradicional (fórmulas) pasará desapercibida para la mayoría de los usuarios que aún no hayan adoptado las nuevas herramientas de Excel (Power Query, PowerPivot).
En este post mostraré un escenario más complejo (más real) donde nuestra misión es consolidar informes mensuales de varios empleados en un único informe. Y ésto debe suceder automáticamente y con sólo apretar un botón.
En este ejemplo cada empleado envía un informe semanal de las horas trabajadas. En este caso tenemos tres empleados pero, por supuesto, podrían ser cinco, veinte o cien.
La misión es:
calcular las horas trabajadas de cada empleado;
descontar media hora de descanso/comida cada día excepto que el empleado haya trabajado menos de siete horas;
crear un informe que totalice las horas netas trabajadas por cada empleado en la semana.
Empezamos por crear una carpeta para guardar los informes semanales de los empleados que recibimos en hojas de Excel, como ésta (al final del post hay un enlace al video que muestra todo el proceso)
Abrimos un cuaderno Excel y creamos una consulta que consolide todos los archivos (informes) guardados en la carpeta de los informes con Obtener Datos-Desde un archivo-Desde una Carpeta
Vamos a resistir la tentación de apretar el botón Combinar y en su lugar apretamos Editar
Eliminamos todas las columnas con excepción de Content y Name. Name contiene el nombre del archivo que es a su vez el nombre del empleado pero con la terminación .xlsx. Para quitarla y que nos nos quede sólo el nombre del empleado usamos Reemplazar
con este resultado
Ahora expandimos la columna Content (apretando la doble flecha a la derecha del encabezado) para combinar los archivos. En la ventana del editor veremos que la hoja contiene dos objetos
Elegimos "Tabla1" que es el rango que contiene la tabla de fechas y horas (más explicaciones en el video al final de este post donde muestro todo el proceso). Después de apretar Aceptar veremos esta tabla de datos combinados
¡La columna con los nombres de los empleados ha desaparecido! Para recuperarla hacemos un clic en el "engranaje" a la derecha del paso aplicado "Otras columnas quitadas1" y marcamos "Name"
y luego volvemos a seleccionar el último paso con un clic
Ahora nuestros datos están completos y podemos empezar a transformarlos. Empezamos por cambiar el nombre de la columna Name a Empleado. Como en el ejemplo del post anterior cambiamos el tipo de datos de Entrada y Salida a número decimal. Ésto nos permite realizar operaciones aritméticas sencillas con los datos evitando usar funciones DateTime y Time de Power Query con las que no estamos familiarizados.
y luego creamos la columna personalizada Horas Trabajadas
La columna personalizada aparece con el formato "Cualquiera" por lo que tenemos que transformarlo a número decimal. La forma más fácil es con el menú Transformar-Tipo de Datos-número decimal.
Ahora tenemos que descontar media hora de cada fila donde el trabajador trabajo siete horas o más. Nuevamente agregamos una columna calculada con una fórmula condicional
Ahora eliminamos las columnas Entrada y Salida y cambiamos el tipo de datos de Horas Trabajadas y Horas Trabajadas Neto a Hora
Cargamos esta consulta a una hoja de Excel y tendremos un reporte detallado. Seguidamente vamos a crear un reporte agrupado por empleado.
Empezamos por crear una consulta cuyo origen es la consulta anterior usando Referencia en la ventana de las consultas
En la nueva consulta transformamos las columnas de la horas al tipo de datos número decimal y luego agrupamos por Empleado creando dos columnas: Horas Trabajadas - bruto y Horas Trabajadas - neto
con este resultado
Para que nuestro informe sea legible queremos que los datos aparezcan como horas, no como números decimales (en el video de la nota agrego una explicación sobre el tipo de datos Duración de Power Query).
Para expresar los datos como horas acumuladas usamos el formato personalizado [hh:mm] en la tabla de la hoja
con el resultado esperado
Este video muestra todo el proceso
En el próximo post mostraré como hacer el cálculo tomando en dos turnos (diurno y nocturno).
