|
SOS:Number of Working days per week/month
All,
I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. |
SOS:Number of Working days per week/month
check out the NETWORKDAYS function. I can't remember for sure, but this may
be one of the functions that pre-2007 versions of excel need you to install the analysis toolpak. "LABKHAND" wrote: All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. |
SOS:Number of Working days per week/month
B Lynn,
I know about the NETWORKDAYS function, my problem is that I do not know how to modify my formula to use this function. "B Lynn B" wrote: check out the NETWORKDAYS function. I can't remember for sure, but this may be one of the functions that pre-2007 versions of excel need you to install the analysis toolpak. "LABKHAND" wrote: All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. |
SOS:Number of Working days per week/month
Seems incredibly unwieldy, I will try and get a simplet formula, but add
this to your formula -SUMPRODUCT(--(ISNUMBER(MATCH(holidays,ROW(INDIRECT(MIN(DATE(YEA R($B$1),MONTH($B$1)+1,0),($B$1+($A3-1)*7-WEEKDAY($B$1+($A3-1)*7)+1))&":"&MIN(DATE(YEAR($B$1),MONTH($B$1)+1,0) ,($B$1+($A3)*7-WEEKDAY($B$1+($A3)*7))))),0)))) -- HTH Bob "LABKHAND" wrote in message ... All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. |
SOS:Number of Working days per week/month
OK, sorry to have underestimated your familiarity with the available
functions. Can you please clarify one part of your question? "WORKING days per each week (1-6)" - does that mean you're trying to count each week as having 6 workdays? Otherwise I don't get what you mean by the "1-6". "LABKHAND" wrote: B Lynn, I know about the NETWORKDAYS function, my problem is that I do not know how to modify my formula to use this function. "B Lynn B" wrote: check out the NETWORKDAYS function. I can't remember for sure, but this may be one of the functions that pre-2007 versions of excel need you to install the analysis toolpak. "LABKHAND" wrote: All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. |
SOS:Number of Working days per week/month
B Lynn,
I am trying to come up with the following data. If i enter 1/1/2010 in cell B1 then the working days per week for this month should be: WK#1 (0 working days since 1/2 was a holiday) WK#2 (5 working days) WK#3 (5 working days) WK#4 (4 working days since 1/18 was a holiday) WK#5 (5 working days) WK#6 (0 working days) I hope you see what I am doing. THX "B Lynn B" wrote: OK, sorry to have underestimated your familiarity with the available functions. Can you please clarify one part of your question? "WORKING days per each week (1-6)" - does that mean you're trying to count each week as having 6 workdays? Otherwise I don't get what you mean by the "1-6". "LABKHAND" wrote: B Lynn, I know about the NETWORKDAYS function, my problem is that I do not know how to modify my formula to use this function. "B Lynn B" wrote: check out the NETWORKDAYS function. I can't remember for sure, but this may be one of the functions that pre-2007 versions of excel need you to install the analysis toolpak. "LABKHAND" wrote: All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. |
SOS:Number of Working days per week/month
Bob,
I tried your formula but it is not working since I get 1 working day for the second week of Jan 2010. Thanks "Bob Phillips" wrote: Seems incredibly unwieldy, I will try and get a simplet formula, but add this to your formula -SUMPRODUCT(--(ISNUMBER(MATCH(holidays,ROW(INDIRECT(MIN(DATE(YEA R($B$1),MONTH($B$1)+1,0),($B$1+($A3-1)*7-WEEKDAY($B$1+($A3-1)*7)+1))&":"&MIN(DATE(YEAR($B$1),MONTH($B$1)+1,0) ,($B$1+($A3)*7-WEEKDAY($B$1+($A3)*7))))),0)))) -- HTH Bob "LABKHAND" wrote in message ... All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. . |
SOS:Number of Working days per week/month
I get 2 7 7 7 7 1 for January.
-- HTH Bob "LABKHAND" wrote in message ... Bob, I tried your formula but it is not working since I get 1 working day for the second week of Jan 2010. Thanks "Bob Phillips" wrote: Seems incredibly unwieldy, I will try and get a simplet formula, but add this to your formula -SUMPRODUCT(--(ISNUMBER(MATCH(holidays,ROW(INDIRECT(MIN(DATE(YEA R($B$1),MONTH($B$1)+1,0),($B$1+($A3-1)*7-WEEKDAY($B$1+($A3-1)*7)+1))&":"&MIN(DATE(YEAR($B$1),MONTH($B$1)+1,0) ,($B$1+($A3)*7-WEEKDAY($B$1+($A3)*7))))),0)))) -- HTH Bob "LABKHAND" wrote in message ... All, I am trying to figure out number of WORKING days per each week (1-6) of each month excluding holidays. I have set up a sheet as follows: B1=1/1/2010 then I have weeks#1 through 6 as a label in cells A3 through A8 in cell B3 I have the following formula: =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula is dragged to populate cells B4 through B8. I also have a named range defined for FY10_Holidays The above formula works, but does not excludes hoildays from the total number of days per week. Do you know how can I modify the formula to exclude holidays? Thanks for your help. . |
SOS:Number of Working days per week/month
LABKHAND;695295 Wrote: B Lynn, I am trying to come up with the following data. If i enter 1/1/2010 in cell B1 then the working days per week for this month should be: WK#1 (0 working days since 1/2 was a holiday) WK#2 (5 working days) WK#3 (5 working days) WK#4 (4 working days since 1/18 was a holiday) WK#5 (5 working days) WK#6 (0 working days) I hope you see what I am doing. THX You say:"WK#1 (0 working days since 1/2 was a holiday)" Since the 2nd January 2010 was a Saturday, does this mean that your working week is NOT normally Mon-Fri? If it is Mon-Sat, the following will not work since it uses Excel's Networkdays function which excludes Sat and Sun from the working week. Hopefully it was a typo for "1/1 was a holiday" in which case the following gives the same results. You use a formula like this in B3: =WorkingDays(YEAR($B$1),1,A3,FY10_Holidays) and copy down. It works similarly to Networkdays in that the last argument (FY10_Holidays) can be left out. The first argument is the year, either use '2010' or as I've done here, calculated the year from cell B1 The second argument is the month (1 to 12), or again you can calculate it from a date elsewhere. The third argument is the week number in the month (1 to 6) or again, you can get that number from another cell. Now none of this will work without the user-defined function behind it, that is, until you put the following code into a standard code module: VBA Code: -------------------- Function WorkingDays(TheYear, TheMonth, TheMonthWkNo, Optional Holidays) WorkingDays = "Error!" On Error GoTo LeaveNow Dim StartDate As Date, EndDate As Date WeekNumofDay1ofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth, 1)) WeekNumofLastDayofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth + 1, 0)) WeekNumsInTheMonth = WeekNumofLastDayofTheMonth - WeekNumofDay1ofTheMonth + 1 If TheMonthWkNo <= WeekNumsInTheMonth Then 'checks for a non-existent week number ActualWeekNo = TheMonthWkNo + WeekNumofDay1ofTheMonth - 1 myDate = DateSerial(TheYear, TheMonth, 0) Do myDate = myDate + 1 Loop Until Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth StartDate = myDate Do While Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth myDate = myDate + 1 Loop EndDate = myDate - 1 If IsMissing(Holidays) Then WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate) Else WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate, Holidays) End If End If LeaveNow: End Function -------------------- -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416 http://www.thecodecage.com/forumz |
SOS:Number of Working days per week/month
|
SOS:Number of Working days per week/month
p45cal,
Thanks, yes, It was a typo and I meant that 1/1/2010 was a holiday. Your solution works partially. for January, I copied your instructions and it gives me the right answers. But when I try to copy these to the adjescent cells Excel hangs and I kill it. I also tried copying the "=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)" to adjesent cell (C3) in which i got an "ERROR!" msg in teh cell. What is wrong? "p45cal" wrote: LABKHAND;695295 Wrote: B Lynn, I am trying to come up with the following data. If i enter 1/1/2010 in cell B1 then the working days per week for this month should be: WK#1 (0 working days since 1/2 was a holiday) WK#2 (5 working days) WK#3 (5 working days) WK#4 (4 working days since 1/18 was a holiday) WK#5 (5 working days) WK#6 (0 working days) I hope you see what I am doing. THX You say:"WK#1 (0 working days since 1/2 was a holiday)" Since the 2nd January 2010 was a Saturday, does this mean that your working week is NOT normally Mon-Fri? If it is Mon-Sat, the following will not work since it uses Excel's Networkdays function which excludes Sat and Sun from the working week. Hopefully it was a typo for "1/1 was a holiday" in which case the following gives the same results. You use a formula like this in B3: =WorkingDays(YEAR($B$1),1,A3,FY10_Holidays) and copy down. It works similarly to Networkdays in that the last argument (FY10_Holidays) can be left out. The first argument is the year, either use '2010' or as I've done here, calculated the year from cell B1 The second argument is the month (1 to 12), or again you can calculate it from a date elsewhere. The third argument is the week number in the month (1 to 6) or again, you can get that number from another cell. Now none of this will work without the user-defined function behind it, that is, until you put the following code into a standard code module: VBA Code: -------------------- Function WorkingDays(TheYear, TheMonth, TheMonthWkNo, Optional Holidays) WorkingDays = "Error!" On Error GoTo LeaveNow Dim StartDate As Date, EndDate As Date WeekNumofDay1ofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth, 1)) WeekNumofLastDayofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth + 1, 0)) WeekNumsInTheMonth = WeekNumofLastDayofTheMonth - WeekNumofDay1ofTheMonth + 1 If TheMonthWkNo <= WeekNumsInTheMonth Then 'checks for a non-existent week number ActualWeekNo = TheMonthWkNo + WeekNumofDay1ofTheMonth - 1 myDate = DateSerial(TheYear, TheMonth, 0) Do myDate = myDate + 1 Loop Until Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth StartDate = myDate Do While Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth myDate = myDate + 1 Loop EndDate = myDate - 1 If IsMissing(Holidays) Then WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate) Else WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate, Holidays) End If End If LeaveNow: End Function -------------------- -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416 http://www.thecodecage.com/forumz . |
SOS:Number of Working days per week/month
Well, it's difficult to say. Examine the formula in the new cell by selecting it and pressing F2, this will highlight the cells it's referring to. Are they the cells you expect? Also: a slight change in the function to the line: VBA Code: -------------------- If TheMonthWkNo <= WeekNumsInTheMonth Then -------------------- to: VBA Code: -------------------- If TheMonthWkNo <= WeekNumsInTheMonth And TheMonthWkNo 0 Then -------------------- will make it return an error rather than a misleading result if someone tries to use the zeroth week of the month. LABKHAND;696872 Wrote: p45cal, Thanks, yes, It was a typo and I meant that 1/1/2010 was a holiday. Your solution works partially. for January, I copied your instructions and it gives me the right answers. But when I try to copy these to the adjescent cells Excel hangs and I kill it. I also tried copying the "=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)" to adjesent cell (C3) in which i got an "ERROR!" msg in teh cell. What is wrong? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416 http://www.thecodecage.com/forumz |
SOS:Number of Working days per week/month
p45cal,
IT WORKED!!! It was my mistake since the month number was a constant as part of your formula and I was not changing it for the other columns. I fixed it now. Thanks for all your help. I send yo u an email with a second question I had. Would you be able to help me out on that? Did you see an email from me? Thanks "p45cal" wrote: Well, it's difficult to say. Examine the formula in the new cell by selecting it and pressing F2, this will highlight the cells it's referring to. Are they the cells you expect? Also: a slight change in the function to the line: VBA Code: -------------------- If TheMonthWkNo <= WeekNumsInTheMonth Then -------------------- to: VBA Code: -------------------- If TheMonthWkNo <= WeekNumsInTheMonth And TheMonthWkNo 0 Then -------------------- will make it return an error rather than a misleading result if someone tries to use the zeroth week of the month. LABKHAND;696872 Wrote: p45cal, Thanks, yes, It was a typo and I meant that 1/1/2010 was a holiday. Your solution works partially. for January, I copied your instructions and it gives me the right answers. But when I try to copy these to the adjescent cells Excel hangs and I kill it. I also tried copying the "=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)" to adjesent cell (C3) in which i got an "ERROR!" msg in teh cell. What is wrong? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416 http://www.thecodecage.com/forumz . |
| All times are GMT +1. The time now is 06:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com