10 Replies Latest reply on May 24, 2016 12:42 PM by thejash.vn

# Count the number of workdays in a month

Hi Everyone,

I got some issues with Calculating the Number of Business Days in a month . Below I have attached the screenshot of my workbook and I am also attaching the excel sheet with the list of holidays. I am getting some error with the calculation I have used. For example in the attachment 1.png , for the month of April there are 21 working days. But its displaying as 22. All the calculations I have used can be seen in the screenshot.

To give you a clear idea, Count of business days = Days in the calendar - weekends - Holidays.

Hope someone helps me out with this issue.

Thanks

• ###### 1. Re: Count the number of workdays in a month

Hi Thejash,

I have already done this in the above link. Can you just take a look at it?

Thanks and Regards

Ashish Chaudhari

3 of 3 people found this helpful
• ###### 2. Re: Count the number of workdays in a month

Thank you Ashish, I will take a look and work on it.

• ###### 3. Re: Count the number of workdays in a month

You are welcome. Mark it as correct answer if you get the required result.

Thanks a lot.

• ###### 4. Re: Count the number of workdays in a month

Hi,

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.

Cheers,

Łukasz

2 of 2 people found this helpful
• ###### 5. Re: Count the number of workdays in a month

Hi Lukasz.

Thank you so much and I appreciate for the effort put by you and helping me out with the issue. This works perfectly!.

• ###### 6. Re: Count the number of workdays in a month

I have attached the wb again with the following change: The field counting holidays excludes holidays falling on Weekends.

• ###### 7. Re: Count the number of workdays in a month

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...?

• ###### 8. Re: Count the number of workdays in a month

Hi Kyle,

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.

Step 4:

[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)

Cheers,

Thej

• ###### 9. Re: Count the number of workdays in a month

thejash.vn I do not see an attachment here. Thank you for your reply

• ###### 10. Re: Count the number of workdays in a month

Hi Kyle,

I am attaching the workbook and excel sheet with the list of holidays. Make sure you update the holidays sheet according to your requirement. Let me know if this works for you or not.

Cheers,

Thej