# Furlo's Playground

## Články

• RSS

## Prečítané diela

• RSS

## Hlášky

• RSS

# Converting between Unix Timestamp and Local Date in Google sheets

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.

## Little math behind

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.

## Converting (local) date to timestamp

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 GMT4 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!

## Converting timestamp to (local) 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 timezonemanipulation 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 GMT4 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! 