CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttimezoneformuladst
SangyK
SangyK

Reputation: 849

Timezone conversion in a Google Sheets with DST (Daylight savings)

I know this looks simple.

In a Google spreadsheet, I have a column where I enter time in one timezone (GMT)

And another column should automatically get time in another time zone (Pacific Time)

GMT PT
5:00 AM 9:00 PM

As of now, I am using:

=$C$3-time(8,0,0)

The problem here is, I want to change the time formula for Daylight savings.

Is there any function or script available which can take the daylight saving into calculation automatically.

Upvotes: 48

Views: 169941

Answers (8)

player0
player0

Reputation: 1

SUMMARY

SHORT ANSWER

yes, you can easily use native formulae to convert timezone with respect to daylight savings

BASELINE

  • default GMT (Greenwich Mean Time) does not observe DST (Daylight Saving Time). GMT remains constant throughout the year
  • standard PT (Pacific Time) follows DST.
    • the start of DST (summer time) for PT is 2nd sunday of march at 2:00 AM (1 hour is added)
    • the end of DST (summer time) for PT is 1st sunday of november at 2:00 AM (1 hour is subtracted)
  • this applies only for PT as every other timezone will have different ruleset so keep in mind, that it is complicated. for PT, here is the ±5 year distro:
YEAR START END
2019 10 Mar 2019 02:00 am 03 Nov 2019 02:00 am
2020 08 Mar 2020 02:00 am 01 Nov 2020 02:00 am
2021 14 Mar 2021 02:00 am 07 Nov 2021 02:00 am
2022 13 Mar 2022 02:00 am 06 Nov 2022 02:00 am
2023 12 Mar 2023 02:00 am 05 Nov 2023 02:00 am
2024 10 Mar 2024 02:00 am 03 Nov 2024 02:00 am
2025 09 Mar 2025 02:00 am 02 Nov 2025 02:00 am
2026 08 Mar 2026 02:00 am 01 Nov 2026 02:00 am
2027 14 Mar 2027 02:00 am 07 Nov 2027 02:00 am
2028 12 Mar 2028 02:00 am 05 Nov 2028 02:00 am
2029 11 Mar 2029 02:00 am 04 Nov 2029 02:00 am

LOGIC

a mare timezone offset like -TIME(8, 0, 0) is not enough to account for DST. to do this the right way, we need to calculate those dates from above, based on a year we are in. the logic is simple. example of equation for start day:

(y &"-"& m &"-"& 1 + 7 * o) - WEEKDAY(y &"-"& m &"-"& 8 - d) + h + {y}

y = year          = 2024
m = month         = 3       (1-12 ... 3 = march)
o = day in month  = 2       (1-4  ... 2 = 2nd sunday) 
d = day of week   = 1       (1-7  ... 1 = sunday)
h = hour          = "2:00" 
y = plus one hour = "1:00"  (only if calculating start day! otherwise skip!)

some DSTs are based on "last day in month", which can be 4th or 5th in order. equation for that is:

(y &"-"& m + 1 &"-"& 8) - WEEKDAY(y &"-"& m + 1 &"-"& 8 - d) - 7 + h + {y}

enter image description here

after we acquired start and end of DST, we just deploy simple IF statement:

IF(((A1 + o) >= s) * ((A1 + o) < e), A1 + o, A1 + o - x) 

A1 = value (datetime or timevalue)                     = 2024-06-06 5:00
o  = timezone offset between PT summer time and GMT    = -"7:00"
s  = starting date of PT summer time                   = 2024-03-10 2:00
e  = ending date of PT summer time                     = 2024-11-03 2:00
x  = difference between summer time and winter time    = "1:00"

SOLUTION FOR OP

in spreadsheet settings select GMT (no daylight saving)

File > Settings > General > Time zone > (GMT+00:00) GMT (no daylight saving)

enter image description here

this will affect cases when we have times (timevalue) without dates (datevalue)

next, we can use this simple formula:

=BYROW(A20:A30, LAMBDA(a, LET(y, 
 IF(YEAR(a)=1899, a+LAMBDA(x,x)(TODAY()), a), 
 s, (YEAR(y)&"-03-15")-WEEKDAY(YEAR(y)&"-03-07")+"3:00", 
 e, (YEAR(y)&"-11-08")-WEEKDAY(YEAR(y)&"-11-07")+"2:00", 
 p, y-"7:00", IF(a="",,IF((p>=s)*(p<e), p, p-"1:00")))))

enter image description here

  • works only with GMT > PT (for universal solution, continue reading)
  • works with single cell
  • works with array
  • understands DST and timezone
  • it's fast and reliable
  • it's easy to read and edit
  • it's able to adjust DST for date automatically
  • no scripts needed
  • no libraries needed
  • works with datetimes
  • works with dates
  • works with timevalues
  • works with volatile NOW() and TODAY()
  • utilizes frozen timestamp LAMBDA(x,x)(TODAY()) no recalculation
  • if date is missing it assumes conversion takes place at that moment
  • costs nothing

spreadsheet demo copy

UNIVERSAL SOLUTION

based on this post we can borrow that "side table" from there and adjust the formula accordingly, so we could freely configure our desired timezone with proper DST, all of it in an instant:

=BYROW(A2:A30, LAMBDA(a, LET(y, IF(YEAR(a)=1899, a+LAMBDA(x,x)(TODAY()), a), 
 i, YEAR(y), m, E3, м, E7, o, LEFT(E4, 1), о, LEFT(E8, 1), 
 l, o="l", л, о="l", d, TEXT(SEQUENCE(7), "ddd"), p, y+E11,
 s, DATE(i, m+l, 1+7*IF(l, 1, o))-WEEKDAY(DATE(i, m+l, 8-XMATCH(E5, d)))-7*l+E6+"1:00", 
 e, DATE(i, м+л, 1+7*IF(л, 1, о))-WEEKDAY(DATE(i, м+л, 8-XMATCH(E9, d)))-7*л+E10, 
 IF(a="",, IF((p>=s)*(p<e), p, p-"1:00")))))

enter image description here

spreadsheet demo copy

RELEVANT

Upvotes: 5

user10897742
user10897742

Reputation: 15

That can also be done without macros. Just using functions and data manipulation will suffice. Explaining the whole process here would be a bit cumbersome. Just do your research on how the various time functions work and use your creativity.

Hint: Use =NOW() if you want both current date and time. You'll actually need that if you need to find out the precise diff in time between to different dates.

Use =NOW()-INT(NOW()) when you only want the time (with date truncated if both times fall on the same date). Then format the corresponding cell or cells for time (i.e. 4:30 PM), not for date-time (3/25/2019 17:00:00). The latter is the format you'd use when you want to show both date and time... like when you use NOW().

Also search online for the Daylight Saving Time offset for the various standard time zones (PT, MT, CT, ET, AT) with respect to the Coordinated Universal Time (UTC). For example, in 2019 the offset for Pacific Time is UTC-7 when DST is observed starting on March 10 at 2 AM (Pacific) until November 3 at 2 AM. That means that the difference in time from UTC to Pacific is 7 hours. During the rest of the year is 8 hours (UTC-8). During DST observance starting sometime in March (the 10th this yr) it goes from PST to PDT by moving clocks forward 1 hr, or what we know as UTC-7 (that's summer time). After DST observance it goes from PDT to PST by moving clocks back 1 hr again, or what we know as UTC-8 (or winter time). Remember that the clock is advanced one hour in March to make better use of time. That's what we call DST, or Daylight Saving Time. So after March 8 at 2 AM (this year in 2019) we are in UTC-7. In November, we do the opposite. In Nov 3 at 2 AM the clock is taken back one hour as the winter kicks in. At that point we are back in Standard Time. Seems a bit confusing, but it's really not.

So, basically, for folks in PT they go from PST to PDT in March and from PDT to PST in November. The exact same process goes on with Mountain Time, Central Time and Eastern Time. But they have different UTC time offsets. MT is either UTC-6 or UTC-7. CT is either UTC-5 or UTC-6. And ET is either UTC-4 or UTC-5. All depending on whether we are in summer time when Daylight Saving is observed to make better use of daylight and working hours, or in winter time (AKA, Standard Time).

Study these thoroughly and understand how they work, and play around with the various time functions in Excel or Google Sheets like the TIME(#,#,#) and NOW() functions and such, and believe me, soon you'll be able to do about anything like a pro with plain functions without having to use VBA Google Apps Script. You can also use the TEXT() function, though, with tricks like =TEXT(L4,"DDD, MMM D")&" | "&TEXT(L4,"h:mm AM/PM"), where L4 contains you date-timestamp, to display time and date formats. The VALUE() function also comes in handy every now and then. You can even design a numerical countdown timer without the use of macros. You'd need to create a circular reference and set iterations to 1, and time display to say every 1 min, in your spreadsheet settings for that.

The official timeanddate dot com website is a good source of info for all to know about time zones and how daylight time is handled. They have all UTC offsets there too.

Upvotes: -1

AeStudios
AeStudios

Reputation: 39

The easiest method is using a simple calculation.

Use =NOW() command in sheets and subtract it with the time difference divided by 24.

Example: IST to Colombia

=NOW()-(10.5/24)

The time difference from India to Colombia is 10hours and 50min, we need to subtract it from the "Now" time and divide it by 24.

If the time zone is ahead of your place, then you need to add it. Example:

IST to JAPAN:

=NOW()+(3.5/24)

=Now is set to US time by default, you can change it under general in settings.

enter image description here

enter image description here

Upvotes: 3

Ryan
Ryan

Reputation: 24103

UPDATED 2024-06-08

This tutorial was amazingly helpful: https://davidkeen.com/blog/2017/01/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.

These are the files I copied into my script project:

Make sure you add the moment.js script first and have it above the moment-timezone.js script because moment-timezone.js depends on it.

Then in your other script project, your Code.gs file can look like this:

/**
 * @customfunction
 */
function convertTimeZone(dateTime, fromZone, toZone) {
  const DT_FORMAT = 'YYYY-MM-DD HH:mm:ss'; // https://day.js.org/docs/en/display/format  
  // Logger.log('convertTimeZone', dateTime, fromZone, toZone);
  const momentDateTime = m.moment.tz(dateTime, DT_FORMAT, fromZone);//https://momentjs.com/timezone/docs/#/using-timezones/parsing-in-zone/
  const result = momentDateTime.tz(toZone).format(DT_FORMAT);
  // Logger.log(result);
  return result;
}

The screenshot below is outdated; the function name is now convertTimeZone, not fromUtc. Google Sheets screenshot

Since MomentJS is outdated, I tried to replace it with DayJS but couldn't get it to work because Google Sheets doesn't support Intl, which is relies on.

Upvotes: 17

NCPatriot
NCPatriot

Reputation: 11

I'm a new contributor and a novice, but I stumbled upon a function that had not been mentioned despite many hours of searching on the Sheets/Time Zone issue. Hoping this relatively simple solution will help.

For my sheet, I just want to add a row and automatically populate the local sheet date and time in the first two cells.

The .getTimezoneOffset() returns the difference in minutes between the client TZ and GMT, which in NY during Daylight Savings Time is 240. The function returns a positive number for the zones with "GMT-x", and vice versa for zones with "GMT+x". Hence the need to divide by -60 to get the correct hour and sign.

function addRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.insertRows(2, 1);
  rightNow = new Date();
  var tzOffset = "GMT" + rightNow.getTimezoneOffset() / -60;
  var fDate = Utilities.formatDate(rightNow, tzOffset, "MM-dd-yyyy");
  var fTime = Utilities.formatDate(rightNow, tzOffset, "HH:mm");
  sheet.getRange('A2').setValue(fDate);
  sheet.getRange('B2').setValue(fTime);
  sheet.getRange('C2').setValue(tzOffset);
}

I've since found that I'm not the first person to respond to the GMT correction connundrum mentioning .getTimezoneOffset(). However, this thread has the most views on this topic, so I figured this option deserves a mention.

DST ends here on November 7th, 2021, so I'll report back if it doesn't adjust as expected to "GMT-5"

.getTimezoneOffset()

Upvotes: 1

Wicket
Wicket

Reputation: 38469

Short answer

There is no built-in function but you could build a custom function.

Example

/**
 * Converts a datetime string to a datetime string in a targe timezone.
 *
 *@param {"October 29, 2016 1:00 PM CDT"} datetimeString Date, time and timezone.
 *@param {"Timezone"} timeZone Target timezone
 *@param {"YYYY-MM-dd hh:mm a z"} Datetime format
 *@customfunction
 */
function formatDate(datetimeString,timeZone,format) {
  var moment = new Date(datetimeString);
  if(moment instanceof Date && !isNaN(moment)){
    return Utilities.formatDate(moment, timeZone, format)
  } else {
    throw 'datetimeString can not be parsed as a JavaScript Date object'
  }
}

NOTE:

new Date(string) / Date.parse(string) implementation in Google Apps Script doesn't support some timezones abbreviations.

From https://tc39.es/ecma262/#sec-date-time-string-format

There exists no international standard that specifies abbreviations for civil time zones like CET, EST, etc. and sometimes the same abbreviation is even used for two very different time zones.

Related

Explanation

In order to consider daylight saving time zones the input argument for of the value to be converted should include the date, no only the time of the day. You could set a default date and time zone to build the datetimeString by concatenating it before calling the formula.

=formatDate("October 29, 2016 "&A2&" GMT","PDT","hh:mm a")

For the target timezone besides the three letter abbreviation we could use TZ database names like America/Los_Angeles, example:

=formatDate("October 29, 2016 "&A2&" GMT","America/Los_Angeles","HH:mm")

If timezone abbreviation and TZ name fails for the datetimeString use time offsets (i.e. UTC-4).

See also

References

Upvotes: 37

manonastreet
manonastreet

Reputation: 111

I had the same problem (convert Manila Time to Sydney Time and automatically adjust for daylight saving time) when I found this page.

I didn't want to have a custom function but I found that, in Sydney, AEST (Australian Eastern Standard Time) starts on the first Sunday of April and AEDT (Australian Eastern Daylight Time) starts on the first Sunday of October.

So I thought, if I could find a formula that detects whether a date falls between the first Sunday of April and first Sunday of October (Standard Time) then I can automatically add 1 hour to the usual 2 hours to Manila time during Daylight Saving Time (dates falling outside the two dates) to have Sydney Time.

These two Excel solutions worked fine in Google Sheets:

First Sunday of April this year (A1):

=CONCATENATE("4/1/",Year(today()))+CHOOSE(WEEKDAY(CONCATENATE("4/1/",Year(today())),1),7,6,5,4,3,2,1)

First Sunday of October this year (A2):

=CONCATENATE("10/1/",year(today()))+CHOOSE(WEEKDAY(CONCATENATE("10/1/",year(today())),1),7,6,5,4,3,2,1)

DST detector (A3) — if a date falls outside these two dates, it's DST in Sydney:

=IF(AND(today()>A1,today()<A2),"AEST","AEDT")

Time in Sydney (A4):

=NOW()+TIME(IF(A3="AEDT",3,2),0,0)

NOW() can be changed to any time format for tabulation:

enter image description here

Upvotes: 1

shar_m
shar_m

Reputation: 39

Create your own Timezone Converter in Google Sheets:

Step 1: Create your table for the timezone converter.

Step 2: Enter the times for your time zones in a column. Note: Ensure that you select date/time format(Select Cell(s) -> Format -> Number -> Time/Date)

Step 3: Write a formula to convert timezone using the following functions Google Sheet Functions

=HOUR(A8)+(B3*C3) converts the hours.
=MINUTE(A8)+(B3*C3) converts the minutes.

Step 4: Convert back to time format using TIME(h,m,s) function

=TIME(HOUR(A8)+(B3*C3), MINUTE(A8)+(B3*C3), SECOND(A8))

This is a simple way to convert timezones. However, if you want to implement an accurate timezone converter that takes care of the previous day, next day, and beyond 24 hours, beyond 60 minutes, please use MOD operations and handle all the cases. Visit(or Use) this google sheet for reference: https://docs.google.com/spreadsheets/d/1tfz5AtU3pddb46PG93HFlzpE8zqy421N0MKxHBCSqpo/edit?usp=sharing

Upvotes: -1

Related Questions