I have read multiple articles for this question and none of them answers my question properly: Convert Unix Epoch Time to Date in Google Sheets | https://webapps.stackexchange.com/questions/108115/how-to-convert-unix-timestamp-to-human-readable | https://code.luasoftware.com/tutorials/google-sheets/google-sheets-convert-unix-epoch-timestamp-to-date/
I have a date from Hubspot Sales for which I use a zap to connect to Google Sheets. From Hubspot I get a date 1574842022157 which I try to convert according to the main idea in those articles which say that an epoch date 1500598288 should be formatted with =1500598288/86400+date(1970,1,1) resulting in a date.
When I try that (after changing the updated syntax for the date function in my locale to be) =1500598288/86400+datum(1970;1;1) I get the result 2017-07-21, when using the example date of 1500598288.
When using the Hubspot date 1574842022157 the result is completely different. Just by doing the first division without adding the traditional date from 1970 I get the date of 51804-10-02. As I understand Google Sheets uses 30 November 1899 as its zero-date, so it makes no sense to add more than 70 years to my date by using the part +datum(1970;1;1) in my date which is already too far into the future.
The peculiar thing is that when I give my Hubspot date of 1574842022157 to the converter at https://www.epochconverter.com/ it returns the date Wednesday 27 November 2019 09:07:02.157 GMT+01:00, which is correct.
So what is that converter doing that all the helpful articles linked to at the top are missing? On the converter page https://www.epochconverter.com/ you are able to read that:
Human-readable time Seconds
1 hour 3600 seconds
1 day 86400 seconds
1 week 604800 seconds
1 month (30.44 days) 2629743 seconds
1 year (365.24 days) 31556926 seconds
A curiosity is that if I divide my date with the amount of seconds in a year in a Google Sheet cell I get closer to my date, but it's still not correct.
1574842022157 / 31556926 = 2036-08-17
So is it a case of just manually finding the respective offset for when dividing either with the seconds of a day alternatively with the seconds of a year respectively, or is there a smart mechanism at work here which is easier to make work for you?
Ok I think I found the answer here http://ramblings.mcpher.com/Home/excelquirks/gassnips/datetimeepoch
where the formula given everywhere else of numeric datetime / the seconds in a day = numeric datetime / 86400 = numeric datetime / (60*60*24) = numeric datetime / 60/60/24 before adding the epoch date of 1970-01-01 is incorrect when it comes to my problem.
Instead it should be replaced with numeric datetime /1000/60/60/24 and when you add the epoch date of 1970-01-01 to the result of that calculation you get the accurate date. So the formula that answers my problem is then
numeric datetime/1000/60/60/24+DATUM(1970;1;1)
In my Google Sheets cell the formula looks like =G3/1000/60/60/24+DATUM(1970;1;1) where G3 = 1574842022157.