The converttimezone( function converts a time or superdate from one time zone to another.
This function has three parameters:
time – time to be converted, either a time (seconds since midnight) or a superdate containing both the date and time.
fromzone – current time zone of the time value (or
"" for the local time zone).
tozone – new time zone to convert into, (or
"" for the local time zone).
This function converts a time or superdate from one time zone to another.
converttimezone(time("3:18 PM"),"New York","Berlin") ☞ 76680
When converted to text with the timepattern( function, the number 76680 becomes 9:18 PM.
When this function is used to convert a superdate, it adjusts the day as well as the time (if needed). For example this formula
converttimezone(superdate(date("12/25/2020"),time("3:18 PM")),"New York","Sydney")
will result in the value 3691811880, which is Saturday, December 26th, 2020 @ 7:18:00 am.
When converting a superdate, the conversion will be adjusted for daylight savings time if needed. Thanks to clever code provided by Apple, the function knows on what dates each time zone starts and ends daylight savings time in different parts of the world.
The best way to specify a time zone is using the name of a major city in the geographic area of the zone. To get a list of all the supported cities, use the info(“timezones”) function. If you use this function you’ll see that the list includes the continent or ocean, for example
Pacific/Honolulu. However, you don’t have to include the continent or ocean when specifying the zone, simply
honolulu will suffice (and capitalization doesn’t matter).
Why is a city the best way to specify the zone? Consider the city of Phoenix, Arizona. Phoenix is in the Mountain Time Zone, but in the summer Arizona does not use Daylight Savings Time, so the time is different than other areas in that time zone. By specifying the zone as Phoenix, you’ll always get the correct conversion for Arizona, no matter what time of year it is.
Another way to specify the time zone is using a three or four letter abbreviation, for example CET for Central European Time. For a complete list of all available abbreviations, use the info(“timezoneabbreviations”) function. Note that some abbreviations imply that they are only for standard or daylight savings time, for example EST vs. EDT. However, the function ignores this hint – it will always use the current time in the specified zone. So specifying EST in July will get you the daylight savings time on the American east coast, not the standard time. (You can use the daylightsavingstimeoffset( function to check whether daylight savings time is in effect in a time zone, and to get an offset value that can be used to convert back to standard time if needed.)
If you are in North America, you can also set the time zone to Hawaii, Alaska, Pacific, Mountain, Central, Eastern or Atlantic. These zone names will not work if your computer is not set to a North American time zone (this is because other parts of the world also have time zones with these names, for example in Europe and Australia).
To convert to or from the local time zone (the time zone the computer is set to), use
"" as the time zone name. For example, this code will convert 7:12 PM from Paris time into local time. (The time24( function is needed in case the conversion goes over midnight. It should only be used with regular time, do not use the time24( function when converting superdates.)
This formula will do the reverse, convert 11:24 AM in local time into Paris time.
As a shortcut, you can leave out the second parameter in this situation, so this formula will also convert 11:24 AM in local time into Paris time.
Coordinated Universal Time (or UTC) is the primary time standard by which the world regulates clocks and time. It is the successor to Greenwich Mean Time (GMT), though for the purposes of this function, UTC and GMT are identical. To maintain a consistent global time system, UTC does not observe Daylight Saving Time, but remains constant year round.
To convert to or from UTC or GMT, use either of these abbreviations as the time zone name. For example, this code will convert 8:17 AM from local time into UTC/GMT time.
This formula will do the reverse, convert 11:24 AM in UTC time into local time.
As a shortcut, you can leave out the both the second and third parameters in this situation, so this formula will also convert 11:24 AM in local time into UTC time.
|10.2||New||New in this version|