如何将给定的序数(从 Excel)转换为日期 [英] How to convert a given ordinal number (from Excel) to a date

问题描述

我有一个值 38142，我需要使用 python 将其转换为日期格式.如果在 excel 中使用此数字并在那时右键单击并格式化单元格，则该值将转换为 04/06/2004，我需要使用 python 获得相同的结果.我怎样才能做到这一点

I have a Value 38142 I need to convert it into date format using python. if use this number in excel and do a right click and format cell at that time the value will be converted to 04/06/2004 and I need the same result using python. How can I achieve this

推荐答案

Excel 中的偏移量是自 1900/01/01 以来的天数，1 是 1900 年 1 月的第一天，所以将天数作为时间增量添加到 1899/12/31:

The offset in Excel is the number of days since 1900/01/01, with 1 being the first of January 1900, so add the number of days as a timedelta to 1899/12/31:

from datetime import datetime, timedelta

def from_excel_ordinal(ordinal, _epoch0=datetime(1899, 12, 31)):
    if ordinal >= 60:
        ordinal -= 1  # Excel leap year bug, 1900 is not a leap year!
    return (_epoch0 + timedelta(days=ordinal)).replace(microsecond=0)

对于1900/02/28之后的任何日期，您必须将序数调整一天；Excel 继承了 Lotus 1-2-3 的 闰年错误并处理了 1900作为闰年.上面的代码为 59 和 60 返回 datetime(1900, 2, 28, 0, 0) 以对此进行更正，并带有小数值在 [59.0 - 61.0) 范围内，都是当天 00:00:00.0 到 23:59:59.999999 之间的时间.

You have to adjust the ordinal by one day for any date after 1900/02/28; Excel has inherited a leap year bug from Lotus 1-2-3 and treats 1900 as a leap year. The code above returns datetime(1900, 2, 28, 0, 0) for both 59 and 60 to correct for this, with fractional values in the range [59.0 - 61.0) all being a time between 00:00:00.0 and 23:59:59.999999 on that day.

以上也支持用小数表示时间的序列，但由于 Excel 不支持微秒，所以这些都被删除了.

The above also supports serials with a fraction to represent time, but since Excel doesn't support microseconds those are dropped.

这篇关于如何将给定的序数(从 Excel)转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！