Coordinar horarios con Excel

miércoles, febrero 15, 2012

Esta nota viene a colación de mi reencuentro con un entrañable amigo de la juventud. Nos unen maravillosos recuerdos de la juventud pero nos separan 15 husos horarios.

Después de intercambiar nuestras direcciones de Skype empezamos a coordinar el día y la hora para comunicarnos. Fijamos el próximo fin de semana, pero ¿a qué hora? Aquí es donde Excel puede darnos una buena ayuda.

Yo resido en la Ciudad 1 y mi amigo en la Ciudad 2. La diferencia horaria es de 15 horas y el día elegido para conversar es el sábado 18 de febrero. Las horas activas, es decir, cuando nuestro estado mental permite una conversación más o menos coherente, son entre las 9 de la mañana y las 10 de la noche (cada uno en su ciudad). Este es el modelo que construí en Excel para ver las horas en que ambos coincidimos



En el cuadro de definiciones (el rango E2:F6) ponemos la fecha, la diferencia horaria, y las horas de comienzo y final del horario de actividad y en el rango H2:I25 vemos las horas en que coincidimos con fondo verde y texto en negrita.

¿Cómo funciona el modelo?

Las celdas de la tabla de horarios (el rango E2:F6 H2:I25) contienen esta fórmula:

=$F$2+((FILA()-2)/24) para Ciudad 1

=$F$2+((FILA()-2+$F$3)/24) para Ciudad 2


donde $F$3 es la diferencia horaria entre las ciudades

La expresión (FILA()-2)/24 calcula la hora del día en saltos de 1 hora, usando el valor de la función FILA y dividiendo por 24 (ese es el método que usa Excel para los cálculos de horas). Esta fórmula nos permite pasar al día siguiente cuando se cumplen las primeras 24 horas.

Para mostrar el resultado con el día de la semana usamos formato personalizado:



Para poner el fondo verde y el texto en negrita usamos formato condicional. La técnica que propongo usa columnas auxiliares ocultas, como seguramente mis perspicaces lectores habran ya notado (en la primer imagen la primer columna visible es la columna D).



Como puede verse, la fórmula usada es muy sencilla: =$C2=1. Veamos qué hay en C2



En A2 ponemos la fórmula:

=Y(HORA(H2)>=HORA($F$5);HORA(H2)<=HORA($F$6))

El resultado es verdadero si la hora de la fecha evaluada en la columna de la ciudad 1 cae dentro del rango establecido entre F5 y F6; lo mismo con la fórmula en la celda B2 para la ciudad 2

=Y(HORA(I2)>=HORA($F$5);HORA(I2)<=HORA($F$6))

En la columna C multiplicamos ambos resultados. Como se trata de expresiones lógicas, dan 1 si ambos resultados son VERDADERO y 0 si alguno de los resultados es FALSO.

Si bien podemos crear una fórmula compleja que combine las tres fórmulas y usarla directamente en el formato condicional, esta técnica tiene en mi opinión la ventaja de exponer en forma más clara la forma del cálculo.

3 comments:

Anónimo,  16 febrero, 2012 03:34  

Hola Jorge:
Quisiera saber que fórmula a aplicado en la celdas I2:I25 de su ejemplo para que me salga sábado 15:00
Te Agradezco
Saludos.

Jorge L. Dunkelman 16 febrero, 2012 06:47  

Gracias por llamarme la atención, reakmente falta la explicación. La fórmula es

=$F$2+((FILA()-2+$F$3)/24)

donde $F$3 es la diferencia de horarios entre las ciudades.

Hugo Lozano 01 febrero, 2016 18:40  

una cosa mas aprendida, fenomenal Sr. Dunkelman

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP