zondag, februari 09, 2014

Business days between two dates (in Business Objects)

A colleague of mine asked me: is it possible to calculate the amount of working days between two dates. Immediately, I thought : of course, but as it turns out, it's not that easy.

A quick google and I found this formula:


=DaysBetween(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin])));RelativeDate([End];Floor(DayNumberOfWeek([End])/6)*(8-DayNumberOfWeek([End])))) -Floor(DaysBetween(RelativeDate(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin])));(DayNumberOfWeek(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin]))))*-1)+1);RelativeDate([End];Floor(DayNumberOfWeek([End])/6)*(8-DayNumberOfWeek([End]))))/7)*2

I'm sure there's an easier way, and I'm working on it, but for the time being, it does the job just nicely. Thanks to this guy's post: http://blog.davidg.com.au/2012/11/workdays-between-two-dates-in-webi.html

Thanks mate. Loved the article.

Peter