Excel Formula Help

[willyg76]

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month. I just can't get this figured out. HELP!

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

By the way, I'm using Excel 2007.

Thanks for your help!
Will

 

[benhlhs]

OK, I'll try. Here's what I have now. I want to put in only start date, end date, and rate. I want it to calculate how much we owe the guy for workdays per month, excluding holidays. The holidays are just listed below as example days in March, but I don't have that in the calc yet, b/c I'm not sure how to do it. My other problem is how to show "0" in the months the contractor is not working, rather than "#NUM!". Thanks, so much!!

A B C D E F ....
Jan Feb Mar Apr May Total
2 CONTRACTORS
3 JOE #NUM! 6,800 14,960 680 #NUM! #NUM!

11 Start Date End Date Rate
12 02/15/10 04/02/10 85.00

17 Holiday dates
18 3/17/2009
19 3/18/2009

Current formula in cel D3 is "=NETWORKDAYS(0,MIN($B12,D$1+31-DAY(D$1+31))-MAX($A12,D$1)+1)*8*$C12"

 

[benhlhs]

Sorry, I had it all lined up nicely, but when posted, it mushed together. Can I attached the spreadsheet??

 

[wsjackman]

set up a list of holidays or just refer to the cells appropriately

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	start date	end date	1/1/2009	2/1/2009	3/1/2009	4/1/2009	5/1/2009	6/1/2009	7/1/2009	8/1/2009	9/1/2009	10/1/2009	11/1/2009	12/1/2009
3	2/3/2009	5/7/2009	0	19	22	22	5	0	0	0	0	0	0	0
Sheet3

 

[benhlhs]

Thanks for your quick response. I set up my test data, exactly as you have with an associated holiday list, etc.

I'm getting a formula error regarding parentheses. When I add a closing paren at the end, it then says the formula containes an error. I copied and pasted your formula into my report, putting my data in the exact cells you have shown. Any thoughts?

I'm in MS2007, so don't know if that makes any difference. Thanks!

 

[wsjackman]

the problem was in how ">" and "<"" are treated in the forum submittals
I applogize I should have checked how it actually was sent.

I also have 2007

use the following formula and remove the spaces around the > and <

=IF(OR($F3 > =I$2,$G3 < H$2),0,NETWORKDAYS(MEDIAN(H$2,I$2-1,$F3),MEDIAN(H$2,I$2-1,$G3),holiday_list))

 

[benhlhs]

OMG..it worked! You are incredible!!! Thank you, thank you, thank you!!!

Can you help me understand the formula a little though? In the networkdays and median section, what is the median function doing?

Thanks, again!!

 

[benhlhs]

Also, how do you modify the cell for December, as there is not future month to put in the calc?

 

[wsjackman]

the median function in this case takes the middle of three options. in this case We are working with beginning of month, end of month, and some other day.

there are several options
the full month may be is the time frame
the time frame may start in the month,
the time frame may end in the month
the time frame may both start and end in the month
the month may not be in the time frame

the networkdays() looks at a beginning date, an end date and holidays
If the beginning date is before the beginning of the month you want to use the first day of the month as the beginning date and use the start of time frame if not. similarly you want to use the last day of the month or the end of time frame for the end date in networkdays(). the median does this with just one formula

the formula could also have been written as

=IF(OR($F3 > =I$2,$G3 < H$2),0,NETWORKDAYS(max(H$2,$F3),Min(I$2-1,$G3),holiday_list))

But the median() does have significant advantages in other similar applications, so I use it to remind miyself about it

 

[wsjackman]

Also, how do you modify the cell for December, as there is not future month to put in the calc?

just continue the beginning of month series as far as you need it

 

[wsjackman]

or you could convert the formula
to

=IF(OR($F3 > =I$2,$G3 < H$2),0,NETWORKDAYS(MEDIAN(H$2,date(year(H$2),Month(H$2)+1,0),$F3),MEDIAN(H$2,date(year(H$2),Month(H$2)+1,0),$G3),holiday_list))