Convertir fechas tiempo UNIX (POSIX) en fechas Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Al importar datos de muchas bases de datos, obtenemos las fechas con formato UNIX, debiendo convertir fechas tiempo UNIX (POSIX) en fechas Excel.

Convertir fechas tiempo UNIX (POSIX) en fechas Excel

Tiempo Unix o Tiempo POSIX es un sistema para la descripción de instantes de tiempo: se define como la cantidad de segundos transcurridos desde la medianoche UTC del 1 de enero de 1970.

Excel calcula las fechas utilizando los días transcurridos desde el 1/1/1900. Por lo tanto, podemos convertir los formatos cambiando de segundos a días y luego agregar la diferencia de 70 años.

En la columna A disponemos de fechas con formato UNIX y en la columna B deseamos convertirla a formato Excel.

 AB
1Fecha UNIXFecha Excel
21514764800 
31462066200 
41217888348 
51398729600 
  • MOSTRAR FECHA

  1. Ir a: Celda B2 / Escribir:

    =A2 / 86400 + 25569

    86400: Número de segundos que contiene un año.
    25569: Días transcurridos entre las 00:00:00 del 1 de enero de 1900 y las 00:00:00 del 1 de enero de 1970.

    O también,

    =(((A2/60)/60)/24)+FECHA(1970;1;1)
  2. Copiar la celda B2 / Pegar en el rango B3:B5.

    Ventana formato de celdas con formato número de fecha en B2
  3. Seleccionar el rango B2:B5.

  4. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Número / Formto de número / Seleccionar: Fecha corta. Mostrará la fecha en formato Excel.

    Ventana formato de celdas con formato fecha 01/01/2018 en B2
  • MOSTRAR FECHA Y HORA

El tiempo Unix es el número de segundos desde el 1 de enero de 1970 para UTC / GMT, mientras que Excel usa la cantidad de días y fracciones del día para la zona horaria local.

  1. Ir a: Celda C2 / Escribir:

    =A2 / 86400 + 25569

    86400: Número de segundos que contiene un año.
    25569: Días transcurridos entre las 00:00:00 del 1 de enero de 1900 y las 00:00:00 del 1 de enero de 1970.

    O también,

    =(((A2/60)/60)/24)+FECHA(1970;1;1)
  2. Copiar la celda C2 / Pegar en el rango B3:B5.

  3. Seleccionar el rango C3:C5.

  4. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Número / Formato de número; O también botón derecho del ratón / Formato de celdas. Mostrará la ventana de formato de celdas.

    Ventana formato de celdas con tipo personalizado dd/mm/aaaa h:mm:ss en C2
  5. Clic sobre la pestaña: Número.

  6. Clic sobre la categoría: Personalizada.

  7. Ir a: Tipo / Escribir:

    dd/mm/aaaa h:mm:ss
  8. Pulsar el botón Aceptar. Cierra la ventana y aplica el formato.

Hoja Excel con valor 01/01/2018 00:00:00 en C2
  • MOSTRAR FECHA Y HORA (CON ZONA HORARIA)

    Para agregar la zona horaria, sumar o restar las horas deseadas con la fecha 1/1/1970. Adaptando los husos horarios o cambios horarios por ahorro energético (horario de verano).

    En nuestro caso aplicamos una diferencia de -5 horas.

  1. Ir a: Celda D2 / Escribir:

    = (A1 / 86400) +25569 + (- 5/24)

    86400: Número de segundos que contiene un año.
    25569: Días transcurridos entre las 00:00:00 del 1 de enero de 1900 y las 00:00:00 del 1 de enero de 1970.
    (- 5/24): Número de horas de diferencia (-5), entre 24 horas del día.

  2. Copiar la celda D2 / Pegar en el rango D3:D5.

  3. Seleccionar el rango D2:D5.

  4. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Número / Formato de número; O también botón derecho del ratón / Formato de celdas. Mostrará la ventana de formato de celdas.

    Ventana formato de celdas con tipo personalizado dd/mm/aaaa h:mm:ss
  5. Clic sobre la pestaña: Número.

  6. Clic sobre la categoría: Personalizada.

  7. Ir a: Tipo / Escribir:

    dd/mm/aaaa h:mm:ss
  8. Pulsar el botón Aceptar. Cierra la ventana y aplica el formato.

Hoja Excel con valor 31/12/2017 19:00:00 en D2

  

Nivel de dificultad: Avanzado Excel Avanzado

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *