Cálculo de finalización de procesos con Excel

viernes, enero 15, 2010

Supongamos que tenemos una planilla en Excel donde queremos calcular la fecha de finalización de un proceso a partir de la fecha de comienzo y la duración en días. Excel permite hacer esto con facilidad sumando el número de días a la fecha de comienzo




Pero si queremos calcular la fecha de finalización del proceso tomando en cuenta los feriados (el 16/01/2010 es sábado y el 17/01/2010 domingo), tendremos que usar la función DIA.LAB




El resultado aparece en rojo para resaltar el hecho que es incorrecto. El resultado correcto es



20/01/2010 08:00


Esto se debe a que DIA.LAB calcula días enteros, sin tomar en cuenta las horas, minutos y segundos.

Para calcular el resultado correcto usamos esta fórmula



=DIA.LAB(B5;C5)+(B5-ENTERO(B5))



Como ya hemos explicado en el pasado, Excel usa una serie de números para los cálculos de fechas. La parte entera del número representa el día y la parte decimal la fracción del día (horas, minutos y segundos).
En nuestro caso 15/01/2010 08:00 está representado por el número 40193,3333333333 donde 40193 son los días transcurridos desde el 1ro. de enero de 1900 y 0.333333 (es decir, 1/3) es la tercera parte del día, 8 horas (24 x 1/3 = 8)

Lo que hacemos en nuestra fórmula es extraer la parte decimal y agregarla al resultado de DIA.LAB.

Supongamos que la duración del proceso no es un número entero de días sino, por ejemplo, 3 días, 10 horas y 20 minutos. Si ignoramos los feriados podemos esta fórmula

=B9+C9+NSHORA(D9;E9;F9)



Usamos NSHORA para convertir los parámetros de tiempo que ponemos como números enteros en el número serial correspondiente al lapso (10:20:00 es representado por el número 0,430555555555556).

Si nos gusta complicarnos la vida o le tenemos antipatía a la función NSHORA, podemos usar en su lugar la expresión

=B10+C10+(D10*3600+E10*60+F10)/86400



Convertimos las horas y los minutos a segundos y los dividimos por 86400 que es la cantidad de segundos que hay en un día (24 x 60 x 60 = 86400)

En caso que tengamos que tomar en cuenta los feriados, tendremos que usar DIA.LAB y corregir el resultado tal como hicimos en el ejemplo anterior




18 comments:

Anónimo,  15 enero, 2010 19:57  

Estimado Jorge, hace tiempo que no te escribo, debo decir que tu blogazo esta bastante diferente que hace un par años. te felicito por tu blog!!

Recurro a ti, puesto que no he podido dar con un comando para las macros. Sé que hay un comando que evita que la pantalla parpadee mientras corre un programa. Es importante para mi y te agradezco mucho tu ayuda.

Muchas gracias maestro.
Saludos desde Chile
Diego

Jorge L. Dunkelman 15 enero, 2010 21:58  

Hola Diego

Application.ScreenUpdating = False

para evita el parpadeo (refresh) y

Application.ScreenUpdating = True

para volver a la situaci[on normal

Anónimo,  21 enero, 2010 15:25  

Muy agradecido. Funcionó a la perfección.

Saludos,
Diego

Gerson Pineda H 21 enero, 2010 21:25  

Hola Jorge!
Sigo de cerca tu blog, y esta muy interesante tu explicacion sobre el fin de un proceso, con la funcion DIA.LAB

Sigue adelante con tu blog

Te envio saludos desde Honduras

Anónimo,  01 febrero, 2010 19:07  

Hola Buenos días
mi nombre es Maria y quisiera me ayudaran por favor, necesito calcular la cantidad de dias que hay por ejemplo entre el 04/01/2010 y el 20/01/2010 exluyendo los fines de semana.

necesito hacer una macro para calcular esto y he tratado de varias formas y no me sale ...por favor si me pueden ayudar estaré agradecida.

Jorge L. Dunkelman 01 febrero, 2010 21:36  

Hola María

¿para qué complicarse la vida con una macro cuando se puede hacer fácilmente con la función DIAS.LAB. En el blog podrás encontrar varias notas sobre el tema.

Alan,  02 marzo, 2013 00:56  

Hola Jorge, hace unos dias que intento implementar algo parecido a lo que explicas en este post y no logro hacerlo. Tengo una lista de tareas para una maquina expresadas en horas. Algunas duran un par de horas y otras llegan hasta 12 horas. Necesito poder calcular la fecha y hora de finalizacion de cada una de las tareas teniendo en cuenta los días laborables, la hora de ingreso, almuerzo y salida de los operarios. Dichos horarios son de 7hs a 12hs y de 13hs a 17hs.

Muchas Gracias por este blog!

Jorge L. Dunkelman 02 marzo, 2013 19:40  

Alan,
te sugiero que me mandes el archivo para darme una idea más clara del problema. No puedo comproterme a responderte (estoy cargado de trabajo) pero haré lo posible.

Ana Gomez 15 noviembre, 2014 20:36  

Hola! Ya comprendí un poco mejor como jugar con los días laborales,
Pero aún tengo un problema
Suponiendo que es 14-nov-14 8:00 am, tengo un proceso que dura 9 horas y mi turno dura 8 horas, cómo puedo hacer para que el día 15-nov-14 la hora de finalización sea 9:00am

Jorge Dunkelman 16 noviembre, 2014 15:39  

Ana,
supongamos que en la celda C2 tienes la duración del turno (en tu ejemplo, 08:00); en la celda B5 introduces el comienzo de la tarea (14/11/2014 08:00) y en la celda C5 la duración de la tarea (09:00); esta fórmula te dará el resultado esperado

=SI(C5>C2,B5+TRUNCAR(C5/C2)+RESIDUO(C5,C2),B5+C5)

La expresión controla si la tarea dura más que el turno; en caso positivo se aplica la expresión: TRUNCAR(C5/C2)+RESIDUO(C5,C2) donde

TRUNCAR(C5/C2) calcula cuantos días hay que sumar a la fecha inicial (días de 8 horas) y la expresión

RESIDUO(C5,C2)

calcula cuando horas por encima de las 8 horas del turno hay que agregar.


Ana Gomez 20 noviembre, 2014 21:07  

Hola! nuevamente ando desafiando los conocimientos de excel.
La fórmula que explicaste anteriormente no contempla cuando se cruzan los días laborales. Así que adapte la fórmula con otros de tus tutoriales, pero ni mi fórmula ni la tuya contempla lo que pasa cuando empiezas cerca de las 4pm.
Ejemplo:
Las horas las tengo C2 (en este caso 00:30)
La fecha de inicio la tengo en D2 (en este caso 14/11/14 03:40p.m)
La fecha de fin la tengo en E2 (teóricamente debería ser 15/11/14 08:10am)
Tu fórmula y la mía arrojan como resultado 14/11/14 4:10pm....

comparto la condicional que hice con la esperanza de que sepas hacer la compensación de ese caso particular =SI(RESIDUO(D2,1)+C2>0.67,DIA.LAB.INTL(D2,TRUNCAR(C2/(8/24)),11)+((D2-ENTERO(D2))+ RESIDUO(C2,(8/24))),C2+D2)

Saludos.

Jorge Dunkelman 21 noviembre, 2014 18:30  

Hola Ana,

suponiendo que en la celda C1 tenemos la hora de comienzo del turno (08:00); en la celda C2 la duración del turno (08:00 horas, de manera que el turno termina a las 16:00); en la celda B5 tenemos la fecha y hora de inciación del proceso (14/11/2014 15:40) y en la celda C5 la duración del proceso (00:30), esta fórmula te dará el resultado

=SI(((B5-ENTERO(B5))+C5)>(C1+C2),DIA.LAB(B5,TRUNCAR(C5/C2)+1)+C1+C5-((C1+C2)-(B5-ENTERO(B5))),B5+C5)

Ana Gomez 22 noviembre, 2014 00:41  

Excelente foro, ya me salió mi cálculo.
Gracias!!!

Jose Luis Camacho 05 febrero, 2016 01:19  

Aun podemos hacer consultas?

Jorge Dunkelman 05 febrero, 2016 07:13  

Hola José Luis, si pero por favor lee lo que pongo en el enlace Ayuda (en la parte superior del blog).

Jose Luis Camacho 05 febrero, 2016 18:47  

Tengo la siguiente información:
Inicio turno 04:00:00 p.m.
Duración turno 7:00:00
Fin del turno 11:00:00 p.m.

Con ls siguiente función =IF(E6>$C$2,WORKDAY.INTL(B6,TRUNC(E6/$C$2),1)+$C$1+MOD(E6,$C$2),B6+E6)

si mi proceso dura 7 horas me arroja la siguiente información:
Comienzo Duración Termino
vie 14 nov 14 16:00 7 vie 14 nov 14 23:00

Sin embargo, no deseo que considere fines de semana a pesar que existe la función, cuando pongo que mi proceso dura 8 horas me manda la siguientes información:
Comienzo Duración Termino
vie 14 nov 14 16:00 8 sáb 15 nov 14 00:00

Me ayudas por favor.

Saludos

Excelente blog.

José Luis

Jose Luis Camacho 05 febrero, 2016 19:02  

C1 inicio de turno = 4:00:00 pm
C2 duración de turno = 7 Hrs
C6 duración del proceso = 8 Hrs
B6 Fecha de inicio = 14-Nov-14 4:00:00 pm
D6 Fecha de termino = 15-nov-14 00:00 (resultado)

Jorge Dunkelman 06 febrero, 2016 16:03  

José Luis

por favor, fijate lo que pongo en el enlace Ayuda y enviame un cuaderno con el ejemplo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP