0

I am trying to convert a Unix Timestamp to CET I used the formula I found in this site, asked by another user

=(A1/86400)+DATE(1970;1;1) - since I live in German',' were replaced in formula to ';'

but it does not work for me, the result I get is #############################

for example timestamp 1629294665262271969 should give Wed Aug 18 2021 14:51:05 but I get 'dates and time that are negative or too large are shown as ######################'

I thought that it might be because it could be in milliseconds so I divided by another 1000 but the result is the same

any advice will be welcomed, Much appreciated,

hagay isaak
  • 1
  • 1
    If you format the value as `General` and examine the results, you can determine that you need to divide the Unix time by `10^9` instead of by `10^3`. And also, don't forget to add the hour for `GMT=>CET` – Ron Rosenfeld Aug 19 '21 at 12:35

1 Answers1

0

It's not in miliseconds all in seconds. Excel dating system starts from 01.01.1900 but Epoch timing starts from 01.01.1970 so we need to convert two dates to same value that's the reason why we add or remove =DATE(1970;1;1)

When you look at a date in excel by using =VALUE(A1) formula you will see some number. These numbers shows you how many days past from 01.01.1900 to given date. So you multiply the value with 86400 to convert day values to seconds. That is the idea how it works.

I wrote the formula you need but you also need to clear current format of cells if you see something like ########### it's probably there was a date formatted value before you write the formula.

Formula examples below.

Date to Epoch time

A1 is 19.08.2021. Formula on B1 is =VALUE(A1-DATE(1970;1;1))*86400. Result: 1629331200

Epoch to Date time

A1 is 1629331200. Formula on B1 is =(A1/86400)+DATE(1970;1;1). Result: 19.08.2021

X999
  • 450
  • 3
  • 15