Add support for an FME date calculation function (either as a datetime function or within a transformer such as DateTimeCalculator) that works on a 5-day week or 'business days' system. Excel has a NETWORKDAYS function that takes a start date, end date, and holidays input to do this kind of calculation.
Page 1 / 1
A solution using a PythonCaller and the Python workdays utility could provide a scripted workaround for the time being. The utility mimics the Excel NETWORKDAYS function, which takes a start date, end date, and list of holidays.
The
5 work days week can be implemented with existing datetime functionality. Holidays will cause some difficulties though. There are different holidays and different holiday rules in different regions. The rules also might change over time.
The easiest option would be to implement something similar to Excel NETWORKDAYS(start_date, end_date, [holidays]), however, typing in the list of holidays is not extremely user friendly.
I wonder how flexible this functionality should be. Does it need to be aware of regional holiday rules and/or have embedded regional holiday lists? Would the user need to be able to alter the holiday lists? Will the functionality be used to calculate business days this year only? Or will it be used for calculations in the past/in the future? How remote will be the past/future then?
If business days calculation is implemented in form of a custom transformer with holiday lists/rules included, would the community be willing to collaborate to keep the lists/rules up to date?
It would be nice as transformer. I have a PythonCaller but it is not as intuitive as a transformer could be.
An input port for holidays would be quite easy to deal with. Just create a CSV or a database table with the holiday dates and read it first in the workspace.
I stole the scripts from Odysseas Tsatalos' workdays package and made it to use in a PythonCaller:
import fme import fmeobjects from datetime import timedelta from datetime import datetime (MON, TUE, WED, THU, FRI, SAT, SUN) = range(7) weekends=(SAT,SUN) def networkdays(start_date, end_date, holidays=[]): delta_days = (end_date - start_date).days + 1 full_weeks, extra_days = divmod(delta_days, 7) # num_workdays = how many days/week you work * total # of weeks num_workdays = (full_weeks + 1) * (7 - len(weekends)) # subtract out any working days that fall in the 'shortened week' for d in range(1, 8 - extra_days): if (end_date + timedelta(d)).weekday() not in weekends: num_workdays -= 1 # skip holidays that fall on weekends holidays = [x for x in holidays if x.weekday() not in weekends] # subtract out any holidays for d in holidays: if start_date <= d <= end_date: num_workdays -= 1 return num_workdays def processFeature(feature): start_date = datetime.strptime(str(feature.getAttribute("pyStartDate")), "%Y%m%d").date() end_date = datetime.strptime(str(feature.getAttribute("pyEndDate")), "%Y%m%d").date() holidays = [datetime.strptime(date, "%Y%m%d").date() for date in feature.getAttribute("pyHolidays").split("_")] feature.setAttribute("py_networkdays", networkdays(start_date, end_date, holidays))As input attributes it needs pyStartDate, pyEndDate in %Y%m%d format (FME Date, eg: 20170521) and pyHolidays in the same %Y%m%d concatenated by _ (underscores, eg: 20171225_20171226).
Output (Attribute Exposed) is py_networkdays.