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.
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 día.
- 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:
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?
interesante
Muy interesante
para analizar.
saludos
danilo
Gracias por compartirlo, te felicito por tu Web tienes una muy buena forma de enseñar y lo que enseñas es interesante!!!
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
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.
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
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
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.
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.
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.
También vale para LibreOfficeCalc!!!
¡Claro, Mark! Como no utiliza funciones propias de Excel, se puede utilizar también en Google Sheets y en cualquier hoja de cálculo.
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.
Hola FFC!
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)
Un millón de gracias… llevaba dos días buscándole una solución y la tuya ha venido genial. Gracias por el tip y el tiempo.
Lo utilicé en una base de datos de alumnos que descargué de Moodle.
86400: es el número de segundos que contiene un DIA no un año.
Tienes razón Marius! Lo acabo de corregir. Un año tiene 31.536.000 segundos.