Business days calculation

building reports webintelligence
1

I am trying to calculate no of working days between the satrt date and end date using this formula but i am not getting the expected data

(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))

pathu (BOB member since 2010-08-05)

2

What’s that?

Marfi :poland: (BOB member since 2006-12-18)

3

And again does this also capture the holidays? It’s better to have a calendar table with a flag that is set up to indicate the business days.

Jansi :india: (BOB member since 2008-05-12)

4

I dont about that number . i just used it blindly as a formula.
I dont have a cal table in the database and they cannot add also it seems.

pathu (BOB member since 2010-08-05)

5

These blog posts will explain:
http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/
http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/2/

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

Go through that blogs

If you are using XI R2 the formula won’t work perfectly.

last section of the blog :

Help Text Bug
As an aside, the help text for the DayNumberOfWeek() function in XIR2 is incorrect. It states:

Web Intelligence always treats Sunday as the first day of the week

Based on my testing this is incorrect. Sunday is always day 7, and Monday is always day 1. This factors into the math used in this post. If that ever changes, the formulas provided in this post will no longer work.

av_deepu :india: (BOB member since 2010-08-11)

7

This post may help:

(See bottom of thread)

:+1:

David P. :us: (BOB member since 2010-11-09)