pd.to_datetime转换Excel日期返回错误年份与日期问题排查

读取得到的整数是Excel存储日期用的原生序列号：

• Windows版Excel默认以1900-01-01为日期计数起点，每经过1天序列号数值加1。由于早期的历史兼容bug，Excel会错误统计不存在的1900-02-29日期，导致序列号和实际公历日期存在固定偏移。
• 两种错误转换的原因非常明确：
  • 加unit='D'参数时，pd.to_datetime默认以Unix纪元1970-01-01为计数起点，和Excel的起点差了近70年，因此转换结果全部偏移到2091年
  • 去掉unit参数时，pandas会把整数值当成纳秒级Unix时间戳解析，自然得到1970年1月1日附近的错误时间

原始Excel中的日期格式示例：

方案1：读取文件时自动解析（优先推荐） #

pandas的read_excel内置了Excel日期的适配逻辑，会自动处理纪元偏移和1900年闰年bug，读取时指定要解析的日期列即可：

import pandas as pd
df = pd.read_excel(excelfile, parse_dates=['Dates'])

方案2：已读取为整数列的手动转换 #

如果已经完成文件读取、日期列已经被加载为整数，可以在转换时指定Excel对应的计数起点，不需要额外计算偏移：

# 针对Windows平台生成的1900纪元Excel文件
df['Dates'] = pd.to_datetime(df['Dates'], unit='D', origin='1899-12-30')

转换后给出的样例值44327会被正确解析为2021-05-11，和Excel中存储的原始日期完全一致。

注意：如果是Mac平台老版本导出的1904纪元Excel文件，把origin参数替换为'1904-01-01'即可正确解析，目前绝大多数流通的Excel文件都使用1900纪元规则。

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