One request that we get a lot is a way to
calculate the difference between two dates, but only using business or working
days (so not including the weekend). As
with many of these types of solutions, we will need to create a new calculated
column to hold these values.
If we just wanted to calculate the number of
days between two dates, we can do that easily using our DateDiff function
available anywhere you can create an expression in Spotfire. For example,
assume we have a column called ‘ORDER DATE’ and another one called ‘DELIVERY
DATE’, the expression to calculate the number of days between them would be the
following:
DateDiff("day",[ORDER
DATE],[DELIVERY DATE])
However, this calculation includes all days,
not just weekdays.
Suppose we want to output the number of
weekdays (not Saturday or Sunday)? We can use a more complicated expression to calculate
only days in between the two dates that are Monday through Friday:
((((Integer(((Integer(DateDiff("day",[ORDER
DATE],[DELIVERY DATE]) / 7) * 5) + DayOfWeek([DELIVERY DATE])) - DayOfWeek([ORDER
DATE])) + (if((integer(DayOfWeek([ORDER DATE])))>(integer(DayOfWeek([DELIVERY
DATE]))),5,0))) - (if((integer(DayOfWeek([DELIVERY DATE])))=(6),1,0))) +
(if((integer(DayOfWeek([DELIVERY DATE])))<>(0),1,0))) + (if((integer(DayOfWeek([DELIVERY
DATE])))=(0),1,0))) - (if((integer(DayOfWeek([ORDER DATE])))=(0),1,0))
If you have a non-standard work week (like
you also include Saturdays), you can update the expression above to include
Saturdays and only exclude Sundays.
This of course will not pick up holidays that
fall into the work week. If you want to
include this, there are a couple of workarounds that are not ideal , but will
do the trick. One such workaround is to create an if statement for each holiday
you want to include, and check if the holiday is between the ‘ORDER DATE’ and ‘DELIVERY
DATE’. Then sum up all of the if statements and you will have a list of
holidays that fell between the two dates.
You can subtract this from the first column you created and you have
your working days that exclude holidays (assuming you only include the holidays
that fall on working days)…I did say it was a workaround after all.
As an example, suppose we wanted to exclude
Christmas and Easter. In 2009, Christmas was the 359th day of the
year and Easter was the 102nd day of the year. We can then create another
calculated column that gives us the
total number of holidays falling on weekdays between our two dates, using the
following expression:
sum(if (DayOfYear([ORDER DATE]) < 359 and 359
< DayOfYear([DELIVERY DATE]),1,0),if (DayOfYear([ORDER DATE]) < 102 and 102
< DayOfYear([DELIVERY DATE]),1,0))
Of course ,maintaining a hard-coded list of
holidays inside your calculation may not be the most effective solution. If you
want a more robust solution that allows the user to enter the holidays in ,
then we would recommend using our SDK to either create a tool that allows the
user to configure a working week and specify holidays, or create a calculation
that shows up in the function list inside any expression dialog. Similar to the
tool, the calculation would take in as arguments, the two date columns, and
then a list of strings defining the work week (i.e. ‘M,T,W,Th,F’), and also a
listing of holidays (‘Apr-12-2009, Dec-25-2009,etc…’)