How to Convert a Timestamp to Milliseconds in Google Sheets

We can convert a timestamp to Unix time (in milliseconds) in Google Sheets. For that, we can use a formula that only involves date functions and arithmetic operators.

Before attempting to convert a timestamp to milliseconds, you must know about Unix Epoch.

Unix, as well as Unix-like operating systems, measure system time from Jan 1st; 1970 00:00:00 UTC. The term Unix Epoch is used for describing this starting point of time.

Unix time is the number of seconds that have elapsed since the above Unix Epoch. In this Google Sheets tutorial, we are going to convert a timestamp or date to Unix time in milliseconds.

That means the number of milliseconds elapsed since Unix Epoch, i.e. from 00:00:00 Thursday, 1 January 1970.

Before writing the formula, you should know how to convert a local time to GMT/UTC in Google Sheets. That is important to write the converter code/formula.

Convert Local Time to GMT/UTC in Google Sheets

Enter the =now() function in cell A1 to get the local time. If this formula is not returning the correct local time in your Sheet, you need to follow the below steps.

Steps:

  1. Click the menu FILE and open “Spreadsheet settings”.
  2. Under the “General” tab see the selected time zone. Correct that to your time zone.
Choosing correct time zone in Docs Sheets

Now you have set the correct local time in Google Sheets. In the next step, you will get the formula to convert a timestamp in local time to GMT/UTC time.

See the above screenshot. If your local time is GMT+5:30:00 (which is correct in my case), use the formula below.

=A1-5.5/24

If your local time is GMT-7:00:00, the formula to convert local time to GMT/UTC will be as follows.

=A1+7/24
Converting local time to GMT/UTC

Note: If your time zone is GMT+, in the formula use the minus sign. If it is GMT- then use the plus sign.

Google Sheets Formula to Convert Timestamps to Milliseconds

Formula to Convert Timestamp aka Date-Time to Unix Time in Milliseconds:

Sheets formula to convert timestamp to milliseconds

I have the following date-time in cell A1.

4/8/2017 11:30:00

The above formula will convert this date-time to 1501826400000 milliseconds.

To convert the current date-time to milliseconds in Google Sheets, use the below formula.

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

In this formula, I have replaced A1 with the now() function which returns the current date and time.

Additional Tips:

  1. Use =(now()-5.5/24-DATE(1970,1,1))*864000 formula to convert to seconds.
  2. And this for microseconds – =(now()-5.5/24-DATE(1970,1,1))*86400000000.

Related:- Convert Unix Timestamp to Local DateTime and Vice Versa in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.