Converting time stamps in excel to dates

Question

I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn't work. My file is 91,568 KB. If there is a simpler way to this that would be great. I'm open to ideas.

Thank you in advance :)

P.S. I don't know any programming languages

Answer 1

=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)

assuming A1 is the cell where your time stamp is located and dont forget to adjust to account for the time zone you are in (5 assuming you are on EST)

Answer 2

A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.

• From a timestamp in milliseconds (ex: 1488380243994)

  use this formula:

  =A1/1000/86400+25569
  

  with this formater:

  yyyy-mm-dd hh:mm:ss.000
  

• From a timestamp in seconds (ex: 1488380243)

  use this formula:

  =A1/86400+25569
  

  with this formater:

  yyyy-mm-dd hh:mm:ss
  

Where A1 is your column identifier. Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.

Answer 3

If your file is really big try to use following formula: =A1 / 86400 + 25569

A1 should be replaced to what your need. Should work faster than =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) cause of less number of calculations needed.

Answer 4

AD ticks to datetime format: =A1/864000000000 - 109205

Answer 5

This worked for me:

=(col_name]/60/60/24)+(col_name-1)

Answer 6

This DATE-thing won't work in all Excel-versions.

=CELL_ID/(60 * 60 * 24) + "1/1/1970"

is a save bet instead.
The quotes are necessary to prevent Excel from calculating the term.