如何转换python datetime。excel序列号的日期时间

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.

我需要将日期转换成Excel序列号，以获得我正在编写的数据清除脚本。通过在我的OpenOffice Calc工作簿中使用日期，我能够推断出“1-Jan 1899 00:00”映射到数字0。

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

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

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

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.

但是，当我尝试一些示例日期时，这些数字与我在Excel中格式化日期时得到的数据不同(OpenOffice Calc)。例如，在Python中测试“2009-03-20”得到3478032000，而excel将序列号显示为39892。

What is wrong with the formula above?

上面的公式有什么问题?

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

*注意:我正在使用Python 2.6.3，所以不能访问datetime.total_seconds()

3 个解决方案

#1

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)

看起来Excel的“串行日期”格式实际上是自1900-01-00以来的天数，其中的分数部分是一天中的一小部分，基于http://www.cpearson.com/excel/datetime.htm。(我想这个日期应该是1899-12-31年，因为没有一个月的第0天这回事)

So, it seems like it should be:

所以，看起来应该是:

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)

#2

While this is not exactly relevant to the excel serial date format, this was the top hit for exporting python date time to Excel. What I have found particularly useful and simple is to just export using strftime.

虽然这与excel串行日期格式并不完全相关，但这是将python日期时间导出为excel的最成功的方法。我发现特别有用和简单的是使用strftime导出。

import datetime
current_datetime = datetime.datetime.now()
current_datetime.strftime('%x %X')

This will output in the following format '06/25/14 09:59:29' which is accepted by Excel as a valid date/time and allows for sorting in Excel.

这将输出如下格式的“06/25/14 09:59:29”，它被Excel作为一个有效的日期/时间接受，并允许在Excel中进行排序。

#3

if the problem is that we want DATEVALUE() excel serial number for dates, the toordinal() function can be used. Python serial numbers start from Jan1 of year 1 whereas excel starts from 1 Jan 1900 so apply an offset. Also see excel 1900 leap year bug (https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year)

如果问题是我们想要DATEVALUE() excel序列号作为日期，那么可以使用toordinal()函数。Python序列号从第一年的1月1日开始，而excel从1900年1月1日开始，所以应用偏移量。还可以查看excel 1900闰年bug (https://support.microsoft.com/en-us/help/214326/excel-incorrect - hypothesis -the- the-year-1900-is- leap-year -year- year- year- year)

def convert_date_to_excel_ordinal(day, month, year) :

    offset = 693594
    current = date(year,month,day)
    n = current.toordinal()
    return (n - offset)

#1