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.