14

Truco: Convertir los Timestamps de UNIX a fechas en un instante

Hace unos días estuve haciendo unas tareas de mantenimiento y optimización de la base de datos del foro de Ayuda Excel. Para ello me descargué dos tablas para analizarlas en profundidad (con Excel, claro).

La base de datos está almacenada en un entorno UNIX, por lo que las fechas se guardan en un formato que no es el habitual. Hasta ahora me había dado igual, ya que los análisis y estadísticas que hago periódicamente, poco tenían que ver con fechas, pero ahora necesitaba conocer estas fechas.

Si abres una tabla y te fijas en algún campo con forma de fecha, verá que los datos que contiene son un montón de números sin sentido llamados timestamp.

Timestamp en Excel

Este timestamp o sello de tiempo se puede definir como el número de segundos transcurrido desde la medianoche del 1 de enero de 1970 hasta este momento.

Por ejemplo, si ahora son las 08:00:00 del día 24 de marzo de 2015, significa que han pasado 1427184000 segundos desde enero de 1970.

En internet puedes encontrar varias webs donde convertir el formato UNIX en algo más fácil de entender por los humanos. Por ejemplo, en http://www.unixtimestamp.com/ puedes hacer la conversión a las dos unidades.

Pero…. aquí lo que nos interesa es hacerlo con Excel, ¿no?. Para convertir varias fechas es mejor utilizar una fórmula de Excel que introducirlas a mano en una web. La fórmula que te propongo hoy es la siguiente:

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

¿Qué son estos números?

  • A1: es la referencia de la celda que contiene la fecha timestamp que quieres convertir.
  • 86400: es el número de segundos que contiene un año.
  • 25569: Son los 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): es el ajuste de los días del año.

El resultado devuelto por la fórmula debes darle formato de fecha. Pulsa Ctrl + 1 y selecciona Fecha del cuadro de diálogo Formato de número. Para un resultado más exacto, escribe dd/mm/aaaa – hh:mm:ss;@ dentro de la pestaña Personalizado.

Éste sería el resultado:

 

Timestamp en Excel

Nota: Ya sabes que Excel trabaja con fechas convertidas en números de serie que comienzan el 1 de enero de 1900, así que para convertir el timestamp en una fecha legible, debes sumarle la diferencia de fechas.

¿Te ha resultado útil? ¿Piensas que lo podrás aplicar en tus tareas?

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 14 comentarios
Jesús

interesante

Responder
danilo

Muy interesante
para analizar.
saludos
danilo

Responder
Daniel Aragón Hüeck

Gracias por compartirlo, te felicito por tu Web tienes una muy buena forma de enseñar y lo que enseñas es interesante!!!

Responder
Jose Cienfuegos

Sergio solo una observación, en todo el artículo te refieres a segundos en el año, debiendo ser a Minutos, me percate ya que no me daban las operaciones matemáticas, Excelente trabajo Saludos

Responder
    Sergio

    José, son segundos la unidad en la que está expresado el número.

    Compruebalo en la siguiente dirección:
    http://www.unixtimestamp.com/

    Si te aparecen errores de cálculo, coméntamelo y ajustamos la fórmula en caso de que haya que hacerlo.

    Responder
      Jose Cienfuegos

      Sergio, disculpa mi insistencia pero mencionas “86400: es el número de segundos que contiene un año”

      Si multiplicamos 24 horas de cada día por 60 minutos cada hora = 1,440 minutos al día si los multiplicamos por 60 segundos cada minuto = 86,400 segundos en un día.

      Por lo tanto la mención sería: “86,400: es el número de segundos que contiene un día”
      Mi observación anterior era errónea, esta es la correcta.

      Saludos

      Responder
Manuel

Sergio:

Muy buena esta fórmula, me sirvió en mi trabajo, aunque ya había solucionado el asunto de una manera digamos poco ortodoxa, con esta fórmula puedo justificar el resultado.

Saludos

Responder
Julia

Muchas gracias por la fórmula. Estaba sufriendo con unos datos que debía procesar y me traían Starttime en ese formato.
Gracias again!

Julia
Pd. es cierto, los 8640 0 son los segundos de un día, sin embargo, eso no invalida la fórmula.

Responder
Elena

Estimado Sergio:
tu fórmula me ha sido de gran ayuda, llevaba mucho tiempo peleándome con el timestamp y tu solución me ha venido genial, gracias.

Responder
    Sergio

    Muchas gracias, Elena! La verdad es que publiqué este artículo porque yo también tenía el mismo problema que tú, así que, después de solucionarlo, decidí compartirlo.

    Responder
Mark

También vale para LibreOfficeCalc!!!

Responder
    Sergio

    ¡Claro, Mark! Como no utiliza funciones propias de Excel, se puede utilizar también en Google Sheets y en cualquier hoja de cálculo.

    Responder
Florenci Falgas Carol

Hola Sergio, Tengo un problema con el timestamp, lo tengo en la celdas en primera posición pero después tengo otros valores separados por comas , que cambios se tendrían que hacer en tu formula para que solo cogiera el primer valor antes de la coma, pòr que no me esta funcionando. en la celda me sale #¡VALOR! Gracias y Saludos.

Responder
    Sergio

    Hola Florenci!
    En ese caso primero debes encontrar qué hay delante de la coma y luego aplicarle la fórmula al resultado. Prueba con esta fórmula:

    =EXTRAE((A1/86400)+25569+(-5/24);1;ENCONTRAR(“,”;(A1/86400)+25569+(-5/24);1)-1)

    Responder

Escribe una respuesta:

Esta web usa cookies propias y de terceros para mejorar tu experiencia de navegación, elaborar información estadística y analizar tus hábitos de navegación. Si no estuvieras conforme puedes eliminarlos. Al hacer clic en "Aceptar" o si continúas navegando, aceptas su uso. Puedes revocar tu consentimiento en cualquier momento. Leer más

Los ajustes de cookies de esta web están configurados para «permitir cookies» y así ofrecerte la mejor experiencia de navegación posible. Si sigues utilizando esta web sin cambiar tus ajustes de cookies o haces clic en «Aceptar» estarás dando tu consentimiento a esto.

Cerrar