如何设置Excel单元格格式以识别带时区偏移的日期时间

方法1：公式手动调整+格式化 #

你的单元格内容2023-01-24 23:06:55+01里的+01代表东1区（比UTC快1小时），要转成标准的yyyy-mm-dd hh:mm:ss格式，分两步操作：

步骤1：用公式转换时区 #

如果要转成UTC时间，在空白单元格输入公式：

=DATEVALUE(LEFT(A1,10)) + TIMEVALUE(MID(A1,12,8)) - (RIGHT(A1,2)/24)

• LEFT(A1,10)提取日期部分，MID(A1,12,8)提取时间部分，RIGHT(A1,2)提取偏移小时数，除以24是因为Excel中1小时等价于1/24天。

如果要转成本地时区（比如你在东8区，比UTC快8小时），公式调整为：

=DATEVALUE(LEFT(A1,10)) + TIMEVALUE(MID(A1,12,8)) - (RIGHT(A1,2)/24) + (8/24)

步骤2：设置单元格格式 #

右键公式所在单元格→「设置单元格格式」→切换到「数字」选项卡→选择「自定义」，在类型框中输入yyyy-mm-dd hh:mm:ss，点击确定即可。

方法2：Power Query批量处理（适合大量数据） #

如果数据量较大，用Power Query处理更高效：

1. 选中目标数据列，点击「数据」→「从表格/区域」（弹窗中勾选「我的表格有标题」）
2. 在Power Query编辑器中，选中时间列，先转换为文本类型：「转换」→「数据类型」→「文本」
3. 添加自定义列：「添加列」→「自定义列」，输入公式（将[时间列]替换为你的实际列名）：

= DateTime.FromText([时间列], [Format="yyyy-MM-dd HH:mm:sszzz"]) - #duration(0,1,0,0)

• DateTime.FromText会自动识别带时区的字符串，减去#duration(0,1,0,0)是抵消东1区的偏移，得到UTC时间；如果要转本地时区，改成加上对应差值，比如东8区就加#duration(0,7,0,0)（UTC+1到UTC+8需加7小时）

4. 点击「关闭并上载」，将处理后的数据导回Excel，再设置单元格格式为yyyy-mm-dd hh:mm:ss

注意事项 #

• 如果偏移量是负数（比如2023-01-24 23:06:55-05），公式里的减法要换成加法，例如+ (RIGHT(A1,2)/24)
• 若Excel无法识别日期字符串，检查系统区域设置的日期格式是否与单元格内格式匹配

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