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)
    How to convert a 10 Digit Number (or Unix Timestamp) into a date in excell

    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 5728 times Last modified on Tuesday, 26 January 2016 08:30
    Published in Microsoft Excel
    Tagged under
    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

    More in this category: « How to change a text to lower case and replace space with a dash in Excel

    Leave a comment

    Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

    back to top

    Unix Timestamp Converter

    Converter

    Insert your 10 digit timestamp number into the relevant field and click on view results to covert it to a readable  date.