I have seen this question been lurking around forums and coincidentally I received a similar requirement to calculate multiple transactions based on business days by excluding weekends only. Please note that, this will not work if you have public holidays in between.
If you are seeking for a holistic approach, you will need to create a table in your data source to fill in weekends and public holidays that are relevant to your country. I will keep that as a separate topic to be discussed in the future.
This is a formula that I’m using in Web Intelligence and it’s working fine so far. You will require two dates in order to calculate the days in between and based on my example below. I’m trying to calculate the days in between my transacted date and current date by creating a new variable called “Transacted Date exclude Weekends”.
=(Truncate(DaysBetween([Transacted Date];CurrentDate()) / 7 ; 0) * 5) + ToNumber(Substr(“1234555123444512333451222345111234500123450123455”; ((DayNumberOfWeek([Transacted Date])-1)*7)+
Mod(DaysBetween([Transacted Date];CurrentDate());7)+1 ; 1))
As demonstrated, you can see that a weekend falls on 6-7 Feb 2016 has been disregarded in line item 04025. This can be achieved by simply copying the formula above, replace those variables and use it in your report. That’s it! and you’re done!
Pixadata 