Menu
I have a sheet with a column of unix epoch times (in seconds): 1500598288
How can I convert these into normal dates?
464
Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.
Convert timestamp to date If you have a list of timestamp needed to convert to date, you can do as below steps: 1. In a blank cell next to your timestamp list and type this formula =(((A1/60)/60)/24)+DATE(1970,1,1), press Enter key, then drag the auto fill handle to a range you need.
You can take an epoch time divided by 86400 (seconds in a day) floored and add 719163 (the days up to the year 1970) to pass to it. Awesome, this is as manual as it gets.
The simplest way, not requiring any JS programming, would be through a formula, dividing by 86400 seconds per day and adding to January 1, 1970. For example the following gives 21 July 2017:
=1500598288/86400+date(1970,1,1) To convert a whole column of numbers, just use ARRAYFORMULA:
=arrayformula(A:A/86400+date(1970,1,1)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
