Counting working days between two dates

building reports desktopintelligence
1

If you have two dates (dimensions), say Order Date and Delivery Date, how can you calculate the number of working days in between the two. It is necessary to eliminate bank holidays as well as weekends. It should ideally be usable in different countries (so different bank holidays). I wondered if it might be possible using a simple calendar maintained in Excel where each day is marked as working or holiday.

Thanks

Steve Belgium (BOB member since 2005-02-09)

2

Hi,

Try the Days Between function, DaysBetween(date 1; date 2).

The data types of the objects have to dates.

Hope this helps,

Steve Bickerton

SteveBickerton :canada: (BOB member since 2002-08-15)

3

Thanks, Steve, but that gives the total number of days, not the working days :cry:

Steve Belgium (BOB member since 2005-02-09)

4

Hi,

It exists a way of calculating the number of working days through 3 different variables, but not including bank days. I guess the only for this last one would be a calendar stored in a table but filled manually.

If you fill it manually I wish you good luck because it will be a very long work :!:

Grodan :fr: (BOB member since 2004-12-29)

5

Merci Grodan

Filling in the calendar is not a problem. It is a simple spreadsheet with a column for the date and a column for the status of the day (working or non-working). For one country it takes about 10 minutes to do it for a year (with copy/paste for the 5 working days and the weekends, followed by manual addition of about a dozen holidays). I just don’t know how to use the spreadsheet to effect the count in BO…

Steve[/quote]

Steve Belgium (BOB member since 2005-02-09)

6

Hi -

There probably are many ways of doing this ! Here’s one suggestion:

If you have a calendar table like this … you could add a column to show the working day number of year.

This would just be a sequentially assigned value that doesn’t go up for non-working day.

To compute the number of working days between you would need to subtract the working day number of year instead of using the days between function.

Deb Franke

dfranke :us: (BOB member since 2002-08-28)

7

Deb,
Thanks - I’ll see if I can do something with that.
Steve

Steve Belgium (BOB member since 2005-02-09)

8

The Excel sheet is an option, but what about loading it into a table in your DB ? With such a table you could insert it into your universe and then all users could use it as well

Grodan :fr: (BOB member since 2004-12-29)

9

What a useful forum… :smiley:

Thanks all, I found a solution based on Deb’s idea. Guess I’ll play with it a bit first though before I try to persuade our database guardians to “pollute” their DB with my calendar tables…

Steve Belgium (BOB member since 2005-02-09)