How to Convert a Timestamp to Milliseconds in Google Sheets

You can convert a timestamp to Unix time in milliseconds using simple formulas in Google Sheets—no scripts needed. This is useful for working with APIs, logs, or systems that require timestamps in Unix format.

Before diving into the formula, let’s quickly review what Unix time is.

Table of Contents

What Is Unix Epoch Time?

Unix-based systems measure time from a fixed starting point: January 1st, 1970, 00:00:00 UTC, also called the Unix Epoch.

Unix time in seconds is the number of seconds elapsed since this point.
Unix time in milliseconds multiplies that count by 1,000.

In this tutorial, you’ll learn how to convert a timestamp in Google Sheets to Unix time in milliseconds, with optional formulas for seconds and microseconds.

How to Set and Use Time Zones in Google Sheets

Before converting timestamps, make sure your Google Sheet is using the correct time zone.

How to Set Your Sheet’s Time Zone:

Go to File > Settings
Under the General tab, choose the correct Time zone
Click Save settings

Selecting the correct time zone in Google Sheets spreadsheet settings

Now, using =NOW() will return your local time accurately.

How to Convert Local Time to UTC in Google Sheets

Google Sheets stores timestamps in local time. To get the correct Unix time, you need to convert local time to UTC (GMT).

UTC Conversion Formula

If your time zone is UTC+5:30, subtract 5.5 hours:

=A1 - 5.5/24

If your time zone is UTC−7:00, add 7 hours:

=A1 + 7/24

Rule of thumb:

If your local time is UTC+, subtract the offset

If UTC−, add the offset

Step 3: Convert Timestamp to Unix Time in Milliseconds

Once your timestamp is in UTC, use this formula to convert it to milliseconds since Unix Epoch:

Formula:

=(A1 - [UTC Offset] - DATE(1970,1,1)) * 86400000

Example:

If A1 = 4/8/2017 11:30:00 and your time zone is UTC+5:30:

=(A1 - 5.5/24 - DATE(1970,1,1)) * 86400000

Result: 1501826400000 (Unix time in milliseconds)

Example of a Google Sheets formula converting a timestamp to milliseconds

Convert Current Timestamp to Milliseconds

Use the NOW() function instead of a cell reference:

=(NOW() - 5.5/24 - DATE(1970,1,1)) * 86400000

This gives you the current time in milliseconds, adjusted for your time zone.

Additional Variations

Unit	Formula
Seconds	`=(NOW() - 5.5/24 - DATE(1970,1,1)) * 86400`
Microseconds	`=(NOW() - 5.5/24 - DATE(1970,1,1)) * 86400000000`

Change the time zone offset (5.5/24) based on your own.