Google sheets do not offer any straightforward way to convert from Unix Timestamp (that is, the number of seconds that passed since 1 January 1970) to local date and vice versa. If you are web admin, this is something you will sooner or later need when passing data between Google sheets and server SQL database.
Unix Timestamp is the number of seconds that passed since 1 January 1970, which marks the start of Unix epoch.
The “magic constant” in formulae below is 86400
, which is the number of seconds in single day (24*60*60).
The second one is DATE(1970,1,1)
, which generates the starting day of Unix epoch. This is the reference date which has the timestamp 0. All dates before this date will have negative timestamp, while dates after it a positive one.
First, the general formula is this:
timestamp = (localDateTime - unixEpochDate - timezoneShift)*daySeconds
In the case of 4 September 2020 9:54:25am GMT+2 (Slovakia during summer time), this translates into the following formula in Google sheets:
timestamp = ( localDateTime - unixEpochDate - timezoneShift ) * daySeconds ↓ ↓ ↓ ↓ timestamp = ( (DATE(2020,09,04)+time(09,54,25)) - DATE(1970,01,01) - time(02,00,00) ) * 86400
Note that you need to subtract a positive GMT shift (so GMT+05:30 becomes
‑ time(05,30,00)
) and, vice versa, add a negative timezone shift (so GMT‑4 becomes + time(04,00,00)
).
When you have a cell with date string which you need to convert into timestamp, you can reference the cell directly, so the final formula will be:
= ( <cellRef> - DATE(1970,01,01) - time(02,00,00) ) * 86400 = ( $J10 - DATE(1970,01,01) - time(02,00,00) ) * 86400
If you ever encounter Google Sheets refusing to accept and convert your date string with a weird
Error DATEVALUE parameter '04/09/2020' cannot be parsed to date/time
, try doing this:
Plain text
(in Format → Number →
menu)United Kingdom
(in File → Spreadsheet settings
menu)Date
This is an exact reverse of the previous procedure, so the general formula is this:
localDateTime = (timestamp / daySeconds) + unixEpochDate + timezoneShift
So in the case of 1599206065 timestamp in GMT+2 (Slovakia during summer time), this will become in Google sheets:
localDateTime = (timestamp / daySeconds) + unixEpochDate + timezoneShift ↓ ↓ ↓ ↓ localDateTime = (1599206065 / 86400) + DATE(1970,01,01) + time(02,00,00)
Note that the timezone‑manipulation in here is the opposite of the previous formula, so you need to add a positive GMT shift (so GMT+05:30 becomes
+ time(05,30,00)
) and, vice versa, subtract a negative timezone shift (so GMT‑4 becomes ‑ time(04,00,00)
).
Again, with cell reference containing the timestamp that needs to be converted, the final formula will be:
= (<cellRef> / 86400) + DATE(1970,01,01) + time(02,00,00) = ($J10 / 86400) + DATE(1970,01,01) + time(02,00,00)
And that's it!