error when change unix time into date-time via excel

Asked Apr 01 '20 at 19:37

Active Apr 01 '20 at 19:37

Viewed 201 times

i want to convert a timestamp column into time. Imagine that this column is extended in 30000 rows

so when i put this formula =ROUND(DATEVALUE("1/1/1970")+A1/(60*60*24),0)

i get this:

VALUE!

Why does this happen

I am using Microsoft office professional plus 2016

the time zone is UTC +2:00

Can you help me with the formula here?

asked Apr 01 '20 at 19:37

redplanet

What is the exact value in A1? is it `UTC +2:00`? – Scott Craner Apr 01 '20 at 19:41
The formula works with unix date/time so there must be something with the data in A1 that is causing the issue. – Scott Craner Apr 01 '20 at 19:56
is depicted like this 1.59E+12 – redplanet Apr 01 '20 at 20:15
is a timestamp from an accelerometer sensor 1585740000000 if i format the above shell – redplanet Apr 01 '20 at 20:23
1

You have miliseconds in that number change the formula to: `ROUND(DATEVALUE("1/1/1970")+A1/(60*60*24*1000),0)` – Scott Craner Apr 01 '20 at 20:36
thank you for your answer. i get the same error. I am using this formula in cell C2. A1, B1, C1 have the label of the dataframe – redplanet Apr 01 '20 at 20:42
So i change the A1 TO C2 and i get back: there are one or more circular elements where a formula refers to its own cell directly or inderectly. This may cause them to calculate incorrectly – redplanet Apr 01 '20 at 20:51
If I put `1585740000000` in A1 and use the formula I gave you, it returns today's date. – Scott Craner Apr 01 '20 at 20:52
You cannot use a formula on itself. – Scott Craner Apr 01 '20 at 20:53
Thank you @scott craner – redplanet Apr 01 '20 at 20:58
this formula works for A1 but how to adjust automatically my other observation which goes down to 30000 rows? @ScottCraner – redplanet Apr 02 '20 at 05:26
you will drag the formula down. – Scott Craner Apr 02 '20 at 13:03