How to convert a python datetime.datetime to excel serial date number

Question

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.

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 

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?

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

Answer 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)

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) 

Answer 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.

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.