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

User · Answer

Be aware of number of digits in epoch time. Usually they are ten (1534936923) ,then use:

=(A1 / 86400) + 25569

For thirteen digits (1534936923000) of epoch time adjust the formula:

=(LEFT(A1,LEN(A1)-3) / 86400) + 25569

to avoid

###################################

Dates or times that are negative or too large display as ######

See more on https://www.epochconverter.com/

User · Answer

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

User · Answer

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

User · Answer

If you get a Error 509 in Libre office you may replace   by   in the DATE   function      COLUMN ID HERE 60  60  24  DATE 1970 1 1

User · Answer

The answer of  NeplatnyUdaj is right but consider that Excel want the function name in the set language  in my case German  Then you need to use  DATUM  instead of  DATE        COLUMN ID HERE 60  60  24  DATUM 1970 1 1

User · Answer

AD ticks to datetime format   A1 864000000000 - 109205

User · Answer

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

User · Answer

below formula worked form me in MS EXEL   TEXT CELL VALUE 24 60 60 1000   25569  YYYY-MM-DD HH MM     CELL VALUE is timestamp in milliseconds  here is explanation for text function

User · Answer

Use this simple formula  It works   Suppose time stamp in A2    DATE YEAR A2  MONTH A2  DAY A2

User · Answer

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

User · Answer

Use this formula and set formatting to the desired time format       COLUMN ID HERE 60  60  24  DATE 1970 1 1    Source  http   www bajb net 2010 05 excel-timestamp-to-date  Tested in libreoffice

User · Answer

This worked for me     col name  60 60 24   col name-1

User · Answer

i got result from this in LibreOffice Calc      DATE 1970 1 1  Column id here 60 60 24

[excel-formula] Converting time stamps in excel to dates

Examples related to excel-formula