I have a column with a start date and another with a finish date for each row. I have columns to the right of that with headers for each month. I want a formula to tell me how many working days are contained in that range of start to finish in each month. Below is an example where I manually entered the answer I need excel to find with the formula. I need this to take out the holidays as well. Please help!
Take a look at NETWORKDAYS formula
I've got that part, I just can't figure out how to make it pull from only one month within that range.
enter this formula in d2 then drag down and across, after you have specified the range for [holidays]
=IF(MONTH(D$1)<MONTH($B2),0,IF(MONTH(D$1)>MONTH($C2),0,NETWORKDAYS(MAX(D$1,$B2),MIN(EOMONTH(D$1,0),$C2),[holidays])))
The formula works except where I'm going from one year into the next, then when I evaluate it comes up N/A when you see if the column date is greater than the finish date. I edited the attachment to show you the actual sheet
Last edited by britt@wjg; 05-17-2012 at 03:11 PM.
Remove the MONTH function from the inequalities and it should work. Here's the formula I used. Holiday is a named range where your holiday dates are.
=IF(D$1<$B2,0,IF(D$1>$C2,0,NETWORKDAYS(MAX(D$1,$B2),MIN($C2,DATE(YEAR(D$1),MONTH(D$1)+1,0)),Holidays)))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
This does not work either, now it "True"'s out to a value of zero on the greater than function.
This should also work
=MAX(0,NETWORKDAYS(MAX(L$1,$E35),MIN(EOMONTH(L$1,0),$F35)))*$J35
Audere est facere
Did you try my version? In L2 copied across and down
=MAX(0,NETWORKDAYS(MAX(L$1,$E2),MIN(EOMONTH(L$1,0),$F2)))*$J2
include holiday range here
=MAX(0,NETWORKDAYS(MAX(L$1,$E2),MIN(EOMONTH(L$1,0),$F2),holidays))*$J2
Last edited by daddylonglegs; 05-17-2012 at 04:04 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks