• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation of Working days for two regions

K

KVAMSHE

New Member
Hi,


Could you please provide me a DAX formula (powerpivot/measure) wherein i can calculate no.of workings days excluding saturdays, Sundays & Holidays based on two different regions.


Thanks in advance.


Regards,

Vamshe
 
I don't know exactly what you mean by a DAX formula, but have you looked at the NETWORKDAYS function? Automatically excludes the weekend, and you can give it a list of holidays.
 
Hi Luke,

I tried NETWORKDAYS but not get exactly might be some formula error. For your reference am herewith providing two regions holiday list below. Could you please build a formula to calculate work days in a month excluding saturdays, Sundays & holidays:

[pre]
Code: 
HDate	             Holiday	       Region
26-Jan-12	Republic Day 	      Chennai
06-Apr-12	Good Friday	      Chennai
01-May-12	May Day	               Chennai
15-Aug-12	Independence Day	      Chennai
20-Aug-12	Ramzan	               Chennai
19-Sep-12	Vinayaga chathurthi     Chennai
02-Oct-12	Gandhi Jayathi	       Chennai
23-Oct-12	Ayudha pooja	       Chennai
13-Nov-12	Diwali	                Chennai
25-Dec-12	Christmas	       Chennai
26-Jan-12	Republic Day 	       Gurgaon
08-Mar-12	Holi	                Gurgaon
06-Apr-12	Good Friday	       Gurgaon
10-Aug-12	Janmastahmi	       Gurgaon
15-Aug-12	Independence Day        Gurgaon
20-Aug-12	Ramzan	                Gurgaon
02-Oct-12	Gandhi Jayathi	       Gurgaon
24-Oct-12	Dussehra	        Gurgaon
13-Nov-12	Diwali	                Gurgaon
14-Nov-12	Govardhan pooja	        Gurgaon
[/pre]
 
If you are wanting to choose 1 of the 2 reguins, this should work. Assuming your lists are in columns A:C, something like this array formula will work:

=NETWORKDAYS(StartDay,EndDay,IF(C2:C20="Chennai",A2:A20,0))

Make sure that you confirm array formulas using Ctrl+Shift+Enter, not just Enter.


If you did intend to use both region's holidays, it shouldn't make any difference. You can just include all the holidays in the formula, even if there are duplicates.
 
You must log in or register to reply here.
Back
Top