If 6 working days in a week 2 holidays....networking days ?

[Ajinkya]

Hi Friends,


If i have 5 working days in a week then i can easily get net working days with the formula

=NETWORKDAYS...


but if i have 6 working days in a week and suppose 3 holidays are coming in the week, then how come i can get networking days for following period?


01-July-2012

31-July-2012


Thanks

 

[Faseeh]

Hi Ajinkya,


Kindly try this one:

=TEXT(A2-A1,"#")-(WEEKNUM(A2)-WEEKNUM(A1))*2-3

A1 = Start Date & A2 = End Date Format cell as General...


Regards,

 

[Ajinkya]

Hi Faseeh,

Thanks for feedback but the output is coming 19, i think its not valid,


lets check..


A1= 1-Jul-2012

A2= 31-Jul-2012


as per you, formula is...

=TEXT(A2-A1,"#")-(WEEKNUM(A2)-WEEKNUM(A1))*2-3


if i cross check the output


July has 31 days

-5 sundays

-3 holidays

total 8 non working days


i.e. 31-8= 23 Days


formula should give 23 working days as output


Thanks

 

[shrivallabha]

If you have Excel 2010 then you can use a built in function:

NETWORKDAYS.INTL. See here:

http://office.microsoft.com/en-us/excel-help/networkdays-intl-function-HA010354379.aspx


And yours truly had also tried a few things:

http://www.mrexcel.com/forum/showthread.php?594833-NETWORKDAYS-function-How-to-include-Saturdays-as-workdays

 

[Faseeh]

Hi Ajinkya,


A mistake from me, try this

=TEXT(A2-A1,"#")-(WEEKNUM(A2)-WEEKNUM(A1))*1-3


Or 


=TEXT(A2-A1,"#")-(WEEKNUM(A2)-WEEKNUM(A1))*HolidaysPerWeek-ExtraHolidays

Regards,

 

[Ajinkya]

thanks faseeh,


just wanna clear 1 doubt, In your following formula "#" indecates what?


=TEXT(A2-A1,"#")-(WEEKNUM(A2)-WEEKNUM(A1))*1-3


@ shrivallabha: Im learning in excel 2007, thanks for addition info.


thanks

 

[shrivallabha]

Faseeh's formula incidently works in that case and few more.

[pre]

Start Date	End Date	Barry	Faseeh
1/1/2012	1/31/2012	26	26
2/1/2012	2/29/2012	25	24
3/1/2012	3/31/2012	27	26
4/1/2012	4/30/2012	25	25
5/1/2012	5/31/2012	27	26
6/1/2012	6/30/2012	26	25
7/1/2012	7/31/2012	26	26
8/1/2012	8/31/2012	27	26
9/1/2012	9/30/2012	25	24
10/1/2012	10/31/2012	27	26
11/1/2012	11/30/2012	26	25
12/1/2012	12/31/2012	26	25

[/pre]

You can use Barry's formula if you want to:

=A2-A1-INT((1-WEEKDAY(A2)+A2-A1)/7)

 

[Faseeh]

Hi shrivallabha,


You are right!! I have not checked it for something else then July, it is not working for any thing else.


Thanks,

Faseeh

 

[ninad7]

try the Networkdays.intl formula OR


http://www.cpearson.com/Excel/BetterWorkday.aspx

 

[shrivallabha]

You can also try ubiquitous SUMPRODUCT with following logic for counting days:


=SUMPRODUCT((WEEKDAY(A2+(ROW($A$1:INDEX(A:A,A2-A1+1))-1))<>1)+0)


Do not change $A$1:INDEX(A:A part of the argument.

<>1 = Sunday, 2 = Monday etc. so you can use this function for excluding any day you wish!


Edit: @ninad: Ajinkya can't use .INTL formula as he uses Excel 2007.