Converting floating point date based on 1900 Date System

[updated formatting 2022-11-08]

Hi,

Excel can either use the number of days since 1900 or 1904, as you discovered. Make sure you use the correct formula.

Here is the code for a PythonCaller that will convert either of those to a formatted timestamp string:

import fmeobjects
import datetime
 
def xldate_as_datetime(xldate, datemode):
   # datemode: 0 for 1900-based, 1 for 1904-based
   epoch = datetime.datetime(1899, 12, 30)
   excel_date = datetime.timedelta(days=xldate + 1462 * datemode)
   return epoch + excel_date
 
def FeatureProcessor(feature):
   days_since_1900 = float(feature.getAttribute('date'))
   date = xldate_as_datetime(days_since_1900, 0)
   formatted_date = date.strftime('%Y-%m-%d %H:%M')
   feature.setAttribute('new_date', formatted_date)

Note the "datemode" parameter that you can set to either 0 or 1 depending of which base year you're using.

The example above expects an attribute "date" containing a float and will create a new attribute "new_date" containing a text string representation of the calculated date. You can modify the parameters to strftime to adapt the results to your needs. See here (http://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior) for more info.

Output from Logger after a run on my machine:

 'date' has value `41165.2823011227'
 'new_date' has value `2012-09-13 06:46'

Watch our for potential time zone issues, if relevant...

Hi,

I have the exact same question as Jon's (Converting floating point date based on 1900 Date System)

It looks like a DateTimeConverter can solve this.

@avdb If you didn't want to use the python already suggested you could use a DateTimeCalculator to get the date

e.g. using the 1900 date system

Fantastic @ebygomm, this worked great! Thanks so much for the detailed screenshot!