Calculate Weekdays excluding and Public Holidays in SAP BO WEB Intelligence

Hi Folks,

I have been wandering around to calculate Weekdays excluding Saturdays & Sundays and Public Holidays in WEBi, couldn’t find relevant post so publishing this – I am sure few of you will find it helpful. I was able to find Weekdays calculation though.

If one gets a list of holidays, DATE OBJECT in Webi either in Excel or Universe or BW – doesn’t really matter which country it belongs to. All we need, Start Date,  End Date & Public Holiday list – possibly all three in same format. If not, can be converted/formatted in WEBi anyway.

Please review below example where I cooked a simple dataset for Start Date,  End Date & Public  Holidays.

Start Date Range: 1st Sep 2018 to 30th Sep 2018

End Date Range:  1st Oct 2018 to 30 Oct 2018

Public Holidays Taken:  5th Oct 2018, 6th Oct 2018 and 15th Oct 2018 (note: 6th Oct is Weekend)

My desired O/P must omit 6th Oct from calculation being a Public Holiday on Weekends (hardly be the case for countries other than India 🙂 )

Calculating Weekdays ([WDs]): 

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

Calculate Weekdays excluding Public Holidays:  

=[WDs]-(If(FormatDate([PH];”ddd”) InList (“Sat”;”Sun”);0; If(FormatDate([PH];”ddd”)=””;0;1)))

Where, [PH] = list of public holidays

[Start Date] = From Date

[End Date]= To date

I hope you find this post simple and useful.