Coordinar horarios con Excel

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.

Formato personalizado numérico condicional en Excel

En los albores de este blog ya había tocado el del formato personalizado de números. Vuelvo sobre el tema debido a la consulta que puso uno de mis lectores en un comentario de la entrada:

tengo unos registros en donde guardo la hora del día, pero quiero que me ponga madrugada si la hora es de 0 a las 5, mañana si es de 6 a 12, tarde de 13 a 19, y noche de 20 a 24, ¿se podrá con el formato personalizado?

En formato personalizado de celda de Excel existe la posibilidad de usar formatos condicionales. El método es muy elemental y admite sólo tres condiciones, de las cuales dos son explícitas y la tercera implícita. Las condiciones, o criterios, son comparaciones numéricas sencillas.

Si la consulta de mi estimado lector se limitara a tres condiciones, digamos mañana, tarde y noche, se podría hacer. Para el caso que queramos que aparezca "mañana" si la hora es anterior o igual a las 12:00, "tarde" si cae entre las 12:00 y las 18:00 y noche para el resto, la sintaxis es la siguiente

[<=0,5]"Mañana";[>0,75]"Noche";"Tarde"

Las dos condiciones explícitas las ponemos entre [] y la implícita es "todo el resto".

El valor 0,5 resulta de dividir 12 por 24; el valor 0,75 de dividir 18 por 24. Como ya explicamos en el pasado, en Excel las horas son números que resultan de dividir la hora por el total de horas del día (24).

En esta imagen podemos ver las horas 08:00 y 16:00 con tres formatos distintos: hora, general y personalizado condicional

Dado que el formato numérico condicional está limitado a tres criterios, no podemos resolver la consulta de mi lector con esta herramienta. La alternativa es usar formato condicional, que a partir de Excel 2007 no está limitado a tres condiciones. En el caso de la consulta los criterios son:

Usaremos formato condicional con la opción Fórmula, definiendo una fórmula para cada uno de los criterios. Para evitar problemas de precisión, usaremos la función HORANUMERO para calcular los números de series de las horas

El cuaderno con el ejemplo se puede descargar aquí.

Formato condicional numérico

Josep me consulta cómo cambiar el formato numérico de una celda en función del valor de otra celda (celda de control). La idea es que si en la celda de control aparece el símbolo del Euro (€) el número de la celda formada condicionalmente reciba el forma “moneda-Euro”; si en la celda de control aparece el símbolo del dólar ($), el formato del número cambiará de acuerdo.

Microsoft introdujo en Excel 2007 (y en Excel 2010) la posibilidad de dar formato condicional numérico. Con las nuevas versiones de Excel podemos podemos hacer esto:

y si cambiamos el símbolo, veremos esto

En la celda B2 usamos Validación de Datos con la opción Lista

En E2 definimos dos reglas para el formato condicional

En las versiones de Excel Clásico (97-2003) no tenemos la posibilidad de definir el formato numérico con Formato Condicional. Así que tendremos que echar mano al Vba (macros). Lo que haremos es programar un evento en la hoja relevante

Private Sub Worksheet_Change(ByVal Target As Range)

    If Union(Target, Range("B2")).Address = Range("B2").Address Then
        If Range("B2") = "€" Then Range("E2").NumberFormat = "[$€-2] #,##0.00"
        If Range("B2") = "$" Then Range("E2").NumberFormat = "[$$-409]#,##0.00"
    End If
  
End Sub

¿Cómo saber qúe código de formato usar (por ejemplo "[$€-2] #,##0.00" para el Euro)? Formamos una celda con el formato deseado; abrimos el menú de formato de celdas y en Número elegimos la opción Personalizado. En la ventanilla Tipo veremos la sintaxis del formato