Dynamics CRM 2011 Timestamp into Python

Question

I am exporting data from Dynamics CRM 2011 into an Excel File. The entries have timestamps. In CRM they look like normal dates, but when I export them into Excel the timestamps look similar to this:

41855.4043865741

41831.6309259259

In Excel I can right-click on the cell with the timestamp and do Cell Formatting > Numbers > Date and convert this to a human readable string.

e.g. 04.08.2014 09:42:19

11.07.2014 15:08:32

The problem is, after I save the Excel with the human readable Datetime format and read the Excel with the xlrd Module (Python 2.7) I still get the strange format and not the translated one.

So I tried using datetime Module to dranslate the date but when doing so I get the wrong date.

import datetime
str_dt = float(41831.6309259259)
print datetime.datetime.fromtimestamp(str_dt).strftime('%Y-%m-%d %H:%M:%S')

My result is: 1970-01-01 12:37:11 Of course it is not Unixtimestamp but I have no idea what timestamp it actually is and how I can convert it with python.

score 1 · Accepted Answer · edited May 23 '17 at 10:27

The values you see are the actual number of days since the 1st of January 1900. This is the format used by CRM and Excel.

As suggested in this answer, you can use xlrd.xldate.xldate_as_datetime to:

Convert an Excel date/time number into a datetime.datetime object.

@param xldate The Excel number
@param datemode 0: 1900-based, 1: 1904-based.

@return a datetime.datetime() object.

In your case the datemode would be 0.

Dynamics CRM 2011 Timestamp into Python

1 Answers1