JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Duración de procesos con turnos

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 elementos del modelo son:

una tabla donde definimos los días laborales y el comienzo y final de la jornada;
tblSemana: consulta conectada a la tabla donde definimos los días laborales;
Fecha_comienzo: consulta que contiene solamente la fecha y hora de comienzo del proyecto/tarea;
Duracion_estimada: consulta que contiene la variable de horas estimadas para finalizar el proyecto/tarea;
periodos: consulta donde realizamos un cálculo intermedio (será explicado más adelante)
Schedule: consulta donde realizamos el cálculo detallando las horas aplicadas a cada día laboral hasta completar el proyecto/tarea.

Las primeras cuatro consultas las definimos como solo conexión. La consulta Schedule la cargamos en una hoja

Finalmente, extraemos el resultado de la tabla Schedule con la función MAX()

En este modelo podemos cambiar los valores del comienzo del proyecto, la duración estimada, modificar la tabla de días laborales (agregar o quitar días de la semana, cambiar las horas de comienzo y final) y apretando el botón Actualizar todo en Datos obtenemos el resultado instantáneamente

A partir de aquí comienzo a explicar el modelo en detalle. La explicación es larga, así que les sugiero descargar el archivo para seguir los pasos o estudiarlo por vuestra cuenta.

Actualización: versión mejorada del modelo

Vamos a explicar como funciona el modelo.

tblSemana

Creamos una conexión a la tabla de días laborales y agregamos una columna personalizada para calcular la duración de cada jornada. Nótese que usamos formato numérico decimal para las horas

El código de la consulta es:

 let  
   Origen = Excel.CurrentWorkbook(){[Name="tblSemana"]}[Content],  
   #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"ID", Int64.Type},   
     {"Dia", type text},   
     {"Comienza", type number},   
     {"Termina", type number}}),  
   #"Resta insertada" = Table.AddColumn(#"Tipo cambiado", "jornada",   
               each [Termina] - [Comienza], type number)  
 in  
   #"Resta insertada"

Fecha_comienzo

Para crear esta consulta empezamos por crear el Fecha_comienzo nombre definido que se refiere a la celda C4

Esto nos permite crear una consulta que es una especie de variable utilizando la técnica que mostré en el post sobre cálculo de lapsos con Power Query. Dado que la celda está ligada a un nombre definido Power Query crea la consulta como si tratara de una tabla/rango (de hecho, una celda es un rango). Dentro de la consulta hacemos "drill down" y la guardamos como Solo conexión.

Duracion_estimada

Hacemos lo mismo que en el paso anterior para crear la "variable" que contiene la duración estimado del proyecto o tarea.

periodos

Esta consulta es el "corazón" del modelo. Este valor (nótese que lo cargamos como "variable") lo calculamos en varios pasos:

creamos una conexión a la tabla de los días laborales;
multiplicamos la columna [jornada] por 24 para expresar la duración de cada jornada en formato numérico (15 en lugar de 0.625);
agregamos una columna personalizada con el valor 1 (o cualquier otro valor, pero el mismo para todas las filas); esto nos permite calcular el total de horas semanales agrupando la tabla;
creamos la columna [periodos] que consiste en la columna [total_semana] que creamos en el paso anterior multiplicada por 336 (7 x 48) donde 7 son los días de una semana y 48 el total de medias horas por día. Usamos 48 y no 24 (las horas de un día) para tener una precisión de media hora, Alternativamente podríamos utilizar 24 para una precisión de una hora o 96 para una precisión de 15 minutos. Finalmente quitamos todas las columna excepto [periodos], realizamos drill down y la guardamos como solo conexión.

Finalmente creamos la consulta Schedule que cargaremos a una hoja y de allí extraeremos la fecha y hora de finalización del proyecto usando la función MAX(), como señalamos más arriba.

Empezamos esta consulta abriendo una consulta en blanco y en la barra de las fórmulas del editor de Power Query ponemos esta fórmula:

= List.DateTimes(Fecha_comienzo,periodos,#duration(0,0,30,0))

que genera una lista (columna) de fechas y hora comenzando por la fecha de comienzo del proyecto con un salta de media hora (definido por #duration(0,0,30,0) )

Convertimos la lista en columna (usando la interfaz de usuario del editor de Power Query). Esta columna que acabamos de crear incluye días no laborales y horas fuera de la jornada laboral. Para eliminar las filas no válidas dividimos la columna en dos (fecha y horas), insertamos el número de día de la semana y combinamos con la consulta tblSemana usando los campos [Dia de la semana] y [ID} e expandimos los campos [Dia], [Comienza] y [Termina]

Nuestra consulta se ve así:

Al combinar las consultas los días no incluidos en tblSemana aparecen con el valor null en el campo [Dia]. Esto nos permite eliminar las filas con fechas inválidas filtrando aquellas donde aparece null.

El próximo paso es eliminar las filas donde la hora está fuera del rango de la jornada laboral. Para ello cambiamos el tipo de dato de [Comienza] y [Termina] al tipo time y luego creamos una columna personalizada con la fórmula

=[Hora]>=[Comienza] and [Hora]<=[Termina]

Creamos así una columna con dos valores : True (si la hora cae en el rango de la jornada laboral) o False (si la hora está fuera de la jornada laboral). Filtramos dejando solamente las filas True con lo cual eliminamos las otras filas no válidas.

Quitamos todas las columnas excepto [Fecha], [Hora] y [Dia]

Ahora tenemos que calcular el tiempo acumulado a cada paso para determinar donde alcanzamos la duración establecida. Sabemos que cada fila representa media hora así que lo que hacemos es agregar una columna [Indice] y luego otra columna personalizada, [acumuladas], que calculamos multiplicando [Indice] por 0.5 (media hora en formato decimal).

Ahora agregamos una columna personalizada [rango_horas] con esta fórmula:

=[acumuladas]<=Duracion_estimada

que dará True si el valor de [acumuladas] es menor de la duración estimada y False en caso contrario.
Todo los que nos queda por hacer es filtrar [rango_horas] dejando solamente las filas con valor True.
Quitamos la columna [rango_horas] y descargamos la consulta a una tabla en una hija del cuaderno.