Distributing 6 Working Days Across Months

F

FarmAccountant

New Member
Joined
Mar 12, 2011
Messages
5
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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)),"")
 
Upvote 0
home

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!!! :)
dates.JPG


dates.JPG
 
Last edited:
Upvote 0
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!!!

dates.jpg
 
Upvote 0
I can't seem to do this in the time I have. Must take off for several hours,,
Good Luck.
Jim
 
Upvote 0
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
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?
 
Upvote 0
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:
Upvote 0
FarmAccountant said:
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!!!

dates.jpg
Click to expand...
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.
 
Upvote 0
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!) :)
 
Upvote 0
Ron Coderre said:
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!) :)
Click to expand...
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! :pray:
 
Upvote 0
You must log in or register to reply here.

Similar threads

welshraz
  • Solved
Calculate Number of Weekend Days That Passed in Current Month
Replies
2
Views
111
Fluff
Fluff
M
  • Question Question
Depreciation Schedule with Partial Months
Replies
1
Views
82
footoo
F
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
159
Domenic
D
J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
367
felixstraube
F
S
  • Solved
=Workday, first day being weekend
Replies
4
Views
385
saboh12617
S

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top