Convertir marcas UTC a hora local en Excel

La mesa de soporte fuera de horario de Marchwood Freight atendió 286 incidencias en 2026, y la revisión de servicio facturó 23 como incumplimientos del objetivo de 30 minutos. Cuatro eran reales. Las otras diecinueve eran una hora, añadida por una resta que cruzaba un huso horario.

La herramienta de tickets exporta en UTC el momento en que se abrió la incidencia: 2026-03-29T01:12:00Z, escrito como texto, con una T en medio y una Z al final. La centralita exporta en hora de Londres el momento en que se atendió.

Nadie lo notó, porque durante cinco meses al año los dos relojes coinciden. Londres va en UTC+0 de finales de octubre a finales de marzo, así que las filas de invierno restan bien y las de verano salen una hora largas.

Una hora no es un redondeo frente a un objetivo de treinta minutos. Es el doble del objetivo, aplicado a toda incidencia abierta entre el último domingo de marzo y el último domingo de octubre.

01Dos Relojes en una Misma Resta

La columna B es lo que escribió la herramienta de tickets: una marca de tiempo ISO 8601 en UTC, como texto. La columna E es lo que escribió la centralita, en hora de Londres. La columna F resta una de la otra y llama respuesta al resultado.

En las filas de verano esa resta es sesenta minutos demasiado generosa, porque la respuesta se registró en un reloj que iba una hora por delante del de la pregunta. INC-2044 tardó 19 minutos y se facturó como 79.

Las columnas C y D son la otra mitad del problema. C convierte UTC a hora local con un =B2+1/24 fijo, correcto en verano y una hora adelantado todas las noches de invierno. D aplica la regla que siguen de verdad los relojes.

02Una Marca UTC es Texto Hasta Que la Conviertes

2026-03-29T01:12:00Z no es una hora para Excel. Son veinte caracteres alineados a la izquierda, y restarlos de cualquier cosa devuelve #¡VALOR! en lugar de una duración.

La marca tiene una forma fija, así que la conversión son dos funciones y nada más:

=FECHANUMERO(IZQUIERDA(A2;10))+HORANUMERO(EXTRAE(A2;12;8))

IZQUIERDA coge 2026-03-29 y FECHANUMERO lo convierte en un número de serie. EXTRAE coge los ocho caracteres que siguen a la T y HORANUMERO los convierte en una fracción de día. Sumados son una marca de tiempo real de Excel.

La T y la Z tienen que desaparecer, lo hagas como lo hagas. =VALOR(SUSTITUIR(SUSTITUIR(A2;"T";" ");"Z";"")) lo resuelve en una celda, y TEXTOANTES y TEXTODESPUES lo resuelven cuando a veces faltan los segundos y no te puedes fiar de las posiciones fijas.

Una columna convertida se alinea a la derecha y una de texto no, que es la comprobación más rápida de la pantalla. =CONTAR(B2:B287) es la lenta y la buena: solo cuenta números, así que cualquier cifra por debajo de 286 es una marca que nunca se convirtió.

03Un Desfase es una Fracción de Día

Una vez que la marca es un número, convertirla es aritmética. Un día es 1, una hora es 1/24, un minuto es 1/1440, y un desfase se suma o se resta como cualquier otro número.

NSHORA(5;45;0) es la forma segura de escribir un desfase raro, porque evita que nadie tenga que decidir cuánto es 5:45 en horas decimales. Media hora es 0,5/24 y tres cuartos son 0,75/24; escribir 5,45/24 es un error de 27 minutos que no ve nadie.

El formato es una decisión aparte del valor. Una celda convertida conserva el formato de número que tuviera, así que una marca correcta puede seguir leyéndose como 46110,05, o como una hora con la fecha escondida.

04Los Cinco Meses en Que un +1 Fijo Miente

Londres no es UTC+1. Es UTC+0 unos cinco meses al año y UTC+1 los otros siete, así que una columna construida sobre =B2+1/24 va una hora por delante en todas las filas de invierno.

Una hora se sobrevive a media tarde. No se sobrevive cerca de medianoche, que es justo donde trabaja una mesa de soporte fuera de horario.

INC-2041 se abrió a las 23:41 UTC del sábado 28 de marzo, cuando los relojes todavía marcaban GMT. El desfase fijo la archivó a las 00:41 del día 29, así que salió del recuento del sábado por la noche y entró en el del domingo.

Nueve de las 286 incidencias cambiaron de fecha así, y dos de las noches a las que se mudaron cruzaron el umbral de cinco incidencias que dispara una revisión.

05Dónde Cambian los Relojes, en una Fórmula

El Reino Unido y la Unión Europea se mueven en los dos mismos instantes: las 01:00 UTC del último domingo de marzo, y las 01:00 UTC del último domingo de octubre.

"El último domingo de marzo" es una fórmula, no una fecha que alguien reescribe cada año:

Empieza  =FECHA(A2;3;31)-DIASEM(FECHA(A2;3;31))+1
Termina  =FECHA(A2;10;31)-DIASEM(FECHA(A2;10;31))+1

DIASEM sin segundo argumento devuelve 1 para el domingo y 7 para el sábado. Restarlo del día 31 y sumar uno cae en el último domingo de ese mes: el 29 de marzo y el 25 de octubre en 2026.

Suma 1/24 a cada resultado y tienes los dos instantes UTC en que cambia el desfase. Ponlos en celdas llamadas InicioVerano y FinVerano, y todas las conversiones del libro leerán los mismos dos números.

Alcance. Estas dos fórmulas son la regla europea y nada más. Estados Unidos cambia el segundo domingo de marzo y el primero de noviembre, a las 02:00 locales, y el hemisferio sur va al revés: Sídney adelanta en octubre y atrasa en abril.

Japón, India, China y casi toda África no cambian nunca, y Arizona y Saskatchewan se quedan fuera de un cambio que sí hacen sus vecinos. La sección 10 es qué hacer cuando un mismo registro reúne a varios.

06La Conversión Que Sabe en Qué Hora Está

Con los dos límites en celdas, el desfase deja de ser una constante y pasa a ser una prueba: =B2+(Y(B2>=InicioVerano+1/24;B2<FinVerano+1/24))/24.

La comparación devuelve VERDADERO o FALSO, Excel los lee como 1 y 0, y dividir entre 24 convierte la respuesta en una hora o en nada. Una fórmula, las dos estaciones, ninguna lista de fechas que mantener.

LET dice lo mismo en voz alta cuando los límites se calculan ahí mismo en vez de tener nombre:

=LET(utc; B2;
     a; AÑO(utc);
     empieza; FECHA(a;3;31)-DIASEM(FECHA(a;3;31))+1+1/24;
     termina; FECHA(a;10;31)-DIASEM(FECHA(a;10;31))+1+1/24;
     utc + (Y(utc>=empieza; utc<termina))/24)

=B2+SI(Y(B2>=InicioVerano+1/24;B2<FinVerano+1/24);1;0)/24 es la misma prueba escrita entera, para quien desconfíe de un VERDADERO haciendo aritmética. Excel lee las dos igual.

Las dos comparaciones son contra UTC, nunca contra la columna local. Probar una marca local contra un límite local es circular: la hora que intentas averiguar es la que necesitarías para hacer la prueba.

07Nunca Restes Entre Dos Husos

Los 3.450 € salieron de una fórmula: =(E2-B2)*1440, los minutos entre la marca de apertura y la de respuesta. Es aritmética correcta sobre dos números que no se miden desde el mismo origen.

De finales de marzo a finales de octubre, E va una hora por delante de B antes de que nadie haga nada, así que la resta devuelve la respuesta real más sesenta minutos. Diecinueve incidencias cruzaron la línea de los 30 minutos solo por esa hora.

El arreglo es una norma, no una fórmula: convierte todo a UTC a la entrada, haz ahí todas las restas y pasa a hora local solo para mostrar. Una duración no tiene huso horario, así que en cuanto dos husos se juntan dentro de una resta el resultado falla por la diferencia entre ellos.

INC-2047 es la fila que lo explica todo. Es un incumplimiento real, de noviembre, en una noche en que los dos relojes coincidían, y pasó la revisión enterrada en una lista de veintitrés donde diecinueve eran un artefacto de la aritmética.

08La Hora Que Ocurre Dos Veces

A las 02:00 locales del 25 de octubre los relojes vuelven a las 01:00, así que la 01:30 local ocurre dos veces esa noche: una a las 00:30 UTC y otra, una hora después, a las 01:30 UTC.

Una marca local dentro de esa hora es genuinamente ambigua. Nada en la celda dice cuál de las dos era y ninguna fórmula puede recuperarlo, y por eso un registro guardado en hora local ya ha perdido algo que un registro en UTC nunca tuvo que guardar.

En marzo pasa lo contrario. Entre la 01:00 y las 02:00 locales del 29 de marzo no hay hora ninguna: los relojes saltan de las 00:59 a las 02:00. Una marca local de la 01:30 esa noche no es un error que puedas corregir, solo uno que puedas señalar.

Ambigua     =Y(D2>=FinVerano+1/24;    D2<FinVerano+2/24)
Imposible   =Y(D2>=InicioVerano+1/24; D2<InicioVerano+2/24)

INC-2046 cae en la hora ambigua: la 01:30 local del 25 de octubre. Su marca UTC es lo único que nos dice cuál de las dos 01:30 era. Pasa las dos pruebas por la columna local y las filas que devuelvan VERDADERO son las que hay que leer en el sistema de origen, no en la hoja.

09Informar por Día Local Sin Mover Incidencias

El cliente lee un recuento por noche en su propia hora, así que el informe tiene que ser local aunque la aritmética no lo sea. Eso es una columna más, no una segunda versión del registro.

=ENTERO(D2) quita la hora y deja la fecha local. Contar sobre esa columna es honesto; contar sobre ENTERO de la columna UTC archiva la última hora de cada noche de invierno en el día siguiente.

Día local     =ENTERO(D2)
Hora de noche =HORA(D2)
Incidencias   =CONTAR.SI.CONJUNTO($I$2:$I$287;J2)
Minutos       =SUMAR.SI.CONJUNTO($G$2:$G$287;$I$2:$I$287;J2)
Media         =REDONDEAR(PROMEDIO(FILTRAR($G$2:$G$287;$I$2:$I$287=J2));1)

HORA sobre esa misma columna da el perfil por hora de la noche que el cliente pide después, y se tuerce igual si la apuntas a la columna UTC.

Un criterio de fecha tiene que llegar a CONTAR.SI.CONJUNTO como número, que es justo lo que da ENTERO. Escribir "25/10/2026" en el criterio funciona en una máquina cuya configuración regional coincide con la tuya y devuelve un cero silencioso en la de un compañero.

10Cuando el Registro Tiene Varios Países

Una mesa es una regla. Tres mesas son una tabla, porque los hemisferios no coinciden en qué meses son verano y América cambia en domingos distintos de los de Europa.

Guarda el desfase base y los dos límites en una tabla pequeña y léelos por fila: =BUSCARX($C2;Sedes[Sede];Sedes[Desfase]) devuelve la base, y la misma búsqueda contra las columnas de límites alimenta la prueba. INDICE y COINCIDIR hacen lo mismo donde no haya BUSCARX.

Excel no tiene una base de datos de husos horarios. Ninguna función recibe una ciudad y un instante y devuelve el desfase vigente, así que lo que construyas es una tabla que alguien tiene que mantener, y ese es el argumento honesto para convertir antes de que los datos lleguen a la hoja.

11Escribir una Marca de Vuelta

Todo lo que sale de la hoja hacia otro sistema sale en UTC y en ISO 8601, porque es la única forma que ningún lector tiene que adivinar:

=TEXTO(utc;"yyyy-mm-dd") & "T" & TEXTO(utc;"hh:mm:ss") & "Z"

Construirla con dos TEXTO deja la T y la Z literales fuera de la cadena de formato, donde habría que escaparlas y donde seguirían sorprendiendo a la siguiente persona que edite la celda.

AHORA() no es UTC. Lee el reloj de la máquina, así que una marca de "recibido" escrita por un portátil de Londres en julio va una hora por delante de otra escrita por la misma fórmula en enero.

Ninguna de las dos celdas lo dice. Conviértela en el momento de capturarla, con la misma prueba de límites que usa el resto de la hoja.

12Power Query, Si el Fichero Llega Cada Semana

Un registro que llega cada semana debería convertirse al importar, no en una columna que alguien se acuerda de arrastrar. Power Query lee el texto ISO como tipo datetimezone y lo convierte en un solo paso.

Qué necesitas. DateTimeZone.FromText lee la marca con su desfase, DateTimeZone.SwitchZone la lleva a un desfase fijo y DateTimeZone.ToLocal la lleva a la zona de la propia máquina, con el horario de verano aplicado. Esa última depende de la máquina, lo cual está bien en un escritorio y es una trampa en una actualización programada en la nube.

Un paso de consulta además sobrevive a que alguien inserte una columna, porque se refiere a la columna por su nombre y no por su dirección. Tres de los cuatro fallos de este artículo tienen forma de dirección.

Las fórmulas de hoja siguen mereciendo la pena. Son a lo que recurres cuando el fichero ya está abierto, cuando un compañero te manda veinte filas por correo y cuando tienes que enseñar a un cliente de dónde sale exactamente su número.

13Ocho Cosas Que Muerden

1. Un +1/24 fijo. Correcto siete meses y una hora adelantado cinco, y hace su peor daño en las filas más cercanas a medianoche.
2. Restar una marca local de una UTC. El resultado falla por el desfase siempre, y parece exactamente una duración plausible.
3. Dejar la marca como texto. #¡VALOR! es el buen desenlace; un cero silencioso dentro de un SUMAR.SI.CONJUNTO es el malo.
4. Tratar AHORA() como un reloj UTC. Es el reloj de la máquina, y la máquina se mueve con las reglas locales dos veces al año.
5. Probar el límite contra la hora local. Circular: la respuesta que necesitas es justo lo que estás probando.
6. Un criterio de fecha escrito como texto. "25/10/2026" se lee distinto en una máquina en orden estadounidense; pasa ENTERO de la marca o una FECHA.
7. Tratar la 01:30 de una noche de cambio como un solo instante. Son dos, y solo el sistema que exporta sabe cuál.
8. Una tabla de desfases sin dueño. Los gobiernos mueven estas fechas, y una tabla escrita una vez falla el primer año que lo hagan.

14Mini Ejercicios

1. Convierte la fila 2 con =FECHANUMERO(IZQUIERDA(B2;10))+HORANUMERO(EXTRAE(B2;12;8)) y dale formato dd/mm/yyyy hh:mm. Espera 28/03/2026 23:41.
2. Escribe los dos límites de 2026 con las fórmulas de DIASEM de la sección 5. Espera el 29 de marzo y el 25 de octubre.
3. Convierte las ocho filas con la prueba de Y y compáralas con la columna D. Espera que todas coincidan con D y que cuatro discrepen de C.
4. Recalcula los tiempos de respuesta desde dos marcas UTC. Espera que los 23 incumplimientos bajen a 4 y que el mayor baje de 86 minutos a 34.
5. Señala la hora ambigua con =Y(D2>=FinVerano+1/24;D2<FinVerano+2/24). Espera VERDADERO solo en INC-2046, porque la 01:30 local del 25 de octubre ocurrió dos veces.

Qué Llevarte

En este fichero no hay nada mal tecleado. Todas las marcas eran exactas, todas las restas eran la aritmética que escribiría cualquiera, y la factura que produjeron entre las dos era cinco veces la que pedía el contrato.

El error fue que dos columnas medían el tiempo desde dos orígenes distintos y nada en la hoja lo decía. Una marca de tiempo sin huso es un número sin unidades, y Excel resta una de otra sin rechistar.

Así que quédate con una norma y el resto va solo: el registro es UTC, el informe es local y la conversión ocurre en una única columna. Diecinueve de aquellos veintitrés incumplimientos no habrían llegado nunca a un papel.