Is there a way in FME to accomplish this same conversion?
More info on the 1900 Date System here: http://support.microsoft.com/kb/180162
Question
Converting floating point date based on 1900 Date System
+5- Contributor
- 25 replies
I have a floating point number (i.e. 41165.2823011227) that I need to convert to year, month, day, hour, minute, second. All I've been told is that the floating point number is the number of days since January 1st, 1900. After a little bit of research, it looks like you can convert these in Excel (make sure you have 'Use 1904 date system' unchecked under Advanced Options). When I enter the floating point number and format the cell to Date, I get 9/13/2012 6:46:31 AM which looks correct.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- 8391 replies
[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...
- Contributor
- 3 replies
Thanks David - that did the trick!
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.
What should the "Input Format" be (it is not ISO nor FME nor Epoch which is based on 01 Jan 1970)?
Thanks!
+44- Influencer
- 3422 replies
@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
