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

Cookbook

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)

Tanaka

+1 for the link. It seems Excel is using the wierd date of 1900-01-00, since dates calculated by the function above is out by one day (according to Excel). – Cookbook

See joelonsoftware.com/items/2006/06/16.html. Basically, it's because Excel wanted to be compatible with Lotus 1-2-3 on dates. – Putty

I tried your function, but I have to change temp = dt.datetime(1899, 12, 30) in order to get correct results. – Edict

change "temp = dt.datetime(1899, 12, 30)" with "temp = datetime.datetime(1899, 12, 31)" – Photojournalism

a cell of number 60 in excel, press Ctrl+Shift+3 would convert into 1900/02/29, which is not exists in any calendar. This is where the weird one more day comes from. – Catalan

Adding + (float(delta.microseconds) / 1e5 / 864000) to the return value, and formatting the excel cells as hh:mm:ss.000, gives you the milliseconds part. – Dulaney

@Putty Your comment deserves more recognition. If one wants to really know what is going on with Excel date numbering that Joel Spolsky blog is the place to go. – Estus

@FObersteiner probably because it was added in Python 3.2, and when this question was asked Python 2 was still the most relevant. The question even mentions that specifically. – Casual

@Casual uh right, that why was kind of stupid. total_seconds might give a bit more concise code but no benefits otherwise, afaik. – Rosetta

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.

Huckaby

this works perfectly and is more elegant I feel, if you are willing to go to Excel and have the extra step to convert to serial over there – Nectar

This is the best answer, hands down. Simple. Pythonic. And easy to remember (%e-x-cel)! :) – Penrod

As JazyWhit says, be careful that this won't work when one exactly wants to convert a datetime to excel date number. This is off topic and is not a best answer for this question. – Foyer

Except it's wrong because %x is a locale-sensitive representation of the date, so whether it works depends on the excel locale and the python locale matching. The selected answer works for all cases but dates in January and February 1900. – Casual

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

def convert_date_to_excel_ordinal(day, month, year) :

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

Hardiman

Answer 4

With the 3rd party xlrd.xldate module, you can supply a tuple structured as (year, month, day, hour, minute, second) and, if necessary, calculate a day fraction from any microseconds 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

Hygienics

Answer 5

According to @akgood's answer, when the datetime is before 1/0/1900, the return value is wrong, the corrected return expression may be:

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

Inae

Answer 6

This worked when I tested using the csv package to create a spreadsheet:

from datetime import datetime

def excel_date(date1):
    return date1.strftime('%x %-I:%M:%S %p')

now = datetime.now()
current_datetime=now.strftime('%x %-I:%M:%S %p')
time_data.append(excel_date(datetime.now()))
...

Verda