How to calculate Working days in Cognos-Explanation

I have been reportedly asked by many BI Authors on how to create a Cognos Report between 2 days which has only working days and is also a frequent question on many IT Cognos Forums. So i thought i will explain in detail.

Many BI Authors are stuck at the calculation of number of working days if the company for which the reports being generated have holidays not only on saturdays and sundays. Suppose, you are working for a company in US or UK you have working days strictly on saturdays and sundays. So you can subtract these two days from the total number of days. But is this sufficient?
No. Its not sufficient. A company can have holidays not only saturdays and sundays but also on other public holidays. Now what if you company is expanding to some countries where the weekend starts on friday?



So, What would be the solution? 
Yes there is a solution, not from the Reporting side, but from ETL side. They have to populate the table that you are using for reporting with one more field for putting a day if it is a working day. A simple flag with 'Y' for Working day and 'N' for non working day would do.

Now, what do we do if there are no public holidays except weekends(saturdays and sundays) and the value you want is approximately?
This is a no brainer. You can simply go to the report. Drag and drop two date prompts which take the period start date and period end date ( these 2 dates are the dates between which you have to record number of holidays). Now, just calculate the difference between them which gives the total number of days between these 2 days.

Now this value should be divided by 7 (total number of days in a week) and you get number of weeks and multiply by 2 (for every 2 days in a week sunday and saturday). This will give you total number of weekends.
(start date - end date)/7 x2

Now here is the stumble. we have to know when is the period end date falling and the start date as well. Since if the period end date starts on saturday. then our values might come with  1 or 2 days less. So be careful in calculating the days. we can approximately give the solution but not perfectly. So its better if we go with the ETL guys and ask them to populate one more table for holidays. All the company's are doing the same and you can do the same.

After all, Cognos Business Intelligence is just a software not a human. :)

~Urs Krish