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

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

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

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

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

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