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:
function toUtc(dateTime, timeZone) {
var from = m.moment.tz(dateTime, timeZone);
return from.tz("Etc/UTC").format('YYYY-MM-DD HH:mm:ss');
}
Save your project and you can now use this function in your sheets like this:
=toUtc(TEXT(A1, "YYYY-MM-DD HH:mm:ss"), B1)
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:
momentVersion = moment.version.split('.')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
Strange. The only way I was able to reproduce this error was if I removed the moment.js code. Maybe try recreating everything, making sure all the scripts are saved and published correctly.
Here is an example sheet which uses this script library.
Super helpful, David. Thanks for taking the time to follow up with the example sheets.
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:
function toZone(dateTime, fromTimeZone, toTimeZone) { var from = m.moment.tz(dateTime, fromTimeZone); return from.tz(toTimeZone).format('YYYY-MM-DD HH:mm:ss'); }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!
function toBrowserZone(dateTime, fromTimeZone) { var from = m.moment.tz(dateTime, fromTimeZone); var toTimeZone = m.moment.tz.guess(); return from.tz(toTimeZone).format('YYYY-MM-DD HH:mm:ss'); }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.
Just Thank you!
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:
function toUtcFormat(dateTime, format, timeZone) { var from = m.moment.tz(dateTime, format, timeZone); return from.tz("Etc/UTC").format('YYYY-MM-DD HH:mm:ss'); }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)
POKI: Unfortunately you cannot use NOW(), RAND(), or RANDBETWEEN() as input to custom functions in Google Sheets.
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,
BOGDAN: You just need to paste the contents of the moment.js and moment-timezone.js files that you can download from https://momentjs.com.
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.
Thank you so much!
Hi David,
Hope you still visit this page! I have a list of timezones in a Google Sheet with a column next to it to display the current time in those timezones. Would this be something I could display using code in this tutorial? If so, how would I do that? 🙂
Thanks.
DAN: Nice idea but unfortunately you cannot get the current time by using NOW() as input to custom functions in Google Sheets. You could certainly have a list of time zones but the input time to the function can’t be dynamic.
Hi David,
Combining a few earlier questions:
We have a sheet to sign up for work sessions. Currently I have a column for each timezone, and it can overwhelm people.
So there are 24 rows, timezone columns, and then date columns, and we put our name into a blank space that matches the date and time that we are free.
I don’t need a column to show their current time, but is there a way for the document to know where it is being accessed, and convert the times from the first row into their local time?
That way I could have 2 columns instead of 12?
Thanks!
Thank you very much! I really needed something like this and I managed to make it work.
If someone else is having trouble to make a function work, try replacing the commas to semi-colons.
From:
toZone(TEXT(A1, "YYYY-MM-DD HH:mm:ss"), B1, C1)To:
toZone(TEXT(A1; "YYYY-MM-DD HH:mm:ss"); B1; C1)Silly thing, but it took me a few minutes! 😛
Hi David,
Thanks for sharing. Your toZone function works great! Do you know how to edit the script to work with array formula so that I can convert the timezone in bulk?
Thanks
David,
I tried to adding the function in my google sheet after creating a project with moment.js, I am getting the below error while applying the formula
Error: Unknown function: ‘toUtc’.
Arun: are you sure you have the function defined in your sheet? Do you see it when you go to Tools > Script editor?
Denny: I’m not entirely sure what you mean but you can apply the function to multiple rows in your sheet. You can just paste it in your first row and then drag to extend it to all the rows in your sheet.
Colleen: it sounds like maybe you want to do what I described in this comment above? Check out the sample document for the toBrowserZone function which guesses the user’s time zone from their browser.
Hi David,
Nice work – your instructions are clear – and, as a GS/JS noob, I got momentjs/moment-timezone running in a Google Sheet fine. But – while initial tests looked good, when I shifted the code to another spreadsheet (the heavily shared, production spreadsheet I wanted to apply the logic in), things started to break down. The problem seemed to be in unexpected results from moment.tz.guess() (with or without ignore cache) – in fact in the Intl.DateTimeFormat().resolvedOptions() call. Digging deeper I learnt that Google Apps Script (GAS) runs server-side. So – how is this going to work reliably? Does the server-side sandbox share the enough context with the browser to get the Intl.DateTimeFormat() init correct?
Hey David, I have used this tutorial before with huge success. So today I went to do it again and I keep get this error message when I run your above time conversion code and for the life of me, I cannot seem to fix it. and I am copying moment.js directly from the site you linked.
Error message = Missing name after . operator. (line 4911, file “moment.js”, project “moment”
Hi David,
Thanks for the good explanation of this, we able to make this work with a few error on my behalf along the way.
Two questions i have pls,
is there a way to add a conversion on the output, i’m in the UK, and at present we are +1 hour from UTC, we are BST not GMT. so it would need to switch at clock change.
Can we flip this around, whereas we give UK time, then we get the time in the local TZ in say Col B.
Thanks,
Dan.
@Dan: Check out the demo sheet. There is a tab that converts between 2 defined zones (eg Europe/London -> Europe/Athens). You could of course hard code this into your script if you always wanted to convert to Europe/London, for example.
For anyone with “ReferenceError: “m” is not defined.”, just delete the “m.” part of your functions. The “m.” part is if you imported an external library, which this walkthrough didn’t actually do.
@JF: The ‘m’ is the library identifier. It will default to ‘Moment’ but as I say in the post I like to change it to something shorter.
Hi David,
Thank you for sharing this information. It works great.
I wanted to understand if the daylight savings is taken into consideration in this . Thank you .
Regards
Nagendra
@Nagendra: Yes, daylight savings changes are accounted for using this method (they are not if you simply just add an offset to a time). One thing to bear in mind is that daylight savings rules change sometimes so it’s a good idea to update your Moment.js library with the latest version every now and then – yearly would be a good idea.
Hi, and thank you so much for this… I had the same issue with “ReferenceError: “m” is not defined.”, and following advice given above did not work and actually went through the whole process multiple times incase I missed something.
I managed to resolve the issue by putting Development mode to ‘on’