Workdays between in Business Objects

Excel has a nice function : networkdays() -- it will show you the net amount of working days between two dates (excluding weekends).

Business Objects doesn't have this -- it only has "daysbetween()"

=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5) + ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

This is the formula I found on
http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/

I had worked out a procedure of my own, but this one is way shorter -- although not easier :D

let's break it down:

=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5)

First we get the number before the comma, when dividing the number of days between two dates by 7. That gives us the number of entire weeks in the period.

Next, that number is multiplied by 5 and we have the basic number of weeks.

But because we did a Truncate, we lost a few days.

And that's part of the formula I'm taking my hat off for.. wow..

=ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

One day I'll be able to write constructions like these :)

Peter