Calcular tiempo en horas entre dos fechas sin festivos ni fines de semana y con rango horario

jcascon11 · el 12 de marzo

Buenas:

Necesito calcular el tiempo entre una fecha inicial y una fecha final sin contar festivos, sabados y domingos y con este rango horario

De lunes a jueves el horario a contar es de 8:00 a 15:00 y de 17:00 a 19:30

Los viernes de 8:00 a 15:00

Alguien me puede ayudar?

Gracias.

Fleming · el 13 de marzo

Hola Jcascon11

Debes leer las Normas del Foro y cumplir lo que se estipula en ellas.

Un saludo

el 14 de marzo

solo comenta si usas una sola celda para fecha y hora (inicio) y otra para fecha y hora (fin)

(o... celdas separadas para fecha y hora ?

y comenta también en que rango depositaras las fechas festivas

saludos,

hector.

jcascon11 · el 14 de marzo

Hola:

Pido disculpas por lo de las normas.

El tema de los campos de fecha, me daría igual usar una celda para fecha y hora que una para cada una. (la forma que sea mas facil). Lo unico que las fechas de inicio y de fin tambien pueden estar fuera de los rangos horarios especificados y solo habria que calcular el tiempo dentro de los rangos.

No se a que te refieres con el rango de las fechas festivas?

Un saludo

el 15 de marzo

las restricciones/condiciones de tu consulta tienen su grado de complejidad "interesante" (horarios mixtos y diferente para los viernes), te paso una forma de solucionarlo, pero considera que por convencionalismos regionales, mi sistema usa coma para separar los argumentos en formulas y funciones, y en matrices constantes (o entre llaves) utiliza el separador de rutas "\" para indicar filas)

este es el escenario (o arreglo de datos) que debes preparar para reproducir el siguiente ejemplo:

1) pon las fechas festivas en un rango y le asignas nombre (p.e.) de "Fest" (ene-1-2013, may-10-2013, dic-25-2013, etc.)

2) define los diguientes 6 nombres usando formulas para...

dLab1 (para identificar los días laborables lunes a viernes)

=diasem(fila(indirecto(!$b3+1&":"&!$d3-1)),2)[/CODE]

dLab2 (para identificar los días excluyendo los festivos)

[CODE]=contar.si(fest,fila(indirecto(!$b3+1&":"&!$d3-1)))[/CODE]

eL (para identificar el horario de entrada de lunes a jueves)

[CODE]={0\8\15\17\19.5}/24[/CODE]

eV (para identificar el horario de entrada para los viernes)

[CODE]={0\8\15}/24[/CODE]

sL (ídem, horario de salida de lunes a jueves)

[CODE]={0\8\15\17\19.5}/24[/CODE]

sV (ídem horario de salida para los viernes)

[CODE]={0\8\15}/24[/CODE]

3) usa celdas separadas para fecha y para hora (tanto inicio como fin), p.e.

[b3] la fecha de inicio

[C3] la hora de inicio

[D3] la fecha de fin

[E3] la hora de fin

4) en la celda [G3] la siguiente formula determina las horas aplicables al dia uno:

[CODE]=dias.lab(b3,b3,fest)*(diasem(b3,2)<5)*elegir(coincidir(c3,el),"9:30","15:00"-c3+"2:30","2:30","19:30"-c3,0)+(diasem(b3,2)=5)*elegir(coincidir(c3,ev),"7:00","15:00"-c3,0)[/CODE]

5) en la celda [H3] la siguiente formula determina las horas aplicables a los días "intermedios":

[CODE]=(dias.lab(b3,d3,fest)>2)*sumaproducto((dlab1<6)*(dlab2=0)*("15:00"-"8:00")+(dlab1<5)*(dlab2=0)*("19:30"-"17:00"))[/CODE]

6) en la celda [i3] la siguiente formula determina las horas aplicables del dia "fin":

[CODE]=dias.lab(d3,d3,fest)*(diasem(d3,2)<5)*elegir(coincidir(e3,sl),0,e3-"8:00","7:00",e3-"17:00","9:30")+(diasem(d3,2)=5)*elegir(coincidir(e3,sv),0,e3-"8:00","7:00")[/CODE]

7) en la celda [J3] simplemente sumas los resultados anteriores (p.e.)

op1: [J3] =suma(g3:i3)*24 <= totaliza las horas en notación decimal

op2: [J3] =suma(g3:i3) <= aplica formato personalizado de [h]:mm <= totaliza las horas en notación hexadecimal

podrias hacer juntar lo anterior en una sola "super-mega formula" (pero nos seria fácil de entender/modificar/...)

las formulas de los nombre "similares" la conserve por si cambian esas condiciones (?)

haz pruebas y comentas ?

saludos,

hector.

p.d. edito para aclarar un detalle (que aunque pequeño, es muy importante):

al momento de definir los nombres dLab1 y dLab2 debe estar seleccionada cualquier celda, pero... => EN LA FILA 3 <=

pegones1 · el 17 de marzo

La verdad es que no he probado la propuesta tan elaborada de Héctor Miguel, pero tiene buena pinta y la tendré que chequear con la que propongo y he publicado esta tarde en mi blog, con dos formas de calcular las horas, una para cualquier versión de Excel y otra para Excel 2010, gracias a la función DIA.LAB.INTL() que permite discriminar como día laboral cualquier día de la semana. Lo he intentado explicar en mi blog por lo que no me repito aquí. Lo que si que digo es que es un poco más general de lo que pide jcascon11 ya que permite hasta 4 rangos y él solo necesita 2 rangos horarios y permite seleccionar individualmente cualquier día de la semana como día laboral.

jcascon11 prueba el fichero adjunto y me cuentas si te sirvió y haces click pinchando en el corazoncito de "Me gusta" de abajo para que suba mi reputación.

HorasEntreFechas-PW1.xls