Time zone conversion in Google Sheets

Time zone conversion in Google Sheets

Google Sheets does not have a built in way of converting time zone data but by using the power of Moment.js and Google’s script editor we can add time zone functionality to any sheet.

First, we need to add the Moment.js code as a library that can be shared between different documents. This is the javascript library that adds date and time zone manipulation support. A good way to do this is to create it from Google Drive so it can be easily edited and shared with all your sheets.

In Google Drive, go to New > More > Connect more apps and choose Google Apps Script. Now, create a new Google Apps Script document. This will open the Google script editor. Call the project ‘Moment’ and create two files in the project called moment.js and moment-timezone.js using the moment and moment-timezone libraries. Make sure you choose one of the moment-timezone files with time zone data. You should end up with a project something like this:

To easily use this in multiple sheets we can publish it as a library. Go to File > Manage versions and save a new version. We are nearly finished here but before we move on go to File > Project properties and make a note of the project key, you will need this to refer to your library in your sheets.

In our test we are going to use our new library to convert times in a local time zone to UTC.

Create a new Google sheet and enter a date in A1 and a time zone in B1, like this:

 

Go to Tools > Script editor and create a project called Functions. In the script editor, go to Resources > Libraries and using the project key you made a note of before add your Moment library and select version 1. I prefer to use a shorter identifier like ‘m’. Click save and your library is now accessible to your sheet’s script. We can create a function to convert to UTC like this:

Save your project and you can now use this function in your sheets like this:

 



32 thoughts on “Time zone conversion in Google Sheets”

  • Thank you for this information! It was a life saver. I was using moment.js in my google sheets project but I was working with a client based in India. The spreadsheet settings were set for the right timezone but moment would still bring up the wrong timezone. So I searched and found your post here and it worked out great! Thanks again.

  • Hi David

    Thank you sooo much!! You have no idea of the countless hours I’ve been looking for this!!

    Cheers.

  • Hello David,

    First of all, thank you very much for this script, it’s going to be very useful for me. However, I have an error with my sheet, it tells me “TypeError: Cannot read property “version” from undefined. (line 27).” which corresponds to this part of the script: “var VERSION = “0.5.13”,”
    Is there something I’m missing?

    Thank you so much for your help,

    Elena

  • It sounds like the problem is this bit:

    Do you have both moment.js and moment-timezone.js (moment-timezone-with-data-2012-2022.js) in your script project (check out the screenshot in the post)?

     

  • Hi David – I’m having the same issue as the above commenter. Just confirmed that I’m using the right scripts.

  • I think I can reproduce this. It looks like the order of scripts in the script editor is important.

    Make sure you have the moment.js script above the moment-timezone.js script:

    If yours is the other way round just remove and re-add moment-timezone.js and it will place it underneath. Publish a new version of your library and update the version used in your sheet.

  • Doesn’t seem to be the root of the issue for me. Moment.js is at the top: https://screencast.com/t/1L4p1Kgoi

    This thread points out that you need to have data accompanying the scripts…but I’m using moment-timezone (with data) version.

    Thread: https://github.com/moment/moment-timezone/issues/465

  • Hi David. Thank you for the post. The code works. However if I want the conversion to be to EET Time, let’s say UTC+02:00, what do I need to change in the function?

  • If you want to customise the target time zone you can add this function to your script:

    Then call it from your sheet by passing in the source and target time zones like this:

    I have updated the example sheet with this new function.

    This list of time zones in the tz database is very useful.

  • How can I simply retrieve the browser timezone directly using something like:

    moment.tz.guess();

    This way, if you are a ‘gamer’ and you have guild members located in various locations around the world but you want a Google Sheet to display ‘their’ local time when planning events, we don’t have to create mass amounts of rows simply so people can copy/paste their own location in order to get their local time for scheduled events.

  • Yep you can do that!

    I’ve added the function to the example sheet.

  • David, how do I do this timezone conversion programmatically? (without the use of the spreadsheet)

  • Derek, Google Scripts are Javascript so as long as you have Moment.js loaded you can use the same function in your regular Javascript projects.

  • what happened to a simple native Google Sheets method: for example: B1=A1-time(8,0,0) ?
    Am I missing something here?

  • Nina, time zones are more than just offsets. For example, they encapsulate rules about daylight saving time so it’s not as simple as just adding or subtracting an offset to a date.

  • Hi, David! Thank you for this tutorial. For some reason the script won’t run if I use my own library… I used the key of the library from the sample you provided and it worked perfectly! Thanks!

  • Hi again, David. I have a small problem… that Date and Time input my bot pushes to the Google Sheet is formatted like this:

    3/5/19 1:16 AM

    which I figure out is:

    DD-MMM-YY, hh:mm A

    How can I modify this script to make it run with 2 digit years? Thank you so much!

  • (since I can’t edit my comment I resposted it correctly. I’m sorry for spamming your blog…)

    Hi again, David. I have a small problem… that Date and Time input my bot pushes to the Google Sheet is formatted like this:

    3/5/19, 1:16 AM

    which I figure out is:

    DD/MMM/YY, hh:mm A

    How can I modify this script to make it run with 2 digit years? Thank you so much!

  • Julio: here is a function that will take a date format:

    You can then call it like this for your example:

    I have added a new sheet to the demo worksheet that demonstrates it’s use.

  • Hi David,

    Great work.
    I seem to be having a problem with the function for some reason it does not allow me to reference a cell with a NOW function for a live update on the time zone, how would I reference that I get two different errors depending on my format.

    D1 Containing the NOW function – returns that I can not reference a now functiion
    =toUtcFormat(D1, "D/M/YYYY, HH:mm A", C1)

    returns the error = ReferenceError: “m” is not defined. (line 2).
    ==toUtcFormat(NOW=( "YYYY-MM-DD HH:mm:ss"), C1)

    Looking at all the information above and even using the code for the project itself never worked, kinda stuck now and just back to the basic. Project Moment the script file moment.js.gs is above the file moment-timezone.js.gs, the script files used are the ones in your example to Josh.

    function toUtc(dateTime, format, timeZone) {
    var from = m.moment.tz(dateTime, format, timeZone);
    return from.tz("Etc/UTC").format('YYYY-MM-DD HH:mm:ss');
    }

    Bamboozled

    Thanks

  • Sorry may I add that i already know about the Function toUtc and toUtcFormat are different in my example above, I tried both the original and the latest response of the function script and know how to trigger the target script so to speak so I did also used the below and also used only 1 = reference!

    =toUtc(D1, "D/M/YYYY, HH:mm A", C1)
    and
    =toUtcFormat(NOW=( "YYYY-MM-DD HH:mm:ss"), C1)

  • Hi Dan,
    Good job, however when I was trying to replicate your project I’ve encountered a difficulty at this step:
    “Call the project ‘Moment’ and create two files in the project called moment.js and moment-timezone.js using the moment and moment-timezone libraries. ” could you let us know what exactly needs to be in the 2 files.
    Thanks,

  • HI thanks for this, but an error keep popping up when I run it.
    “ReferenceError: “m” is not defined. (line 2, file “Code”)”

  • ABIGAIL: The “m” is the identifier defined for the library (Resources > Libraries…). You probably have it called something like “Moment”.

  • This is great. Thanks. I have an additional twist to add for those interested. I wanted to further address two issues. First, I wanted to be able to input and output actual date values rather than text. So, for the formula, I wanted to just reference a cell instead of having to keep copying a formula with that big TEXT() function in it. When you start doing that, though, Google Sheets & scripts starts to take their own time zone setting into account. In other words, Sheets send the date value with its sheet TZ setting taken into account. Here is what I came up with to negate that but still let you use date values simply:


    function toZone(dateTimeD, fromTimeZone, toTimeZone) {
    var scriptTZ = Session.getScriptTimeZone();
    var sheetTZ = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
    var dateTimeT = m.moment.tz(dateTimeD, sheetTZ).format('YYYY-MM-DD HH:mm:ss');
    var stepOne = m.moment.tz(dateTimeT, fromTimeZone);
    var stepTwo = stepOne.tz(toTimeZone);
    var stepTwoT = stepTwo.format('YYYY-MM-DD HH:mm:ss');
    var stepThree = m.moment.tz(stepTwoT, sheetTZ);
    var stepFour = stepThree.tz(scriptTZ);
    var stepFourY = Number(stepFour.format('YYYY'));
    var stepFourM = Number(stepFour.format('MM'))-1;
    var stepFourD = Number(stepFour.format('DD'));
    var stepFourH = Number(stepFour.format('HH'));
    var stepFourm = Number(stepFour.format('mm'));
    var stepFours = Number(stepFour.format('ss'));
    var stepFourAll = new Date(stepFourY,stepFourM,stepFourD,stepFourH,stepFourm,stepFours);
    return stepFourAll;
    }

    I honestly don’t really know anything about Java Script, so this was all trial and error. It’s entirely likely that there is a simpler way to achieve a lot of this.

  • I have the same error as Abigail. – ReferenceError: “m” is not defined. (line 2). – I’m not sure how to change it. I see your response but don’t know how to fix it.

Leave a Reply

Your email address will not be published. Required fields are marked *

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