如何将 python datetime.datetime 转换为 excel 序列日期号 [英] How to convert a python datetime.datetime to excel serial date number

问题描述

我需要将日期转换为 Excel 序列号，以用于我正在编写的数据处理脚本.通过在我的 OpenOffice Calc 工作簿中处理日期，我能够推断出1-Jan 1899 00:00:00"映射到数字零.

I need to convert dates into Excel serial numbers for a data munging script I am writing. By playing with dates in my OpenOffice Calc workbook, I was able to deduce that '1-Jan 1899 00:00:00' maps to the number zero.

我编写了以下函数将python datetime对象转换为Excel序列号:

I wrote the following function to convert from a python datetime object into an Excel serial number:

def excel_date(date1):
    temp=dt.datetime.strptime('18990101', '%Y%m%d')
    delta=date1-temp
    total_seconds = delta.days * 86400 + delta.seconds
    return total_seconds

但是，当我尝试一些示例日期时，这些数字与我在 Excel(以及 OpenOffice Calc)中将日期格式化为数字时得到的数字不同.例如，在 Python 中测试 '2009-03-20' 给出 3478032000，而 excel 将序列号呈现为 39892.

However, when I try some sample dates, the numbers are different from those I get when I format the date as a number in Excel (well OpenOffice Calc). For example, testing '2009-03-20' gives 3478032000 in Python, whilst excel renders the serial number as 39892.

上面的公式有什么问题?

What is wrong with the formula above?

*注意:我使用的是 Python 2.6.3，所以无法访问 datetime.total_seconds()

*Note: I am using Python 2.6.3, so do not have access to datetime.total_seconds()

推荐答案

看来 Excel 的序列日期"格式实际上是自 1900-01-00 以来的 天数，带有小数基于 http://www.cpearson.com/excel/datetime.htm.(我想那个日期实际上应该被认为是 1899-12-31，因为没有一个月的第 0 天这样的东西)

It appears that the Excel "serial date" format is actually the number of days since 1900-01-00, with a fractional component that's a fraction of a day, based on http://www.cpearson.com/excel/datetime.htm. (I guess that date should actually be considered 1899-12-31, since there's no such thing as a 0th day of a month)

所以，看起来应该是:

def excel_date(date1):
    temp = dt.datetime(1899, 12, 30)    # Note, not 31st Dec but 30th!
    delta = date1 - temp
    return float(delta.days) + (float(delta.seconds) / 86400)

这篇关于如何将 python datetime.datetime 转换为 excel 序列日期号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！