custom macro needed

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" <Stuart@discussions.microsoft.com> wrote in message
news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> i dont fully understand go to
> http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the
> needed adjustments and send modified file to
> stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > 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.
> >
> >
> >

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" <Stuart@discussions.microsoft.com> wrote in message
> news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> > i dont fully understand go to
> > http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the
> > needed adjustments and send modified file to
> > stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>

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
(Insert>Addins, 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" <Stuart@discussions.microsoft.com> wrote in message
news:4C161658-4C18-4C91-884C-5EF3DBC1C8DD@microsoft.com...
> 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" <Stuart@discussions.microsoft.com> wrote in message
> > news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> > > i dont fully understand go to
> > > http://homepage.ntlworld.com/stuartb...e/Downtime.xls make
the
> > > needed adjustments and send modified file to
> > > stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > > > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >

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
> (Insert>Addins, 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" <Stuart@discussions.microsoft.com> wrote in message
> news:4C161658-4C18-4C91-884C-5EF3DBC1C8DD@microsoft.com...
> > 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" <Stuart@discussions.microsoft.com> wrote in message
> > > news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> > > > i dont fully understand go to
> > > > http://homepage.ntlworld.com/stuartb...e/Downtime.xls make
> the
> > > > needed adjustments and send modified file to
> > > > stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > > > > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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
> > (Insert>Addins, 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" <Stuart@discussions.microsoft.com> wrote in message
> > news:4C161658-4C18-4C91-884C-5EF3DBC1C8DD@microsoft.com...
> > > 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" <Stuart@discussions.microsoft.com> wrote in message
> > > > news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> > > > > i dont fully understand go to
> > > > > http://homepage.ntlworld.com/stuartb...e/Downtime.xls make
> > the
> > > > > needed adjustments and send modified file to
> > > > > stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > > > > > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > > > > > 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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <Stuart@discussions.microsoft.com> wrote in message
news:508E74E0-BE30-4284-B95C-59036632BA6A@microsoft.com...
> 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
> > > (Insert>Addins, 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" <Stuart@discussions.microsoft.com> wrote in message
> > > news:4C161658-4C18-4C91-884C-5EF3DBC1C8DD@microsoft.com...
> > > > 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" <Stuart@discussions.microsoft.com> wrote in message
> > > > > news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> > > > > > i dont fully understand go to
> > > > > > http://homepage.ntlworld.com/stuartb...e/Downtime.xls
make
> > > the
> > > > > > needed adjustments and send modified file to
> > > > > > stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > > > > > > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > > > > > > 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.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

i dont know exactly but when the customer tried to enable the analysis
toolpack options he got a message saying that he needs excel 2003 so
obviously the formulae cant work for excel 2000 so is there an alternative
way of doing this that works for excel 2000?

"Bob Phillips" wrote:

> 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" <Stuart@discussions.microsoft.com> wrote in message
> news:508E74E0-BE30-4284-B95C-59036632BA6A@microsoft.com...
> > 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
> > > > (Insert>Addins, 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" <Stuart@discussions.microsoft.com> wrote in message
> > > > news:4C161658-4C18-4C91-884C-5EF3DBC1C8DD@microsoft.com...
> > > > > 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" <Stuart@discussions.microsoft.com> wrote in message
> > > > > > news:9BDD594A-CAE6-4FFC-9571-3532E9503171@microsoft.com...
> > > > > > > i dont fully understand go to
> > > > > > > http://homepage.ntlworld.com/stuartb...e/Downtime.xls
> make
> > > > the
> > > > > > > needed adjustments and send modified file to
> > > > > > > stuartbennett-online@ntlworld.com 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="",NETWORKDAYS(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" <Stuart@discussions.microsoft.com> wrote in message
> > > > > > > > news:2317D315-94F2-4FD4-9C3A-462ED50282C7@microsoft.com...
> > > > > > > > > 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.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>