Search code examples
excelexcel-2007

Serial number to timestamp in excel

I have a numbers like 20160715082219000000000 and i want to convert into timestamp like 2016-07-15 08:22:19 and delete that remaining 0's.Please suggest how to do this conversion in excel?

Solution

  • Use the following formula for the number in cell A1

    =DATE(LEFT(A1*10^-9&"",4),MID(A1*10^-9&"",5,2),MID(A1*10^-9&"",7,2))+TIME(MID(A1*10^-9&"",9,2),MID(A1*10^-9&"",11,2),MID(A1*10^-9&"",13,2))

    This will output a number that represents a date and time, so 20160715082219 is represented by 42566.34883. Apply custom formatting to this cell (right click>format cells>custom) and type the following string in to the Type: field

    yyyy-mm-dd hh:mm:ss

    And you will get the result:

    2016-07-15 08:22:19

    As required