So do I Stuart. What is it about 2000 that you think doesn't work with it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
Bob

we apear to have hit a problem. i was unaware that our customer is only
using office 2000 is there anyway we can adapt the formulae to just use
the
technologies available in excel 2000?

"Stuart" wrote:

thanks bob it was part of my excelk i just went to tools then addins and
ticked both boxes you need the normal and the vba ticked ill try this at
work
on monday thank you for your help.

"Bob Phillips" wrote:

The Analysis Toolpak is part of Excel, it just needs to be installed
from
the Excel CD. Are you sure it is not installed, jut not loaded
(InsertAddins, and check it)

If your IT guys are not willing to do that (why not? - challenge
them),
then you could use


=IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6}
)-A2+8)/7))-

SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*(
holidays<=(IF(B2="",TODAY(),B2)))))

replace holidays with a range of dates for holidays, such as H1:H10,
(even
if you have none)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
firstly i dont have analysis toolpack which is probably why it didnt
work
and
even if i knew where to get it from we arent allowed to install new
software
at our work and the customer wont want to install new software just
to
read
there reports. is there any other way round this problem?

"Bob Phillips" wrote:


In your workbook, use the formula that I gave and in the first
row,
change
onsite_date to A2, return_date to B2.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
i dont fully understand go to
http://homepage.ntlworld.com/stuartb...e/Downtime.xls
make
the
needed adjustments and send modified file to
as this will be easier that
way ill
be
able
to see what you did. would you guys mind?

"Bob Phillips" wrote:

You don't need a macro for this, a simple formula will work




=IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET
WORKDAYS(onsite_date,return_date)))

it uses NETWEORKDAYS which is part of the Analysis Toolpak, so
that
needs to
be installed.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
I work for a company in Hertford now. We are an accident
management
company
as part of our services we provide our fleet customers with
weekly,
biweekly,
monthly or even daily reports. one of the pieces of
information in
these
reports is the downtime which is how long the vehicle has
been in
the
bodyshop. I have attached an working example to this to this
email
it
uses
an
excel formulae to calculate the difference between the 2
dates.
now
then
what
I want to know from you is how to improve on this formulae.

I need it to not only work out the difference between the 2
dates
but
if a
onsit date is given but no returned date it will show how
many
days
its
been
from onsite date to today's date. also if returned date is
given
but
no
onsite date it should return a blank value as our customers
don't
want
to
see
"#VALUE" show up anywhere. lastly this formulae is limited
in that
our
bodyshop's do not work on weekends yet the current formulae
can't
compensate
for weekends is the any way to get the formulae to filter
out
weekends
when
doing its calculations?

any help you can provide me with would be greatly
appreciated.
thanks
in
advance.