When you have a Unix epoch timestamp in seconds, milliseconds, or microseconds, you can convert it to a DateTime using the EPOCHTODATE function in Google Sheets.
The converted DateTime will be in UTC, but you can get the local time by adjusting for your timezone offset.
Many of you are probably here to learn how to use this function for future reference. If that’s the case, you might not have a Unix epoch timestamp (also known as Unix time, POSIX time, or Unix timestamp) available for testing.
So, we’ll start by converting a given DateTime to Unix epoch timestamps in seconds, milliseconds, and microseconds. Then, we’ll use the EPOCHTODATE function to convert them back to our local DateTime. Sounds interesting, right?
Let’s first understand the function and its arguments.
Syntax and Arguments
Syntax of the EPOCHTODATE Function in Google Sheets:
EPOCHTODATE(timestamp, [unit])Arguments:
- timestamp: A Unix epoch timestamp.
- unit: The unit of time (seconds, milliseconds, or microseconds) in which the timestamp is expressed. You can leave this argument blank, or input:
- 1 (default) for seconds,
- 2 for milliseconds,
- 3 for microseconds.
The function shortens fractional amounts of milliseconds. Additionally, negative timestamps are not accepted.
How to Use the EPOCHTODATE Function in Google Sheets
As mentioned, we will first convert a local timestamp to Unix epoch timestamps and then use the EPOCHTODATE function to convert them back. Here’s how to do that.
Prerequisites
Assuming the DateTime “2023-02-14 10:10:15” (in yyyy-mm-dd hh:mm:ss format) is in cell C3. This is the ISO 8601 format, a standard way to display dates that doesn’t affect the local settings of the sheet. Alternatively, you can use 14/02/2023 10:10:15 or 02/14/2023 10:10:15, depending on your locale.
Note: Unix Time is the number of seconds since 1st Jan 1970 (the Unix epoch). So, use a date after 01/01/1970 for testing.
Also, my timezone (IST) is 5 hours and 30 minutes ahead of GMT, so I’ll subtract that from the timestamp using -TIME(5, 30, 0) in the formulas below.
You should adjust this offset according to your timezone (use - if your timezone is ahead of GMT, and + if it is behind).
Conversion Formulas
Here are the formulas to convert the value in C3 to Unix epoch timestamps.
D3 (Unix Epoch Timestamp in Seconds):
=(C3 - DATE(1970, 1, 1) - TIME(5, 30, 0)) * 86400D4 (Unix Epoch Timestamp in Milliseconds):
=(C3 - DATE(1970, 1, 1) - TIME(5, 30, 0)) * 86400000D5 (Unix Epoch Timestamp in Microseconds):
=(C3 - DATE(1970, 1, 1) - TIME(5, 30, 0)) * 86400000000
Now, let’s use the EPOCHTODATE function to convert these Unix epoch timestamps back to DateTime in UTC.
Using the EPOCHTODATE Function to Convert to UTC
We already have the Unix epoch timestamps in seconds, milliseconds, and microseconds in the range D3:D5.
In F3:F5, we will use the EPOCHTODATE function to convert them into UTC.
F3 Formula (Convert Seconds):
=EPOCHTODATE(D3, 1)F4 Formula (Convert Milliseconds):
=EPOCHTODATE(D4, 2)F5 Formula (Convert Microseconds):
=EPOCHTODATE(D5, 3)Adjusting for Local Time
As you can see in the above examples, the results don’t match the value in C3 because the EPOCHTODATE function returns the DateTime in UTC, while the value in C3 is in local time.
You can add or subtract your GMT offset from the result to convert the returned DateTime to your local time.
In my case (IST is 5 hours and 30 minutes ahead of GMT), the formulas would look like this:
F3 (Local Time in Seconds):
=EPOCHTODATE(D3, 1) + TIME(5, 30, 0)F4 (Local Time in Milliseconds):
=EPOCHTODATE(D4, 2) + TIME(5, 30, 0)F5 (Local Time in Microseconds):
=EPOCHTODATE(D5, 3) + TIME(5, 30, 0)
That’s all about how to use the EPOCHTODATE function in Google Sheets to convert Unix timestamps to DateTime and adjust for local time.
