How to Use EPOCHTODATE Function in Google Sheets

This guide will explain how to use the EPOCHTODATE function in Google Sheets to convert a Unix timestamp into a datetime value.

While computers are robust enough to handle datetime values from different timezones, there is still some ambiguity when using the typical date time system. To counter this ambiguity, some applications or datasets use a Unix timestamp to represent a specific moment in time. 

The Unix timestamp, also known as epoch time, allows computers to store dates as an integer. Epoch time is measured by the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC (Coordinated Universal Time)

For example, we can use the Unix timestamp 946684800 to refer to the first second of the date, January 1st, 2000 GMT.

If you encounter these Unix timestamps in your data, Google Sheets includes a built-in EPOCHTODATE function to help convert these timestamps to the more familiar date values.

In this guide, we will provide a step-by-step tutorial on how to use the EPOCHTODATE function. We will also cover how to handle epoch time set in different units such as milliseconds and microseconds.

Let’s dive right in!

The Anatomy of the EPOCHTODATE Function

The syntax of the EPOCHTODATE function is as follows:

=EPOCHTODATE(timestamp, [unit])

Let’s look at each argument to understand how to use the EPOCHTODATE function.

• = the equal sign is how we start any function in Google Sheets.
• EPOCHTODATE() refers to our EPOCHTODATE function. This function accepts an integer value representing the epoch timestamp and returns the corresponding datetime in UTC.
• timestamp refers to the Unix epoch timestamp. This value may be in seconds, milliseconds, or microseconds.
• The [unit] parameter is an optional argument that we can use to indicate the time unit of our epoch timestamp. A value of 1 indicates a time unit in seconds. A value of 2 indicates a time unit in milliseconds. Lastly, a value of 3 indicates a time unit in microseconds.
• When no unit argument is provided, the EPOCHTODATE function assumes that the timestamp uses seconds as a unit.
• Do note that the result of the function is in UTC rather than the user’s local time zone.

A Real Example of Using the EPOCHTODATE Function in Google Sheets

Let’s explore a simple example where we’ll need to use the EPOCHTODATE in Google Sheets.

In the table below, we have a column of integer values representing different dates. We want to convert these integers into the date time values they represent.

We’ll use the EPOCHTODATE function in column B to find the corresponding UTC date-time value.

We can use the following formula to convert the epoch time to a human-readable date and time:

=EPOCHTIME(A2)

In the example below, we have a similar case where we are given a Unix timestamp. The main difference is that the timestamp uses milliseconds as a unit of time.

We’ll add 2 as a second parameter to indicate we want to perform the conversion using milliseconds as a unit.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

Use our sample spreadsheet to see how we used the EPOCHTODATE function.

If you’re ready to try using the EPOCHTODATE function yourself, head over to the next section to read our step-by-step breakdown on how to do it!

How to Use the EPOCHTODATE Function in Google Sheets

This section will guide you through each step needed to use the EPOCHTODATE function in your Google Sheets spreadsheets.

Follow these steps to start converting Unix timestamps to datetime values:

1. First, select an empty cell where you want to output the datetime value.
   In the example above, we’ll start by converting the epoch time in cell A2 to a datetime value in cell B2.
   
2. Type “=EPOCHTODATE(“ to start the function. Type the cell reference that contains the value you want to convert.
   
3. Hit the Enter key to evaluate the EPOCHTODATE function.
   In the example above, we discover that the timestamp 1522810907 is equivalent to the datetime April 4, 2018, 3:01:47 (UTC).
   
4. We can now use the AutoFill tool to convert the remaining Unix timestamps.
   
5. If your timestamps use milliseconds or microseconds, we must specify the unit in our formula. We can provide a value of 2 in our second argument to specify that our epoch time uses milliseconds.
   

Frequently Asked Questions (FAQ)

Here are some frequently asked questions about this topic:

1. How does the EPOCHTODATE function compute the datetime value?
   According to Google’s documentation, the EPOCHTODATE result divides the provided timestamp (converted to milliseconds) by the number of milliseconds in a day. Afterward, the number 25,568 is added to the result.

This tutorial should cover everything you need to know about the EPOCHTODATE function in Google Sheets.

We’ve explained how to use the EPOCHTODATE function to convert Unix timestamps to datetime values. We’ve also covered how to handle cases where the timestamp uses milliseconds and microseconds.

The EPOCHTODATE is just one of many built-in functions to handle dates in Google Sheets. For example, we can use the DATEVALUE function to return the number that represents a given date in Google Sheets. You can read our step-by-step guide to learn more about what the DATEVALUE function can do.

You may also check our guide on how to convert dates to fiscal quarters in Google Sheets to help set up financial reports.

That’s all for this guide! If you’re still looking to learn more about Google Sheets, be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

Subscribe