# Distributing 6 Working Days Across Months

#### FarmAccountant

##### New Member
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

Last edited:

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### jim may

##### Well-known Member
Limited to 12 columns with HTML Maker sw, but how 'bout this??????
Excel Workbook
ABCDEFGHIJKL
1BegEndJanFebMarAprMayJunJlyAugSepOct
23/1/20113/15/201113
31/1/20111/31/201126
42/1/20112/28/201124
53/1/20113/31/201127
64/1/20114/30/201126
75/1/20115/31/201126
86/1/20116/30/201126
97/1/20117/31/201126
108/1/20118/31/201127
119/1/20119/30/201126
1210/1/201110/31/201126
Sheet1
Excel 2007
Cell Formulas
RangeFormula
C2=IF(MONTH(\$A2)=COLUMN()-2,NETWORKDAYS(\$A2,\$B2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(\$A2&":"&\$B2)))=7)),"")

#### FarmAccountant

##### New Member

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!!!

Last edited:

#### FarmAccountant

##### New Member
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

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

#### Ron Coderre

##### MrExcel MVP

this formula begins the count of non-Sundays from the A2:B2 date range that are in the month referenced above the formula
Code:
``````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

##### MrExcel MVP
EDITED to tweak the formula slightly
Another (shorter) approach...also a regular formula:
Code:
``````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:
Code:
``````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))``````

Last edited:

#### T. Valko

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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:

Replies
3
Views
215
Replies
3
Views
155
Replies
5
Views
76
Replies
1
Views
81
Replies
2
Views
88

1,190,740
Messages
5,982,684
Members
439,790
Latest member
jonaust

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back