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:

1. Change the format of all your dates to Plain text (in Format → Number → menu)
2. Change the locale of your sheet into United Kingdom (in File → Spreadsheet settings menu)
3. Change the format of all your dates back to Date
4. Formula should work now!

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!

• Facebook
• Twitter
• LinkedIn
• Pinterest
• Reddit
• Email

• EpochConverter.com, a splendid online tool for conversions between timestamp and time, both GMT and local
• Convert Unix Timestamp to Local DateTime and Vice Versa in Google Sheets, the original article from which I have learned the math behind.
• Sheets not recognizing dates: Error DATEVALUE parameter '14/2/2020' cannot be parsed to date/time, a helpful Google support thread when Google sheets reject to parse your date strings