Calculo de tiempo entre fechas, un reto !
Hola a todos, he estado intentando calcular un tiempo transcurrido entre días con fórmulas de Excel y no he podido, la situación e sal siguiente: considerando como horas diurnas el tiempo de 6:00 a 18:00 y horas nocturnas de 18:01 a 5:59, calcular el tiempo que ha transcurrido entre varios días, o en su defecto el mismo día. Ejemplo:
A B C D
1 FECHA 1 HORA 1 FECHA 2 HORA 2
2 29-12-10 13:35 06-01-11 09:00
3 HORAS DIURNAS 89.25 HORAS NOCTURNAS 96
Las celdas B3 y D3 reflejan el tiempo en horas de lo que ha transcurrido, ¿cómo hacer que al digitar los datos de la fila 2 me calcule los campo de la fila 3?, no se olviden de la consideración de horas diurnas y nocturnas.
Gracias !
A B C D
1 FECHA 1 HORA 1 FECHA 2 HORA 2
2 29-12-10 13:35 06-01-11 09:00
3 HORAS DIURNAS 89.25 HORAS NOCTURNAS 96
Las celdas B3 y D3 reflejan el tiempo en horas de lo que ha transcurrido, ¿cómo hacer que al digitar los datos de la fila 2 me calcule los campo de la fila 3?, no se olviden de la consideración de horas diurnas y nocturnas.
Gracias !
1 Respuesta
Respuesta de caranbis
1
Basado en el ejemplo que das, la fórmula para calcular las horas diurnas (celda b3), es:
=suma(si((si(b2<0,75;si(b2<0,25;0;1)))=1;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1)))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1)))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1)))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
Y la fórmula para calcular las horas nocturnas (celda d3), es:
=suma(si((si(b2<0,75;si(b2<0,25;0;1)))=0;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1)))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1)))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1)))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
No preguntes cómo lo hice mi querido amigo, je je, es muy complicado de explicar, pura matemática y lógica matemática, sólo confía, está bien, hice algunas pruebas, pero cualquier inconsistencia me avisas.
=suma(si((si(b2<0,75;si(b2<0,25;0;1)))=1;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1)))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1)))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1)))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
Y la fórmula para calcular las horas nocturnas (celda d3), es:
=suma(si((si(b2<0,75;si(b2<0,25;0;1)))=0;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1)))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1)))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1)))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
No preguntes cómo lo hice mi querido amigo, je je, es muy complicado de explicar, pura matemática y lógica matemática, sólo confía, está bien, hice algunas pruebas, pero cualquier inconsistencia me avisas.
Recuerda que si quieres representar los resultados en HORAS y MINUTOS, debes multiplicar los decimales por 60, es decir, si por ejemplo da 42,75 horas diurnas, debes multiplicar 0,75 por 60 y así sacas los minutos, es decir:
0,75 * 60 = 45
Entonces serían, 42 horas y 45 minutos.
0,75 * 60 = 45
Entonces serían, 42 horas y 45 minutos.
Hola caranbis, muchas gracias por tu gestión estupenda anotación y claro no te voy a peguntar donde salió eso, pero he probado algunas fechas y no me fucnionan, prueba esto:
A B C D
1 FECHA 1 HORA 1 FECHA 2 HORA 2
2 01-01-2011 18:35 07-01-11 08:00
3 HORAS DIURNAS 60 HORAS NOCTURNAS 60
me arroja siempre 60 horas y no me da decimales, los cuales corresponden a los minutos transcurridos. También pasa si pones cualquier cantidad de minutos a la hora 1 y 2.
Espero por favor me ayudes .
A B C D
1 FECHA 1 HORA 1 FECHA 2 HORA 2
2 01-01-2011 18:35 07-01-11 08:00
3 HORAS DIURNAS 60 HORAS NOCTURNAS 60
me arroja siempre 60 horas y no me da decimales, los cuales corresponden a los minutos transcurridos. También pasa si pones cualquier cantidad de minutos a la hora 1 y 2.
Espero por favor me ayudes .
Tienes toda la razón, me faltaba un 1 por ahí, ya están corregidas, sigue probando, cualquier cosa me avisas.
Horas diurnas:
=suma(si((si(b2<0,75;si(b2<0,25;0;1);1))=1;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1);1))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1);1))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1);1))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
horas nocturnas:
=suma(si((si(b2<0,75;si(b2<0,25;0;1);1))=0;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1);1))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1);1))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1);1))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
Horas diurnas:
=suma(si((si(b2<0,75;si(b2<0,25;0;1);1))=1;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1);1))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1);1))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1);1))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
horas nocturnas:
=suma(si((si(b2<0,75;si(b2<0,25;0;1);1))=0;(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24)));0);(entero(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12)/2))*12;si(y((si(b2<0,75;si(b2<0,25;0;1);1))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=0);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si(y((si(b2<0,75;si(b2<0,25;0;1);1))=0;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))=1);(residuo(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))));12));0);si((si(b2<0,75;si(b2<0,25;0;1);1))=1;(residuo(entero(((((c2-a2)*24)+((d2-b2)*24))-(si(b2<0,75;si(b2<0,25;(0,25-b2)*24;(0,75-b2)*24);12-((b2-0,75)*24))))/12);2))*12;0))
