2 of 2 people found this helpful
I have already done this in the above link. Can you just take a look at it?
Thanks and Regards
Thank you Ashish, I will take a look and work on it.
You are welcome. Mark it as correct answer if you get the required result.
Thanks a lot.
It was not easy but I finally managed to build a view which shows number of Working Days in a period using a table of holidays and [Start Date] & [End Date] parameters:
You may choose datetruncs other than months (quarters, weeks, etc.)
It only works if there are at least 2 holidays between Start & End dates.
Working Days in Period 9.0.twbx 52.7 KB
Thank you so much and I appreciate for the effort put by you and helping me out with the issue. This works perfectly!.
I have attached the wb again with the following change: The field counting holidays excludes holidays falling on Weekends.
Hello all! I've got workdays in the month by using this as a calculated field:
DATEDIFF("weekday", [Start Date],[End Date])
- 2* (DATEPART('week', [End Date]) -
DATEPART('week',[Start Date])) +
(IF DATENAME ('weekday', [End Date]) =
'Saturday' OR DATENAME ('weekday',[Start Date])=
'Sunday' THEN 0 ELSE 1 END)
and it works fine. I would like the ability to subtract 1 day from specific months for known holidays. Can someone help with where in this I would place, for example, an IF 'May' then -1 OR IF 'December' then -1, etc...?
Try this steps with the above calculated field which you have used for workdays.
Step 1: Create a excel sheet with the list of holidays you want to include. The screenshot of the holiday list is in the attachment below.
Step 2: Create a calculated filed named "new holiday" to get the count of the date. i.e . COUNT([F2]). F2 field can be seen in the screenshot.
Step 3: Blend F2 and the data source you are using.
[Number of Workdays]-
(IF [Sheet1 (Holiday list New 2016 - 2018)].[New holiday]>0 THEN [Sheet1 (Holiday list New 2016 - 2018)].[New holiday] ELSE 0 END)
1.png 49.4 KB