This guide will explain how to use EPOCHTODATEfunction in Google Sheets.
When we need to convert Unix epoch timestamps to UTC, we can easily do this using the EPOCHTODATE function in Google Sheets.
The rules for using the EPOCHTODATE function in Google Sheets are the following:
- The
EPOCHTODATEfunction is used to convert a Unix epoch timestamp to a DateTime in Universal Time Coordinated (UTC). - The default unit of time is seconds.
- The fractional amounts of milliseconds are shortened.
- The function will return the UTC and not the local time zone of our spreadsheet.
- The function does not accept negative timestamps.
Google Sheets has several built-in functions that deal with date-related situations or conversions. Some examples of date-related functions in Google Sheets are the DATEDIF function, the DAYS function, and the DATEVALUE function.
The EPOCHTODATE function specifically converts Unix epoch timestamps to UTC. The Unix epoch refers to the number of seconds that have passed since January 1, 1970, not counting leap seconds.
In this guide, we will provide a step-by-step tutorial on how to use the EPOCHTODATE function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the EPOCHTODATE Function
The syntax or the way we write the EPOCHTODATE function is as follows:
=EPOCHTODATE(timestamp,[unit])
- = the equal sign is how we begin any function in Google Sheets.
- EPOCHTODATE() refers to our
EPOCHTODATEfunction. This function is used to convert a Unix epoch timestamp in seconds, milliseconds, or microseconds to a DateTime in Universal Time Coordinated (UTC). - timestamp is a required argument. This refers to the Unix epoch timestamp in seconds, milliseconds, or microseconds we want to convert.
- unit is an optional argument. This is the unit of time in which the timestamp is expressed. By default, the function expressed the timestamp in seconds.
EPOCHTODATE Function Unit Options
The EPOCHTODATE function has an optional unit argument which allows us to decide what unit of time we want to express the timestamp in.
The table below shows the different options we can choose.
| Unit Option | Description |
| 1 | This is the default option. The function will express the timestamp in seconds. |
| 2 | The function will express the timestamp in milliseconds. |
| 3 | The function will express the timestamp in microseconds. |
A Real Example of Using EPOCHTODATE Function in Google Sheets
Let’s say we have a data set containing several Unix epoch timestamps. Our initial data set would look like this:
In the spreadsheet above, we can see the Unix epoch timestamps in different units of time, such as seconds, milliseconds, and microseconds. Our goal is to convert these timestamps into a DateTime in Universal Time Coordinated.
We must remember that the Unix epoch time is the seconds elapsed since the 1st of January 1970. Thus, we cannot convert a date that happens before the 1st of January 1970.
We can use the formula below to convert Unix epoch timestamps:
=EPOCHTODATE(A2,1)
The first part of the formula is simply referring to the cell containing the Unix epoch timestamp we want to convert. Then, we need to input the appropriate unit argument.
In the first row of the data set, the Unix epoch timestamp is in seconds. Hence, we will input 1, which refers to the time unit seconds as our unit argument.
The next row of the data set is a Unix epoch timestamp in milliseconds. Thus, the formula we would be using is=EPOCHTODATE(A3,2)
This time, the unit argument is 2, which is the unit option for the time unit milliseconds.
Lastly, the last row is a Unix epoch timestamp in microseconds. Our formula would be =EPOCHTODATE(A4,3)
The unit argument is 3, which is the unit option for the time unit microseconds.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the EPOCHTODATE function in Google Sheets.
How to Use EPOCHTODATE Function in Google Sheets
1. First, we will create a new column to input the converted timestamps.
2. In the first row of the new column, we will type in an equal sign and the EPOCHTODATE function. This will make our formula “=EPOCHTODATE(”.
3. We will select the cell containing the timestamp we want to convert. In this case, our formula will be “=EPOCHTODATE(A2”.
4. Since the timestamp is in seconds, we will input the unit option 1. Our final formula will be “=EPOCHTODATE(A2,1)”.
5. We will press the Enter key to return the result.
6. Next, we will convert the Unix epoch timestamp that is in milliseconds. To do this, we will again type in the EPOCHTODATE function and select the cell that has the timestamp. Our formula will be “=EPOCHTODATE(A3”.
7. This time we will use the unit option for milliseconds which is 2. Thus, our formula will be “=EPOCHTODATE(A3,2)”.
8. We will press the Enter key to return the result.
9. Then, we can move on to the last timestamp, which is in microseconds. Similarly, we will type in the function and select the cell containing the timestamp. Our formula will be “=EPOCHTODATE(A4”.
10. We will utilize unit option 3, which is for microseconds. Our final formula would be “=EPOCHTODATE(A4,3)”.
11. Lastly, we will press the Enter key to return the results.
And tada! We have successfully used the EPOCHTODATE function in Google Sheets.
You can apply this guide whenever you need to convert Unix epoch timestamps in seconds, milliseconds, and microseconds into Universal Time Coordinated. You can now use the EPOCHTODATE function and the various other Google Sheets formulas available to create great worksheets that work for you.
FAQs:
1. Will the converted timestamp be in my local time zone?
No. The EPOCHTODATE function will always convert to the Universal Time Coordinated (UTC). If you want to convert it to your local timezone, you can add your GMT offset from the returned date using the + symbol.
Let’s say your timezone GMT is +8 hours. You can simply add this to your formula. Thus, your final formula would be =EPOCHTODATE(B2,1)+time(8,00,0).
2. What is the use of the Unix epoch timestamp?
The Unix epoch time is a timestamp represented by the number of seconds passed since January 1, 1970. This specific timestamp can be used as an integer, making it easier to compute and use across different systems.
That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
