在Google Sheets中实现UTC与含夏令时偏移的本地时间互转

一、UTC时间戳转本地时间（自动处理夏令时） #

针对输入格式为YYYYMMDDTHHMMSSZ的UTC时间，使用以下公式转换为dd/mm/yyyy HH:mm:ss格式的本地时间（需替换时区为你的目标时区，比如Europe/Athens、America/New_York）：

=TEXT(TIMEZONE(TO_DATE(VALUE(REGEXREPLACE(A1, "^(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})Z$", "$1-$2-$3 $4:$5:$6"))), "UTC", "Europe/Athens"), "dd/mm/yyyy HH:mm:ss")

公式说明： #

1. REGEXREPLACE(A1, ...)：将非标准UTC字符串转换为Google Sheets可识别的YYYY-MM-DD HH:MM:SS格式
2. TO_DATE(VALUE(...))：将转换后的字符串转为日期时间数值
3. TIMEZONE(..., "UTC", "目标时区")：自动根据目标时区的夏令时规则，将UTC时间转换为本地时间
4. TEXT(..., "dd/mm/yyyy HH:mm:ss")：将转换后的日期时间数值格式化为指定的可读格式

二、本地时间转UTC时间戳（自动处理夏令时） #

针对输入格式为dd/mm/yyyy HH:mm:ss的本地时间，使用以下公式转换为YYYYMMDDTHHMMSSZ格式的UTC时间（需替换时区为你的本地时区）：

=TEXT(TIMEZONE(TO_DATE(VALUE(SUBSTITUTE(LEFT(B1,10), "/", "-")&" "&RIGHT(B1,8))), "Europe/Athens", "UTC"), "YYYYMMDD\"T\"HHMMSS\"Z\"")

公式说明： #

1. SUBSTITUTE(LEFT(B1,10), "/", "-")：将本地时间的日期部分从dd/mm/yyyy转为yyyy-mm-dd，确保Google Sheets能识别
2. TO_DATE(VALUE(...))：将拼接后的yyyy-mm-dd HH:MM:SS字符串转为日期时间数值
3. TIMEZONE(..., "本地时区", "UTC")：自动根据本地时区的夏令时规则，将本地时间转换为UTC时间
4. TEXT(..., "YYYYMMDD\"T\"HHMMSS\"Z\"")：将转换后的UTC时间格式化为指定的标准戳格式

关键注意点 #

• 必须使用IANA时区标识符（如Asia/Shanghai、Europe/London）作为时区参数，Google Sheets会自动处理这些时区的夏令时切换规则，无需手动编写判断逻辑
• 若输入的时间格式有变动，只需调整REGEXREPLACE或字符串处理部分的规则即可适配

内容的提问来源于stack exchange，提问作者player0