Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
Hello -
I have employees that have different number of business days they work. I need to be able to calculate when the employee has utilized a specific number of business days specific to the days of the week they work and the number of days per week work. For example, if I have an employee that works 3 business days per week (Specifically M, W, and F), and I need to know the date this employee worked a total of 30 business days, is there a way to calcuate this date (which should be 6/9/06 if we use a start date of 4/3/06. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
This caters for holidays as well
=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+ SIGN(days)*(ROW(INDIRECT(" 1:"&ABS(days)*10))))={1,2,3})* ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kelly" wrote in message ... Hello - I have employees that have different number of business days they work. I need to be able to calculate when the employee has utilized a specific number of business days specific to the days of the week they work and the number of days per week work. For example, if I have an employee that works 3 business days per week (Specifically M, W, and F), and I need to know the date this employee worked a total of 30 business days, is there a way to calcuate this date (which should be 6/9/06 if we use a start date of 4/3/06. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
Sorry, meant
=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+ SIGN(days)*(ROW(INDIRECT(" 1:"&ABS(days)*10))))={2,4,6})* ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kelly" wrote in message ... Hello - I have employees that have different number of business days they work. I need to be able to calculate when the employee has utilized a specific number of business days specific to the days of the week they work and the number of days per week work. For example, if I have an employee that works 3 business days per week (Specifically M, W, and F), and I need to know the date this employee worked a total of 30 business days, is there a way to calcuate this date (which should be 6/9/06 if we use a start date of 4/3/06. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
Hi Bob -
The business I am does not cater to holidays. They are considered business days as well. Would I just take out the ",holidays, 0))" portion of the equation. This seems to be way over my head as I am not that familiar wtih formulas. "Bob Phillips" wrote: This caters for holidays as well =start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+ SIGN(days)*(ROW(INDIRECT(" 1:"&ABS(days)*10))))={1,2,3})* ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kelly" wrote in message ... Hello - I have employees that have different number of business days they work. I need to be able to calculate when the employee has utilized a specific number of business days specific to the days of the week they work and the number of days per week work. For example, if I have an employee that works 3 business days per week (Specifically M, W, and F), and I need to know the date this employee worked a total of 30 business days, is there a way to calcuate this date (which should be 6/9/06 if we use a start date of 4/3/06. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
Kelly,
Just create a range name called holidays, but leave it blank. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kelly" wrote in message ... Hi Bob - The business I am does not cater to holidays. They are considered business days as well. Would I just take out the ",holidays, 0))" portion of the equation. This seems to be way over my head as I am not that familiar wtih formulas. "Bob Phillips" wrote: This caters for holidays as well =start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+ SIGN(days)*(ROW(INDIRECT(" 1:"&ABS(days)*10))))={1,2,3})* ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kelly" wrote in message ... Hello - I have employees that have different number of business days they work. I need to be able to calculate when the employee has utilized a specific number of business days specific to the days of the week they work and the number of days per week work. For example, if I have an employee that works 3 business days per week (Specifically M, W, and F), and I need to know the date this employee worked a total of 30 business days, is there a way to calcuate this date (which should be 6/9/06 if we use a start date of 4/3/06. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
If start date is in A1 and positive number of business days in B1 =SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*3))-1)={2,4,6},A1+ROW(INDIRECT("1:"&B1*3))-1),B1) confirmed with CTRL+SHIFT+ENTER note that {2,4,6} refers to Mon, Wed and Fri. Adjust accordingly for other combinations -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=534245 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
It's a good formula, but it falls down if you go for just 2 days say
{2,4}because ROW(INDIRECT("1:"&B1*3)) creates a too small a comparison date range. You can allow for it, but it's a bit messy IMO =SMALL(IF(WEEKDAY(start_date+ROW(INDIRECT("1:"&num _days*(6-COUNT({2,4,6})))) -1)={2,4,6},start_date+ROW(INDIRECT("1:"&num_days*( 6-COUNT({2,4,6}))))-1),nu m_days) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "daddylonglegs" wrote in message news:daddylonglegs.26n32y_1145664902.5454@excelfor um-nospam.com... If start date is in A1 and positive number of business days in B1 =SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*3))-1)={2,4,6},A1+ROW(INDIRECT("1: "&B1*3))-1),B1) confirmed with CTRL+SHIFT+ENTER note that {2,4,6} refers to Mon, Wed and Fri. Adjust accordingly for other combinations -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=534245 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
I see what you mean, Bob. Perhaps it would be better to use =SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+B1*5)))={ 4,6},ROW(INDIRECT(A1&":"&A1+B1*5))),B1) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=534245 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
LOL. Or even
=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*5))-1)={2,4,6},A1+ROW(INDIRECT("1: "&B1*5))-1),B1) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... It's a good formula, but it falls down if you go for just 2 days say {2,4}because ROW(INDIRECT("1:"&B1*3)) creates a too small a comparison date range. You can allow for it, but it's a bit messy IMO =SMALL(IF(WEEKDAY(start_date+ROW(INDIRECT("1:"&num _days*(6-COUNT({2,4,6})))) -1)={2,4,6},start_date+ROW(INDIRECT("1:"&num_days*( 6-COUNT({2,4,6}))))-1), nu m_days) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "daddylonglegs" wrote in message news:daddylonglegs.26n32y_1145664902.5454@excelfor um-nospam.com... If start date is in A1 and positive number of business days in B1 =SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*3))-1)={2,4,6},A1+ROW(INDIRECT("1: "&B1*3))-1),B1) confirmed with CTRL+SHIFT+ENTER note that {2,4,6} refers to Mon, Wed and Fri. Adjust accordingly for other combinations -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=534245 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Dates Using Different Values for NETWORKDAYS
I think our posts crossed :-)
Bob "daddylonglegs" wrote in message news:daddylonglegs.26numm_1145700602.2883@excelfor um-nospam.com... I see what you mean, Bob. Perhaps it would be better to use =SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+B1*5)))={ 4,6},ROW(INDIRECT(A1&":"&A 1+B1*5))),B1) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=534245 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating cells based on dates | Excel Worksheet Functions | |||
Sumif using dates and positive negative values | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Linking dates and values | Excel Discussion (Misc queries) | |||
Convert three separate columns of values to dates | Excel Worksheet Functions |