Business Days - Need to Exclude Holidays

building reports webintelligence
1

I have a formula for business days that removes the weekends, however, I also need to somehow exclude holidays. Told by the vendor it is too complex a formula for them to figure out (and I’ve had no luck myself). Below is what I have currently - any ideas on also removing the holidays?

=Floor(DaysBetween([Date Current Loan Status Set];CurrentDate())/7)*5+
If(DayNumberOfWeek(CurrentDate())>=DayNumberOfWeek([Date Current Loan Status Set]);
DayNumberOfWeek(CurrentDate())-DayNumberOfWeek([Date Current Loan Status Set]);
DayNumberOfWeek(CurrentDate())-DayNumberOfWeek([Date Current Loan Status Set])+5)

csmwhite (BOB member since 2016-11-23)

2

The easiest way I can think of to do this without a metric ton of time investment to manually construct dynamic flags that mimic the actual identification methodology for each individual holiday at your company would be a universe/db flag that matches to a table of dates that are listed elsewhere as holidays by your HR (or whoever determines them) department then use that to flag holidays and exclude them using if/then or where in your logic.

tendernips (BOB member since 2017-07-27)

3

Yeah, that is how we do it in Excel, but I don’t know how to build a table to reference from in the Business Objects tool. Was thinking sub-report like you can do in Microsoft Reporting Services, but again I can’t see how to do that in Business Objects.

csmwhite (BOB member since 2016-11-23)

4

you need a calendar table in the DB with each day and a 1/0 flag to indicate if it’s a working day or not.

erik.stenson :us: (BOB member since 2012-07-30)