Print this page
Monday, 25 January 2016 10:34

How to convert a 10 Digit Number (or Unix Timestamp) into a date in excell

Written by
Rate this item
(4 votes)

In this article, I reveal step by step instructions to convert a 10 digit number unix timestamp into a date in Excel. This is particularly useful to Joomla users as Joomla stores and exports date as Unix Timestamps.

Unix timestamp is defined as the total number of seconds since midnight (GMT time) on January 1, 1970 - also known as the Unix epoch.

  • Insert your ten digit unix timestamp number in the first cell of your Excel sheet

    inserting formula

  •  

     

  • Type the following formula in the second or third cell of your Exel sheet:

    =(((A2/60)/60)/24)+DATE(1970,1,1) 

    The formula converts the seconds to days and then add them to January 1, 1970

    inserting formula

  •  

     

  • Adjusting the formula for the GMT offset. 

    If you live in France, you need to adjust to offset by 1 (+1) which gives you the following formula 

    =(((A2/60)/60)/24)+DATE(1970,1,1)+(1/24) 

  •  

  • Right click on the formula cell and click on format cells

    formating excel cell into date

  • In the "number" tab, select Date

    formating excel cell into date 2

  •  

  • 10 Digit Unix Timestamp Converted

    timestamp converted

  •  

 

 

 

 

Read 6398 times Last modified on Tuesday, 26 January 2016 08:30
Fabien Bello

Fabien Bello has been working in web and app develoment since 1997. He has held numerous positions from game developer to head of creative and product development for major firms such as Xerox, Gala Coral Group. Fabien continues contributing to online community, owns and manages several websites as well as offering consultancy to all size businesses.

Latest from Fabien Bello

Related items