32

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

jpp
  • 147,904
  • 31
  • 244
  • 302
Homunculus Reticulli
  • 60,275
  • 77
  • 205
  • 314

6 Answers6

50

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)
Community
  • 1
  • 1
akgood
  • 1,027
  • 8
  • 4
  • +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). – Homunculus Reticulli Mar 05 '12 at 22:30
  • 6
    See http://www.joelonsoftware.com/items/2006/06/16.html. Basically, it's because Excel wanted to be compatible with Lotus 1-2-3 on dates. – kindall Mar 05 '12 at 22:37
  • 12
    I tried your function, but I have to change `temp = dt.datetime(1899, 12, 30)` in order to get correct results. – saroele Mar 28 '12 at 13:09
  • change "temp = dt.datetime(1899, 12, 30)" with "temp = datetime.datetime(1899, 12, 31)" – rugby82 May 22 '15 at 09:28
  • 3
    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. – cdarlint Jun 09 '15 at 03:28
  • 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. – Tjaart Jul 17 '17 at 06:57
  • @kindall 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. – FinancialRadDeveloper Jul 12 '18 at 14:58
  • Why add days and seconds, why not simply use the `total_seconds` of the timedelta object? – FObersteiner Sep 07 '21 at 14:53
16

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.

JazzyWhit
  • 382
  • 3
  • 7
  • 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 – newyuppie Nov 01 '14 at 15:36
  • 1
    This is the best answer, hands down. Simple. Pythonic. And easy to remember (%e-x-cel)! :) – steve-gregory Mar 31 '16 at 05:38
  • 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. – Yuya Takashina Sep 30 '21 at 01:06
9

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)
rjha94
  • 4,142
  • 3
  • 28
  • 36
1

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
jpp
  • 147,904
  • 31
  • 244
  • 302
0

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
F ONE
  • 23
  • 4
0

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()))
...
Michael Behrens
  • 635
  • 6
  • 7