Distributing 6 Working Days Across Months

[FarmAccountant]

I have 2 column headings for a beg and end date. I have 12 column headings for all 12 months. I need to type in the beg/end date and then have excel put in the # of working days (6 INCLUDING Saturday) in the related months. I've been able to use one for 5 days, but I NEED 6. Any help would get GREAT! Thanks. Using Excel 2010

 

[jim may]

Limited to 12 columns with HTML Maker sw, but how 'bout this??????

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Beg	End	Jan	Feb	Mar	Apr	May	Jun	Jly	Aug	Sep	Oct
2	3/1/2011	3/15/2011			13
3	1/1/2011	1/31/2011	26
4	2/1/2011	2/28/2011		24
5	3/1/2011	3/31/2011			27
6	4/1/2011	4/30/2011				26
7	5/1/2011	5/31/2011					26
8	6/1/2011	6/30/2011						26
9	7/1/2011	7/31/2011							26
10	8/1/2011	8/31/2011								27
11	9/1/2011	9/30/2011									26
12	10/1/2011	10/31/2011										26
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(MONTH($A2)=COLUMN()-2,NETWORKDAYS($A2,$B2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A2&":"&$B2)))=7)),"")

 

[FarmAccountant]

Thank you so much!!! But I need something a little different. I think this example with explain a little better what I'm trying to do. I alreaady figured out how to do column C. I need to be able to change the start/end date and have Column E through R calculate the working days (including Saturday). AGAIN... THANK YOU SO MUCH....I"ve been pulling my hair out at work over this!!!

 

[FarmAccountant]

Thank you so much!!! But I need something a little different. I think this example with explain a little better what I'm trying to do. I alreaady figured out how to do column C. I need to be able to change the start/end date and have Column E through R calculate the working days (including Saturday). AGAIN... THANK YOU SO MUCH....I"ve been pulling my hair out at work over this!!!

 

[jim may]

I can't seem to do this in the time I have. Must take off for several hours,,
Good Luck.
Jim

 

[Ron Coderre]

Using your posted example...

this formula begins the count of non-Sundays from the A2:B2 date range that are in the month referenced above the formula

E2: =SUMPRODUCT((TEXT(ROW(INDEX(A:A,$A2):INDEX(A:A,$B2)),"yyyymm")=TEXT(
E1,"yyyymm"))*(WEEKDAY(ROW(INDEX(A:A,$A2):INDEX(A:A,$B2)))<>1))

Copy that formula across through R2

Using your example these are the results for Jan through May:
<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=312><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=4 width=61><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=68 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=61 align=right>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=61 align=right>27</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=61 align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=61 align=right>0</TD></TR></TBODY></TABLE>

Is that something you can work with?

 

[Ron Coderre]

EDITED to tweak the formula slightly
Another (shorter) approach...also a regular formula:

E2: =IF(MAX(MIN(F1-1,$B2)-MAX($A2,E1)+1,0),SUMPRODUCT(--(WEEKDAY(ROW(
INDEX($A:$A,MAX($A2,E1)):INDEX($A:$A,MIN(F1-1,$B2))))<>1)),0)

Copy that formula across through R2.

Also, for clarity and consistency, my first formula should probably lock Col_A:

E2: =SUMPRODUCT((TEXT(ROW(INDEX($A:$A,$A2):INDEX($A:$A,$B2)),"yyyymm")=TEXT(
E1,"yyyymm"))*(WEEKDAY(ROW(INDEX($A:$A,$A2):INDEX($A:$A,$B2)))<>1))

 

[T. Valko]

Thank you so much!!! But I need something a little different. I think this example with explain a little better what I'm trying to do. I alreaady figured out how to do column C. I need to be able to change the start/end date and have Column E through R calculate the working days (including Saturday). AGAIN... THANK YOU SO MUCH....I"ve been pulling my hair out at work over this!!!

Here's another one...

Entered in E2 and copied across as needed.

=IF(AND(E1>=$A2-DAY($A2)+1,E1<=$B2),SUM(INT((WEEKDAY(MAX($A2,E1)-{1,2,3,4,5,6},2)+MIN($B2,EOMONTH(E1,0))-MAX($A2,E1))/7)),"")

Note that the EOMONTH function requires the Analysis ToolPak add-in be installed if you're using a version of Excel prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the EOMONTH function. It'll tell you how to fix the problem.

 

[Ron Coderre]

Ya know...I thought about that approach...but I dread being asked how it works. (It would take me forever to figure out how to explain it!)

 

[T. Valko]

Ya know...I thought about that approach...but I dread being asked how it works. (It would take me forever to figure out how to explain it!)

Shhhh!

I was going to use a similar method to Jim's but didn't really want to use the volatile INDIRECT to "build" an array of dates.

I'd like to see what Barry H. might be able to come up with. He's a real date guru! ray: