Converting time stamps in excel to dates
Excel FormulaExcel Formula Problem Overview
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
Excel Formula Solutions
Solution 1 - Excel Formula
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
Solution 2 - Excel Formula
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.
Solution 3 - Excel Formula
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)
Solution 4 - Excel Formula
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.
Solution 5 - Excel Formula
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.
Solution 6 - Excel Formula
=(((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)
Solution 7 - Excel Formula
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.
Solution 8 - Excel Formula
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/
Solution 9 - Excel Formula
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)
Solution 10 - Excel Formula
AD ticks to datetime format: =A1/864000000000 - 109205
Solution 11 - Excel Formula
i got result from this in LibreOffice Calc :
=DATE(1970,1,1)+Column_id_here/60/60/24
Solution 12 - Excel Formula
This worked for me:
=(col_name]/60/60/24)+(col_name-1)
Solution 13 - Excel Formula
Use this simple formula. It works.
Suppose time stamp in A2
:
=DATE(YEAR(A2),MONTH(A2),DAY(A2))