Add time zones to the CONVERT function
Similar to how CONVERT(A1,"g","kg") is much clearer than A1/1000, CONVERT(A2,"UTC","EST") would be much clearer than A2-TIME(5,0,0).
Jon Wittwer commented
I would like the function to handle daylight saving time (DST), meaning something like CONVERT(datetime,"Mountain","Eastern"), but that would not be trivial because of the change in the laws regarding DST over time. The function would need to be more than just a table of UTC offsets. Having a function that was able to handle DST in date/time conversions would be much more powerful than just applying a simple UTC offset, because handling DST is difficult to do in a spreadsheet with just formulas.
Kenneth Barber commented
I was thinking more along the lines of converting time zone abbreviations (https://en.wikipedia.org/wiki/List_of_time_zone_abbreviations). So even though the Eastern Time Zone can observe EST or EDT depending on the time of year, EST is always UTC−05:00 and EDT is always UTC−04:00. Then we don't run into issues of region or time of year.
I realize that this doesn't address the language issue, but this can perhaps be addressed by using abbreviations in the language that the user is using for Excel.
A.C. WILSON commented
Implementing this would require:
- a good internal list of time-zone acronyms and definitions, for each language-region;
- keeping track of the varying (by local law/regulation) dates/times for switching to and from "summer" time.