LinkBack URL
About LinkBacksThe 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.
> > > >
> > > >
> > > >
> >
> >
> >
Register To Reply
Bookmarks