Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos
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.
Labels in this area