Cálculo de horas trabajadas con Power Query

Cálculo de horas trabajadas es uno de los temas sobre los cuales recibo la mayor cantidad de consultas. Así que ha llegado el momento de ver cómo podemos realizar estos cálculos con Power Query.
Si ya hemos mostrada en varios posts como podemos calcular horas trabajadas, con y sin turnos, ¿por qué mostrar como hacerlo con Power Query? Por la misma razón por lo cual recomiendo a todo quien este dispuesto a escuchar:

• porque las soluciones con Power Query suelen ser más robustas;
• porque una vez que desarrollamos el modelo en Power Query no hace falta reescribir las fórmula y realizar los cálculos cada vez que que recibimos datos nuevos;
• porque podemos combinar datos de varios cuadernos o varias hojas automáticamente y con facilidad;
• porque Power Query es una de las herramientas centrales del "nuevo Excel" (Power Query, Power Pivot, Power BI).

Lista parcial :)

Y ahora, ¿cómo hacemos un cálculo de horas trabajadas con Power Query? En esta nota voy a mostrar un caso sencillo. Supongamos que recibimos de un trabajador (más adelante veremos cómo lo integramos con varios) una planilla semanal de horas trabajadas com ésta:

Tal como ya mostramos en posts anteriores, una cuestión a resolver es que a veces el turno termina al día siguiente del principio (en nuestro ejemplo el miércoles y el jueves). Ésto es crítico ya que no contamos con la fechas. La solución con fórmulas Excel es usar  RESIDUO (hay otras, pero ésta es la que recomiendo). Con Power Query vamos a hacerlo de otra manera.

Como toda solución en Power Query empezamos por crear una consulta a partir de la tabla de horas (para este ejemplo usaremos Excel 2016/365, pero funciona de la misma manera para Excel 2010/13) y visualizarla en el editor de consultas

Podemos ver que Power Query interpreta las horas como números decimales. Esto no debe sorprendernos ya que las fechas y horas son también en Excel números (enteros para las fechas y la parte decimal para las horas, minutos y segundos).
Por ahora no vamos a cambiar el tipo de datos. Para poder calcular las horas trabajadas creamos la columna "Horas Trabajadas" con esta fórmula

donde agregamos 1 (sumar un día) al valor de Salida cuando este es menor que el correspondiente de Entrada.

Ahora podemos eliminar las columnas Entrada y Salida, pero antes de cargar la consulta una hoja tenemos que cambiar el tipo de dato de la columna personalizada de "Cualquiera" a "Hora"

Agregamos la línea de Total con la función Suma a la tabla

y aplicamos el formato [hh]:mm

A partir de este momento cada vez que recibimos un reporte del trabajador todo lo que tenemos que hacer es reemplazar el reporte anterior en la carpeta y actualizar la consulta.

En el próximo post veremos como crear un reporte que combina los datos de varios trabajadores.

Cálculo de lapsos con Power Query

Comencé a publicar posts sobre Power Query hace casi cuatro años (este fue mi primer post sobre el tema). Desde entonces no sólo he publicado artículos sobre esta herramienta (indispensable para todo quien trabaje con masas de datos) sino que también se ha convertido en mi herramienta principal. Puedo decir que hoy en día la mayoría de las soluciones que desarrollo en Excel se basan, total o parcialmente, en el Power Query.

Hoy voy a mostrar como calcular lapsos, el tiempo transcurrido entre dos instantes, usando el Power Query. Supongamos que tenemos esta serie de mediciones y queremos saber cuánto tiempo ha transcurrido entre cada medición

Podemos hacerlo usando fórmulas de Excel, por supuesto. Pero supongamos que tenemos una gran cantidad de datos (en el caso real que traté había más de 50 mil mediciones) y que tenemos que realizar otras transformaciones por lo que Power Query será la herramienta ideal para el caso.

Como casi todas las notas de este blog ésta está dirigidas al usuario medio o medio-avanzado. Es decir, voy a prescindir de tecnicismos y pondré el énfasis en los aspectos prácticos.

Observando detenidamente el ejemplo podemos ver que hay dos tablas: una contiene una sola fila con la fecha y hora del comienzo de las mediciones y la segunda con las mediciones. Antes de crear las consultas consideremos lo siguiente: el cálculo de cada lapso es el tiempo transcurrido entre una medición y la inmediata anterior; pero el primer lapso es entre la primera medición de la tabla y el punto inicial. Éste dato se encuentra en otra tabla. Para poder usar este dato vamos a hacer lo siguiente:

Cargamos la tabla del punto inicial a una consulta

Con un clic del mouse abrimos la opcíón "Rastrear..." (drill down)

con este resultado

En la ventanilla Propiedades de la consulta cambiamos el nombre a "Inicio" y cerramos la consulta con la opción "sólo crear conexión". Luego cargamos la tabla de mediciones, sobre la cual haremos todos los cálculos

Nótese los diferentes iconos en las consultas, lo que nos indica que Power Query las está tratando de forma distinta. De hecho, la segunda es una tabla (como lo indica el icono) mientras que “Inicio” es una especia de variable (podemos cambiar el valor en la hoja de Excel y el cambio se reflejará en la consulta).

Ahora vamos a editar la consulta de las mediciones donde haremos nuestras cálculos. Empezamos por agregar una columna Índice con base cero

Cambiamos el nombre del último paso a "AgregInd" (nemotécnica para Indice Agregado)

Ésto nos permite trabajar con más facilidad en los próximos pasos.

Ahora agregamos una columna personalizada con esta fórmula

con este resultado

Debemos prestar atención a estos detalles en nuestra fórmula

if [Índice]=0 then[Medicion]-Inicio else [Medicion]-AgregInd[Medicion]{[Índice]-1}

• usamos directamente Inicio para referirnos a la consulta que contiene el punto de partida, como si estuviéramos usando una variable (o constante a los efectos de la consulta);
• para referirnos a la fila anterior anteponemos el nombre del paso anterior (AgregInd) al nombre de la columna sobre la cual queremos operara (Medicion, en nuestro caso) y usamos la expresión Indice - 1 entre corchetes para indicar que queremos referirnos a la fila anterior (no voy a entrar aquí en explicaciones sobre los objetos de Power Query, tablas y listas).

Dos detalles par finalizar:

• eliminamos la columna Indice
• cambiamos el tipo de dato de la columna Lapso usando la opción Transformar - Detectar tippo de datos

Todo lo que nos queda por hacer es cargar la consulta a una tabla, si así lo queremos, o dejarla como "sólo conexión".

El archivo con el ejemplo se puede descargar aquí

Cálculo de fecha de finalización de curso

Supongamos que programamos un curso de programación dividido en 10 encuentros/clases. Las clases se dictarán todos los lunes, miércoles y viernes. La pregunta es: ¿cuál es la fecha del último encuentro?

Podemos resolver este problema con facilidad usando la función DIA.LAB.INTL (Excel 2010 y versiones posteriores) pero tomando en cuenta un parámetro oscuramente explicado en la ayuda en línea de la función.

Empezamos por montar nuestro modelo

En las columnas D a H marcamos con el 0 (cero) los días en que se dictarán las clases y con 1 los días de la semana en los que no se dictarán las clases del curso. Ésto parece ir contra la intuición ya que, siendo usuarios consuetudinarios de Excel, asociamos el 0 con el valor FALSO y el 1 con el valor VERDADERO. Pero, como veremos más adelante, la función DIA.LAB.INTL requiere que usemos estos valores.

Como podemos ver, el curso de Programación comprende 10 encuentros (columna C) y se dicta los días Martes y Jueves.

Ahora agregaremos la columna con el cálculo de la fecha de cierre del curso

La fórmula en la celda J3 es =DIA.LAB.INTL(I3,C3-1,CONCAT(D3:H3)&"11")

Antes de seguir adelante aclaremos que la función CONCAT está disponible solamente para los usuarios de Excel 2016 (en Office 365). Usuarios de versiones aneriores deberán usar la función CONCATENAR.

Expliquemos la fórmula

El primer argumento es la fecha inicial; el segundo es el número de días que en nuestro caso es el número de encuentros. El tercer argumento es un valor textual compuesto de 1 y 0 que indican cuales son los días laborales de la semana y cuales no. Este valor textual debe tener siempre siete símbolos (uno por cada día siguiendo el orden de los días de la semana). 

El "truco" en esta fórmula es definir sólo los días en que se dicta el curso como días laborales. 

Opcionalmente podemos agregar una lista de feriados, el cuarto argumento, en caso de ser necesario.

También podemos cambiar el formato de las fechas para que muestren el día de la semana 

Otra mejora posible es usae Formato Numérico Personalizado para mostrar "si" en lugar de 0 y "no" en lugar de 1

con esta definición

o mejor aún con Formato Condicional

definido de esta manera

Cálculo de tiempo acumulado con Power Query

La principal dificultad del usuario Excel experimentado al comenzar a trabajar con Power Query no es tanto acostumbrarse al nuevo entorno, comprender los nuevos objetos y el nuevo idioma (M) o dominar las nuevas funciones. El principal obstáculo a vencer es dejar de “pensar Excel”.

Supongamos (en mi caso más que suposición era parte de un proyecto) que tenemos una serie de mediciones en una tabla; en una columna tenemos el momento de la medición (fecha y hora) y en otra tabla el valor medido.

La tarea a realizar es calcular el tiempo acumulado transcurrido desde la primera medición hasta la última, para cada medición. Con Excel la tarea es sencilla: en la primer celda de la columna C de la tabla ponemos es fórmula =A2-$A$2 y la copiamos a lo largo de la columna

Sólo tenemos que asegurarnos de “anclar” el sustraendo (=A2-$A$2). 

Si es tan fácil hacerlo con fórmulas de Excel, ¿por qué hacerlo con Power Query? Hay muchos motivos (supongamos por ejemplo una tabla con dos millones de filas), pero en mi caso se trataba de un proyecto totalmente desarrollado con Power Query.

En Power Query no existe el concepto de celda tal como lo conocemos en Excel, por lo que no podemos replicar el cálculo que he mostrado más arriba. A continuación mostraré los pasos.

Empezamos por cargar la tabla de datos a la ventana de Power Query 

Luego selecccionamos el primer valor del campo Registro en la ventana del Power Query y aplicamos Drill Down

con lo que obtenemos esta situación

Como pueden apreciar, el menú en la cinta a cambiado a "Text Tools"; en la ventanilla de la propiedades cambiamos el nombre (Name) a "varComienzo" (la partículo "var" la agrego como método de identificar que se trata de una variable).

Terminamos el proceso cargando la consulta como "sólo conexión" (Home-Close and Load-Connection Only). Nuestra hoja se ve ahora así

Ahora vamos a cargar la tabla y vamos a agregar una columna que llamaremos "Comienzo"; esta columna contendrá el valor de la variable (fecha y hora de la primera medición). Luego creamos la columna "Tiempo Acumulado" restando la columna Registro de la columna Comienzo. Finalmente eliminamos la columna Comienzo, que ya no necesitamos, y cargamos la tabla a una hoja de Excel. Todo el proceso lo muestro en este video.

Un detalle a tomar en cuenta es el tipo de variable que usamos para el tiempo acumulado: "Duration".

Suma condicional de fechas en Excel

Este post viene a colación de una consulta sobre cómo totalizar valores por fechas con más de una condición, más específicamente, entre dos fechas.
Excel pone a nuestra disposición varias herramientas: fórmulas, tablas y tablas dinámicas. Hacer el cálculo con tablas o tablas dinámicas no ofrece mayores inconvenientes. Por ejemplo, si hemos organizado los datos en forma de Tabla, podemos usar el filtro y la opción de Fila de Totales para ver el total de los valores del primer semestre del año

En una tabla dinámica creada con los mismos datos aplicamos también el filtro de fechas

Si queremos o tenemos que usar fórmulas, nos toparemos con dos "bemoles":

1. la forma en que Excel maneja la fechas dentro de las fórmulas (tema que ya tratamos en el pasado remoto de este blog);
2. el uso de operadores del tipo "mayor que", "igual", etc. dentro de fórmulas.

Si queremos hacer con funciones el cálculo mostrado más arriba podemos usar SUMAPRODUCTO o SUMAR.SI.CONJUNTO, ya que tenemos dos condiciones.

Con SUMAPRODUCTO usamos 

=SUMAPRODUCTO((B3:B62>=FECHA(2016,1,1))*(B3:B62<=FECHA(2016,3,31))*C3:C62)

La función FECHA convierte la fecha del criterio, por ejemplo 01/01/2016 en su número de serie (42370) ya que esa es la forma en que Excel maneja las fechas.

Si queremos usar SUMAR.SI.CONJUNTO usamos

=SUMAR.SI.CONJUNTO(C3:C62,B3:B62,">="&FECHA(2016,1,1),B3:B62,"<="&FECHA(2016,3,31))

donde podemos ver que concatenamos el operador ">=" con el numeral de la fecha usando el operador &.

Formato condicional y precisión de cálculo de Excel

En el pasado he tocado el tema de la precisión de cálculo en Excel y los problemas que puede generar. Para evitar una ola de pánico entre mis lectores señalemos que en la práctica estos problemas aparecen muy raramente, pero que existen…existen.

El caso que voy a presentar está basado en una consulta de una amiga, investigadora en el área de la biología. Supongamos esta tabla de mediciones de lapsos

Como puede observarse, en la columna “Lapso transcurrido” se ha calculado cuántos minutos transcurrieron entre una observación y la anterior. Queremos controlar que no hayan transcurrido más de 15 minutos entre cada observación, por lo que aplicamos formato condicional con la siguiente fórmula

Al hacerlo surge un problema: a pesar que todos los intervalos son de 15 minutos, como podemos controlar a ojo desnudo, Excel marca ciertas filas como si el intervalo hubiera excedido el tiempo permitido

El problema reside en la precisión de cálculo de Excel (problema del floating point que no desarrollaré aquí, pero pueden consultar este artículo de la base de conocimientos de Microsoft). Para hacer visible el problema voy a copiar los valores de la columna C a la columna D aplicando el formato numérico General

Aparentemente todos los valores son idénticos (15/1440 = 0.10416667), pero si agregamos más decimales a la presentación en pantalla de los números vemos lo siguiente

¡Los números, que expresan el intervalo en formato decimal, son distintos a pesar que los intervalos son idénticos! Como Excel utiliza la forma decimal para hacer los cálculosde tiempo y fechas, nuestro Formato Condicional falla ( los marcados con la X, son un poco más grandes y por lo tanto son señalados por el formato condicional).

Una solución posible es reducir la precisión de cálculo a 10 lugares decimales usando REDONDEAR

Otra solución, más precisa, es usar la función MINUTO para expresar el intervalo como número entero

y en el formato condicional usar =$C4>15

Las funciones TRUNCAR y ENTERO de Excel

Excel tiene dos funciones para extraer la parte entera de un número: TRUNCAR() y ENTERO(). Si bien en principio puede parecer una redundancia, no lo es.

ENTERO() toma como argumento un número y lo redondea al entero inferior más próximo.

TRUNCAR() suprime la parte fraccionaria de un número. A diferencia de la función ENTERO, TRUNCAR tiene un argumento opcional: número de decimales.

Dado que ENTERO redondea hacia abajo y TRUNCAR suprimer la parte decimal, el resultado será el mismo si el argumento es un número positivo, pero distinto si el número es negativo

Como TRUNCAR tiene el argumento opcional "num_decimales" la fórmula =TRUNCAR(64.5894,1) da como resultado 64.5, a diferencia de REDONDEAR(64.5894,1) que dá 64.6.

Otro uso de TRUNCAR y ENTERO es obtener la fecha sin las horas

Las fechas en Excel son números positivos donde la parte entera es la fecha y la parte decimal la hora (como he explicado en esta nota). Por lo tanto ENTERO y TRUNCAR quitan la parte "horaria" (decimal) y dejan la parte "entera" (la fecha).

Crear series de días hábiles en Excel

Podemos crear una serie de días hábiles en Excel usando una funcionalidad poco conocida: Rellenar  - Series. Podemos activar este funcionalidad de dos maneras: en la cinta de opciones con Rellenar-Series o con el menú contextual de opciones de autorrelleno.

Supongamos que queremos crear una lista de los días hábiles de agosto del 2015. En la celda A1 ponemos la fecha del primer día hábil del mes de agosto de este año; luego en la cinta de opciones activamos Inicio-Modificar-Rellenar-Series y completamos el formulario de esta manera

Apretamos Aceptar y obtenemos la serie de días hábiles

La fórmula en la columna B es
=ELEGIR(DIASEM(A1;2);"lunes";"martes";"miércoles";"jueves";"viernes";"sábado";"domingo")

Podemos hacer lo mismo con el menú contextual  arrastrando con el mouse

La serie de días hábiles resultante depende de las definiciones regionales del sistema.

Excel reconoce si el valor de la celda es una fecha y ofrece crear series de días corridos, hábiles, meses y años.

Calcular horas trabajadas en distintos turnos

Esta nota es la continuación del post anterior sobre el cálculo de horas nocturnas con Excel. Supongamos ahora la existencia de tres turnos de trabajo: día, tarde y noche. Nuestra tarea es calcular dentro de las horas trabajadas por cada operario en una jornada, cuántas pertenecen a cada turno.
En el post anterior vimos cómo calcular las horas nocturnas, que de hecho es calcular cuantas de las horas trabajadas caen dentro de un intervalo determinado.

A los efectos de nuestro ejemplo supongamos que el turno tarde comienza a las 18:00 y concluye a las 22:00 y el turno corre de las 22:00 hasta las 06:00. Las restantes horas del día, de 06:00 a 18:00, corresponden al turno día.

Para calcular cuántas horas caen dentro de cada turno usamos este modelo:




Esta tabla es interactiva y el modelo puede decargarse usando el icono de descarga

Las fórmulas en el modelo son:

Total trabajado: =RESIDUO(C7-B7,1), que ya hemos explicado en el post anterior;

Día: =(B7>C7)*MEDIANA(0,C7-$D$4,RESIDUO($D$4-$C$3,1))+MAX(0,MIN($C$3,C7+(B7>C7))-MAX($D$4,B7))

Noche: =RESIDUO(C7-B7,1)-(C7<B7)*($C$4-$D$4)-MEDIANA(C7,$D$4,$C$4)+MEDIANA(B7,$D$4,$C$4) explicada en el post anterior

Tarde: =D7-E7-G7 - sencillamente resstamos los turnos Día y Noche del Total Trabajado.

Cálculo de horas trabajadas en turno noche

Un tema recurrente en las consultas que recibo es cómo calcular horas trabajadas en turno noche. Supongamos la siguiente situación:

• un operario trabaja desde las 20:00 hasta las 04:00
• el turno noche, cuya tarifa es distinta del horario normal, corre de las 22:00 a las 06:00

¿Cómo calculamos con Excel cuántas horas de las 8 horas trabajadas pertenecen al turno noche? Veamos esta tabla con varios ejemplos

La fórmula en la celda E7, que calcula correctamente las horas trabajadas en turno noche tal como ésta definido en las celdas D3 y E3, es:

=RESIDUO(C7-B7,1)+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3)-(C7<B7)*($D$3-$E$3)

Esta fórmula se basa en la función MEDIANA y en la función RESIDUO
.
Para explicar esta fórmula vamos a dividirla en tres módulos:

RESIDUO(C7-B7,1)  - esta fórmula, que ya hemos explicado, calcula el total de horas trabajadas.

+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3) - Esta expresión nos permite calcular el total de horas trabajadas fuera del turno noche. Este resultado será siempre negativo o cero, de manera que al restarlo de las horas trabajadas (el primer "módulo" de nuestra fórmula), obtenemos las horas que caen dentro del horario nocturno.

(C7<B7)*($D$3-$E$3) - el objetivo de esta expresión es corregir el resultado cuando el horario trabajado pase de un día al otro. Si la hora de salida es mayor que la hora de entrada, todo el trabajo ha sido realizado dentro del mism día; en este caso la expresión dá cero (FALSO) y no varía el resultado. Si la hora de salida es menor que la de entrada, la jornada terminó al día siguiente; la expresión da VERDADERO (que equivale a multiplicar por 1), y el resultado es corregido de acuerdo.

Este cuadro interactivo permite ver como funciona cada módulo de la fórmula (los valores de entrada y salida y los del horario nocturno pueden ser modificados)





En este post pueden ver una solución para el caso de tres turnos (día, tarde y noche).