How to convert python datetime.datetime to increase date serial number

Question

I need to convert dates to Excel serial numbers for data collection script I am writing. Playing with the dates in my OpenOffice Calc book, I was able to deduce that "Jan 1, 1899 00:00:00" displays a zero number.

I wrote the following function to convert from a python datetime object to 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 a few approximate dates, the numbers are different from the ones I get when I format the date as a number in Excel (well OpenOffice Calc). For example, testing “2009-03-20” yields 3478032000 in Python, while Excel allocates serial number 39892.

What is wrong with the formula above?

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

Answer 1

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

So it looks 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

Although not entirely important for the Excel date format, it was the best hit for exporting Python dates to Excel. What I found particularly useful and simple is simply exporting using strftime.

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

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

Answer 3

If the problem is that we want DATEVALUE () to exceed the serial number for dates, you can use the toordinal () function. Python serial numbers start on January 1 of year 1, while excel starts on January 1, 1900, so apply the offset. Also see Excel 1900 leap year ( https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year )

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

Answer 4

Using the third-party module xlrd.xldate you can provide a tuple structured as (year, month, day, hour, minute, second) and, if necessary, calculate the fraction of the day from any microsecond component:

 from datetime import datetime from xlrd import xldate from operator import attrgetter def excel_date(input_date): components = ('year', 'month', 'day', 'hour', 'minute', 'second') frac = input_date.microsecond / (86400 * 10**6) # divide by microseconds in one day return xldate.xldate_from_datetime_tuple(attrgetter(*components)(input_date), 0) + frac res = excel_date(datetime(1900, 3, 1, 12, 0, 0, 5*10**5)) # 61.50000578703704