如何在Python中将xlrd读取为浮点数的"YYYY:MM:DD"日期转为日期类型

我之前踩过一模一样的坑！Excel的日期格式总能搞出点意外，尤其是当它用冒号分隔（YYYY:MM:DD）的时候，要么是自定义格式的日期序列数，要么是被当成数值存储的日期字符串，直接用常规的转换代码根本行不通。下面分情况给你解决办法：

1. 先回顾标准YYYY/MM/DD日期的转换 #

当Excel单元格是标准日期类型时，它会被存储为一个日期序列数（从1900年1月1日开始的偏移天数），这时候用这段代码就能完美转换：

import datetime
import xlrd

workbook = xlrd.open_workbook("your_excel_file.xlsx")
work_sheet = workbook.sheet_by_index(0)

# 读取标准日期单元格，得到序列数（整数或浮点数）
x = work_sheet.cell(1, 4).value
# 修正Excel的1900闰年bug，转换为datetime
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(x) - 2)
print(dt)  # 输出: 2006-02-04 00:00:00

为什么要减2？因为Excel有个历史bug，错误地把1900年当成了闰年，所以需要减去2天来修正偏移量。

2. 解决YYYY:MM:DD格式的转换问题 #

如果用上面的代码转换失败，大概率是这个单元格不是标准日期序列数，而是下面两种情况之一：

情况A：单元格是文本格式的YYYY:MM:DD #

有些时候用户手动输入2006:02:04，Excel会把它当成文本存储，xlrd读取后得到的是字符串。这时候直接用strptime解析即可：

date_str = work_sheet.cell(1, 4).value
# 按冒号分隔的格式解析
dt = datetime.datetime.strptime(date_str, "%Y:%m:%d")
print(dt)  # 输出: 2006-02-04 00:00:00

情况B：单元格是数值，自定义格式为YYYY:MM:DD #

另一种常见情况是：单元格里实际存储的是20060204（数值），但通过Excel的自定义格式设置成了YYYY:MM:DD来显示。这时候xlrd读取到的是浮点数（比如20060204.0），需要先转成字符串再解析：

x = work_sheet.cell(1, 4).value
# 转成整数后再转字符串，确保是8位的日期数字
date_str = str(int(x)).zfill(8)
# 按YYYYMMDD格式解析
dt = datetime.datetime.strptime(date_str, "%Y%m%d")
print(dt)  # 输出: 2006-02-04 00:00:00

通用兼容方案（自动判断格式） #

如果你不确定单元格属于哪种情况，可以写个小函数来兼容处理：

import datetime
import xlrd

def excel_to_datetime(cell_value):
    if isinstance(cell_value, str):
        # 尝试解析文本格式的日期（支持/或:分隔）
        for fmt in ["%Y/%m/%d", "%Y:%m:%d"]:
            try:
                return datetime.datetime.strptime(cell_value, fmt)
            except ValueError:
                continue
        raise ValueError(f"无法解析日期字符串: {cell_value}")
    elif isinstance(cell_value, (int, float)):
        # 先尝试标准日期序列数转换
        try:
            return datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(cell_value) - 2)
        except (ValueError, OverflowError):
            # 如果失败，尝试按数值转日期字符串解析
            date_str = str(int(cell_value)).zfill(8)
            return datetime.datetime.strptime(date_str, "%Y%m%d")
    else:
        raise TypeError(f"不支持的单元格值类型: {type(cell_value)}")

# 使用示例
workbook = xlrd.open_workbook("your_file.xlsx")
work_sheet = workbook.sheet_by_index(0)
cell_value = work_sheet.cell(1, 4).value
dt = excel_to_datetime(cell_value)
print(dt)

这个函数会自动判断读取到的单元格值类型，先尝试常规转换，失败后再切换到文本/数值解析逻辑，基本能覆盖大部分情况。

内容的提问来源于stack exchange，提问作者Gabriel Octavio Lozano Pinzón