Función DÍA LABORAL

Cuando necesite calcular una fecha futura y excluir los fines de semana, puede considerar explorar la función DÍA LABORAL. En esta publicación, usaremos la función WORKDAY para preparar un plan de proyecto simple y luego mostrarlo con un diagrama de Gantt.

Objetivo

Tenemos un proyecto que estamos gestionando y tiene varias tareas. Sabemos el día de inicio y la cantidad de días laborables que tarda cada tarea en completarse. Queremos que Excel calcule la fecha de finalización de cada tarea. No podemos simplemente agregar la fecha de inicio y la cantidad de días porque eso incluiría los fines de semana. Necesitamos excluir los fines de semana de la ecuación.

Una vez que Excel calcule la fecha de finalización, nos gustaría encontrar una forma sencilla de mostrar visualmente la línea de tiempo del proyecto, en un diagrama de Gantt. El resultado final debería verse así.

Estos son los pasos que seguiremos para completar nuestro libro de trabajo:

• Calcular la fecha de finalización con la función WORKDAY
• Configurar el diagrama de Gantt
• Fórmula de formato condicional
• Productos cosméticos

Hagámoslo.

DÍA LABORAL

En primer lugar, calcular la fecha de finalización con la función DÍA LABORAL. La tarea A comienza el 1/1/2016 y está programada para durar 5 días hábiles. Si simplemente sumáramos estos valores, obtendríamos el 6/1/2016, una fecha de finalización incorrecta. Esto se debe a que queremos excluir los fines de semana de nuestro cálculo. Aquí es donde la función WORKDAY puede ayudar. Calcula la fecha de finalización en función de la fecha de inicio y el número de días laborables, y excluye los fines de semana. Además, puede excluir una lista de días festivos si es necesario.

Dado que nuestra fecha de inicio está almacenada en C8 y el número de días laborables en D8, podemos escribir la siguiente fórmula en E8:

=DÍA LABORAL(C8,D8)

La función WORKDAY devuelve una fecha del 8/1/2016…perfecto. Completamos la fórmula para todas las tareas como se muestra a continuación.

Ahora pasemos al diagrama de Gantt.

Gráfico de gantt

El diagrama de Gantt se crea con formato condicional. Antes de pasar a la mecánica, consideremos lo que estamos tratando de lograr conceptualmente.

Nos gustaría llenar las celdas de color azul cuando el día esté entre la fecha de inicio y la fecha de finalización (inclusive). Por ejemplo, si miramos la columna F, el día es 1/1/16. Queremos llenar la celda azul para la Tarea A ya que el 1/1/16 está entre el día de inicio (1/1/16) y la fecha de finalización (8/1/16). No queremos llenar las celdas restantes en la columna F para las otras tareas, ya que el 1/1/16 no se encuentra entre sus fechas de inicio y finalización. ¿Hasta ahora, todo bien? Esta misma lógica se transmite a todas las columnas. Cuando el día cae entre las fechas de inicio y finalización, queremos sombrear la celda de azul.

La buena noticia es que podemos aplicar esta lógica a Excel con la función de formato condicional.

Formato condicional

Para comenzar, seleccionamos todas las celdas del rango del diagrama de Gantt, es decir, F8:AJ11. Luego seleccionamos Formato condicional Nueva regla. En el cuadro de diálogo Nueva regla de formato, optamos por “Usar una fórmula para determinar qué celdas formatear” y, asumiendo que la celda F8 es la celda activa dentro del rango seleccionado, ingresamos la siguiente fórmula.

=F$7=MEDIANA($C8,$E8,F$7)

Un par de notas sobre esta fórmula. En primer lugar, la función MEDIANA está diseñada para devolver el valor medio de sus argumentos. Usamos los valores de fecha de inicio, fecha de finalización y día como argumentos y devolverá la fecha intermedia. Comparamos esa fecha intermedia con el día en F7 y, si es igual, la fórmula devolverá VERDADERO y aplicará el formato seleccionado. Si F7 no es la fecha intermedia, entonces la fórmula devuelve FALSO y Excel no aplica el formato condicional.

A continuación, debemos prestar atención a las referencias de celda utilizadas. Necesitamos imaginar que la fórmula se completará en todo el rango seleccionado. Eso significa que debemos prestar atención a las referencias absolutas y relativas. Comenzando con la fecha almacenada en F7, a medida que se completa la fórmula de formato, necesitamos que la referencia permanezca bloqueada en la misma fila y, a medida que se completa correctamente, queremos que la referencia de la columna se actualice en consecuencia. Entonces, utilizamos el estilo de referencia de celda mixta F$7, que es fila absoluta (7) y columna relativa (F). Para las fechas de inicio y finalización almacenadas en C8 y E8, a medida que se completa la fórmula de formato, queremos que la referencia de la fila se actualice, pero a medida que se completa a la derecha, queremos que la referencia de la columna permanezca. Entonces, usamos el estilo de referencia de celda mixta $C8 y $E8 que proporciona una fila relativa y una columna absoluta.

Nota: Como alternativa al uso de la función MEDIANA, podríamos haber usado la siguiente fórmula de formato: =AND(F$7=$C8,F$7=$E8)

Con nuestra fórmula de formato condicional completa, simplemente usamos el botón Formato para seleccionar el tipo de formato a aplicar. Solo usé un simple relleno de celda azul. El cuadro de diálogo resultante se muestra a continuación como referencia.

Con el formato condicional aplicado, ¡nuestro diagrama de Gantt se ve bien!

Ahora, algunas actualizaciones cosméticas.

Productos cosméticos

En lugar de mostrar la fecha completa, solo queremos mostrar el número del día. Entonces, seleccionamos los valores del encabezado de fecha del diagrama de Gantt y aplicamos el siguiente formato de celda personalizado.

La d en el campo Tipo le dice a Excel que muestre solo el número del día. Luego cambiamos el ancho de las columnas del diagrama de Gantt a 2, opcionalmente formateamos los bordes de las celdas en gris y el rastreador de proyectos actualizado se muestra a continuación.

¡Muy bien, creo que logramos nuestro objetivo!

Si tiene otros usos para la función WORKDAY u otros enfoques para los diagramas de Gantt, compártalos publicando un comentario a continuación… ¡gracias!

Recursos

• jornada laboral
• Publicación de formato condicional de la Universidad de Excel