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 7321 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

    4 comments

    • Comment Link Eleojipsy Monday, 04 May 2020 14:37 posted by Eleojipsy

      isotretinoin discount fedex shipping Glasgow https://abuycialisb.com/# - Buy Cialis Where Is The Best Place To Buy Viagara Buy Cialis Cytotec Grossesse

    • Comment Link bad credit auto sales san antonio Tuesday, 28 April 2020 14:03 posted by bad credit auto sales san antonio

      Your regular monthly settlement would certainly be $335.

    • Comment Link film Tuesday, 14 April 2020 06:30 posted by film

      A fascinating discussion is worth comment.
      I think that you should publish more on this subject matter, it may not be a taboo matter but generally people
      don't speak about these issues. To the next! All the best!!

    • Comment Link JamAcoumma Saturday, 21 March 2020 03:36 posted by JamAcoumma

      Cialis Generico Preco https://apcialisle.com/# - cialis generic release date Purchase Amoxicillin Online buy cialis online canada Furosemide For Sale

    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.