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)
Leave a Reply