Counting working Days in Specific Month between Two Dates

[buiya]

Good day,

Please excuse me if this has been posted elsewhere, the closest thing I could find was from a post in 2012 "[h=1]Counting Days in Specific Month between Two Dates".[/h]
The formula "=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1" provided in that thread works and was able to assist me halfway.

I need to show the number of working days in a date range for a specific month minus Mondays and holidays.
For example:

A1 is start range of Mar 23, 2015 and B1 is end date of April 17, 2015. I need to show in the next rows how many working days minus Monday's in that range for March and same thing for April.

March would be 5 (7 working days minus 2 Mondays)
April would be 11 (13 working days minus 2 Mondays)

Thanks in advance

 

[Caribeiro77]

Welcome to the board?
Do you want to count saturday's and sunday's?

 

[buiya]

Would like to exclude Saturday, Sunday and Mondays

 

[Caribeiro77]

Try:

=NETWORKDAYS.INTL(A1,B1,"1000011")

 

[buiya]

Thanks for the quick response! It looks accurate and is working (shows 16), however I need this number broken out to show how many of those days are in March and how many are in April.

 

[Caribeiro77]

Don't know if this is what you need, but try:

Book1
	A	B
1	23-03-2015	17-04-2015
2
3	March	5
4	April	11
Sheet1

Cell Formulas
Range	Formula
B3	=NETWORKDAYS.INTL(A1,EOMONTH(A1,0),"1000011")
B4	=NETWORKDAYS.INTL(EOMONTH(A1,0)+1,B1,"1000011")

 

[buiya]

OMG you're awesome! That does work! Thank you! Is there anyway you modify the formula to be agile so that if the date range goes across more than 2 months, say like ranges over 6 months to have each month broken out.

 

[buiya]

To clarify if i have a date range from 7-4-2015 to 12-6-2015, the formula is telling me how many days in April based on the criteria of excluding Sat, Sun & Mon, but it is adding the days in May and June instead of splitting out the days in May and June but if I change the end date range to a date in May it works.

 

[barry houdini]

=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1

You can amend that formula to only show working days based on your definition, so with the same setup, start date in A2, end date in B2 and the 1st of each month in C1 across, try this formula in C2, incorporating Caribeiro77's NETWORKDAYS.INTL suggestion, copied across and down if required

=MAX(0,NETWORKDAYS.INTL(MAX(C$1,$A2),MIN(EOMONTH(C$1,0),$B2),"1000011",$Z$2:$Z$10))

where Z2:Z10 contains your holiday dates, change as required

 

[buiya]

That worked!!! thank you both so much for your assistance!!!