Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days without the Analysis Pack
Hi,
I'm trying to calculate what 13 working days are from a set date (cell a1), however i understand that the formulae for this the 'WorkDays' one needs the Analysis tool pack to function. This is the stumbling block as our organisation are not able/permitted to install this on the server. Does any one know another route i can take???? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days without the Analysis Pack
Hi sdg8481
See http://www.dicks-blog.com/archives/2...-addin-part-2/ -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, I'm trying to calculate what 13 working days are from a set date (cell a1), however i understand that the formulae for this the 'WorkDays' one needs the Analysis tool pack to function. This is the stumbling block as our organisation are not able/permitted to install this on the server. Does any one know another route i can take???? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days without the Analysis Pack
i dont think this funktion is depend of analysis too... but not sure
=IF(OR(TEXT(C2,"dddd")="sunday",TEXT(C2;"dddd")="s aturday"),"Weekend";"Workday") "Ron de Bruin" skrev: Hi sdg8481 See http://www.dicks-blog.com/archives/2...-addin-part-2/ -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, I'm trying to calculate what 13 working days are from a set date (cell a1), however i understand that the formulae for this the 'WorkDays' one needs the Analysis tool pack to function. This is the stumbling block as our organisation are not able/permitted to install this on the server. Does any one know another route i can take???? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days without the Analysis Pack
Hi, Thanks for you quick response. However i'm having a bit of trouble
understanding the code, please could you help by the way of the following example; I've copied your code as follows; =start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days))) But where do i put in my criteria? my start date is in B2, i want to add 13 working days, and my holidays are contained in cell reference C2:C7 "Ron de Bruin" wrote: Hi sdg8481 See http://www.dicks-blog.com/archives/2...-addin-part-2/ -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, I'm trying to calculate what 13 working days are from a set date (cell a1), however i understand that the formulae for this the 'WorkDays' one needs the Analysis tool pack to function. This is the stumbling block as our organisation are not able/permitted to install this on the server. Does any one know another route i can take???? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days without the Analysis Pack
Hi
First this is array formula (not Enter but Ctrl-Shift-Enter) http://www.cpearson.com/excel/array.htm start_date is a named cell with the start date days is a named cell with the working days holidays is a named range with holidays You can replace the named ranges with cell addresses if you want -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, Thanks for you quick response. However i'm having a bit of trouble understanding the code, please could you help by the way of the following example; I've copied your code as follows; =start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days))) But where do i put in my criteria? my start date is in B2, i want to add 13 working days, and my holidays are contained in cell reference C2:C7 "Ron de Bruin" wrote: Hi sdg8481 See http://www.dicks-blog.com/archives/2...-addin-part-2/ -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, I'm trying to calculate what 13 working days are from a set date (cell a1), however i understand that the formulae for this the 'WorkDays' one needs the Analysis tool pack to function. This is the stumbling block as our organisation are not able/permitted to install this on the server. Does any one know another route i can take???? Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days without the Analysis Pack
Absolutely Brilliant. Thank You very much, never used Arrays before thats
where i was going wrong. However one more thing if you would be so kind, is how/where would i incorporate and IF statement so that if start_date cell a2 = "","",otherwise do that formula, i've tried putting the =IF(A2="","",.......) but it doesn't seem to like it, any ideas. "Ron de Bruin" wrote: Hi First this is array formula (not Enter but Ctrl-Shift-Enter) http://www.cpearson.com/excel/array.htm start_date is a named cell with the start date days is a named cell with the working days holidays is a named range with holidays You can replace the named ranges with cell addresses if you want -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, Thanks for you quick response. However i'm having a bit of trouble understanding the code, please could you help by the way of the following example; I've copied your code as follows; =start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days))) But where do i put in my criteria? my start date is in B2, i want to add 13 working days, and my holidays are contained in cell reference C2:C7 "Ron de Bruin" wrote: Hi sdg8481 See http://www.dicks-blog.com/archives/2...-addin-part-2/ -- Regards Ron de Bruin http://www.rondebruin.nl "sdg8481" wrote in message ... Hi, I'm trying to calculate what 13 working days are from a set date (cell a1), however i understand that the formulae for this the 'WorkDays' one needs the Analysis tool pack to function. This is the stumbling block as our organisation are not able/permitted to install this on the server. Does any one know another route i can take???? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date (minus) date = working days diff | Excel Discussion (Misc queries) | |||
How do I modify the default working days in excel | Excel Discussion (Misc queries) | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
Is there a way to calculate business working days between dates i. | Excel Worksheet Functions | |||
Date Difference On Working Days | Excel Worksheet Functions |