How to Convert Unix Timestamp to Dates in Google Sheets & Airtable

In this quick how-to, we’ll explain what Unix timestamps are and, more importantly, how to automatically convert them into dates in Google Sheets, Excel and Airtable.

If you’re unfamiliar with Unix timestamps, strings of numbers like 1671408000 probably just look like computer gibberish. However, as intimidating as those numbers might appear, we promise you, you haven’t slipped into the matrix—not yet, at least.

You’ve come to the right place to learn all about Unix timestamps (and Epoch time) and how to convert them into date and time formats that don’t make your head spin.

At the end of this quick tutorial, we’ll show you some cool ways to use Unix timestamps in everyday workflows.

What is a Unix Timestamp?

Unix time is simply a measurement of the number of seconds that have elapsed since January 1, 1970. Seriously. That’s it. That’s what all the fuss is about.

Given that there are 24 hours in a day and that each hour consists of 60 minutes and that, in turn, each minute consists of 60 seconds, each day in Unix time has a value of 86,400 (i.e., 24 × 60 × 60).

To give you a better idea, here are a few examples of dates and times represented in Unix time:

January 1, 1970 12:00:00 AM —— 0

March 11, 1989 3:00:00 PM —— 605631600

July 16, 2006 10:00:00 AM —— 1153044000

October 5, 2022 7:41:00 PM —— 1633462860

As a fun exercise, click here to learn more about Unix time and check out the running Coordinated Universal Time (UTC) clock, converter and reference chart.

What is Epoch Time?

In your search to make sense of number strings like 788400000, you may have across the terms Epoch, Epoch time or Unix epoch. While computer programmers often use these terms interchangeably, it’s worth noting that Unix time and Epoch time are not necessarily the same thing.

In computing, an epoch refers to the date and time relative to which a computer’s internal clock and system calculates its timestamps. In other words, an epoch can be any date with time 00:00:00.

By that definition, the Unix epoch is, as we have recently discovered, January 1, 1970 00:00:00.

Because so many computing systems and programming languages use the Unix epoch, it is practically ubiquitous. However, other notable epochs include January 1, 1601 (Windows API) and January 1, 1900 (Network Time Protocol).

There’s also the Coffee Epoch (a.k.a., the How We Start Our Day Epoch).

How to Convert Unix Timestamps to Dates in Google Sheets

If you can multiply, divide and add, you can easily convert Unix time into a date format that doesn’t resemble coordinates on a global position system.

In the following walkthrough, we’re going to use Google Sheets but you can use the same formulas and formats in Excel.

Step 1: Set Up Your Google Sheet

How you configure your spreadsheet will depend on your own needs. For illustrative purposes, we’ve set up a three-column sheet.

We’ve labeled Column A Unix Time. This will be the column where we store our Unix timestamps. Given that Unix timestamps only exist in integers, you can format the cells accordingly. To do so, select Column A then navigate to Format > Number > Number and reduce the decimal places by two (2).

Similarly, for both Column B and Column C, set the format to Date time.

Step 2: Set a Control Cell

Unix time is calculated in reference to January 1, 1970. As such, you’ll need a “control cell” or a “reference cell” that is set to “0” in Unix time (a.k.a. January 1, 1970).

In our example, we’ve chosen to use B2 as our reference cell. In your reference cell, type in the following formula:

=date(1970,1,1)+time(0,0,0)

After you press Enter, your cell should return the results 1/11970 0:00:00.

Step 3: Convert Unix Timestamps to Dates

Unfortunately, there are no simple formulas for converting Unix time into money. But there is an easy formula for converting any Unix timestamp into a date.

To convert Unix time to a date, all you have to do is divide the Unix timestamp by 86,400 (recall that this is the number of seconds in a day or, equivalently, the conversion rate of days into Unix time). Next, you’ll add the quotient of Unix timestamp/86,400 to the reference cell value (i.e. “0”) and let Google Sheets do the rest.

The formula should look something like this:

=(A2/86400)+$B$2

where A2 is the Unix timestamp and B2 is the reference cell set to January 1, 1970.

Note: Adding “$” before the column and cell values locks that cell into the formula if you later choose to copy and paste or drag and copy the formula from that cell.

Next, let’s test out our Unix time converter with the examples from our previous section:

All done! It’s not exactly time travel but it is a pretty nifty time conversion trick.

Note: You can change the format of the cells in your results column (the column we’ve labeled Date and Time) by navigating to Format > Number > Custom date and time.

How to Convert Unix Timestamps to Dates in Airtable

If you’re using a database like Airtable, you can still convert Unix timestamps to dates. In fact, with AirTable’s processing power, it wouldn’t surprise us if you did, incidentally, discover the formula for time travel.

Step 1: Configure Your Base

Just as with Google Sheets and Excel, the exact configuration of your Airtable base will depend on your specific needs. Regardless, you’ll have to include a table with at least two columns:

Step 2: Edit Your Unit Time Field

Either create or edit a field with the following configuration:

This will be the field with your Unix timestamps.

Step 3: Edit Your Date Field

Edit or create a field with the following configuration:

Manually type (do not copy and paste) the following formula into the Formula field of your editor window:

DATEADD(‘1/1/1970’,{Unix Time},’seconds’)

Note: It’s important that you type the formula into the Formula field as Airtable will not recognize the text properly if you paste it. Also, you may need to edit the formula above depending on what name you’ve given to your Unix Time field. If you’ve chosen a name other than Unix Time, then replace {Unix Time} with {Your Field Name} and leave the rest of the formula unchanged.

Test out your Airtable time converter with the dates and times from our previous example.

Note: Airtable will calculate as you enter Unix timestamp values.

Save (Unix) Time with Uncanny Automator

Now that you’ve mastered the art of converting time, you can start saving it.

As the #1 WordPress automation plugin, Uncanny Automator can help you sync your Unix timestamp data between all of your favorite apps and plugins, including Google Sheets and Airtable. Or, frankly, any of Automator’s 100+ integrations!

Here are just a few of the ways that you might want to use Automator’s Unix timestamp tokens feature in your own workflows:

• Monitor key performance indicators (KPIs) such as site session durations, shopping cart persistence, etc.
• Use Automator’s calculations feature to add, subtract, divide or multiply Unix timestamps within an app or plugin to modify permissions and privileges.
• Compare timestamps across your various apps and plugins to find efficiencies.

With your new knowledge of Unix timestamps and the power of Automator at your fingertips, you may just become a master horologist!

How do you intend to use your Unix timestamps? What are some of the workflows that you would like to see? Let us know in the comments section below. Until then, do what the Unix clock does and keep ticking!