custom macro needed

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.
0
Stuart (76)
11/5/2005 10:22:02 AM
excel.misc 78881 articles. 5 followers. Follow

16 Replies
457 Views

Similar Articles

[PageSpeed] 44

How do I find your example?

0
11/5/2005 10:55:12 AM
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.


0
bob.phillips1 (6510)
11/5/2005 11:52:22 AM
i dont fully understand go to 
http://homepage.ntlworld.com/stuartbennett-online/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.
> 
> 
> 
0
Stuart (76)
11/5/2005 1:46:04 PM
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/stuartbennett-online/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.
> >
> >
> >


0
bob.phillips1 (6510)
11/5/2005 2:01:49 PM
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/stuartbennett-online/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.
> > >
> > >
> > >
> 
> 
> 
0
Stuart (76)
11/5/2005 2:42:01 PM
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/stuartbennett-online/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.
> > > >
> > > >
> > > >
> >
> >
> >


0
bob.phillips1 (6510)
11/5/2005 3:05:09 PM
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/stuartbennett-online/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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
> 
> 
> 
0
Stuart (76)
11/5/2005 3:29:01 PM
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/stuartbennett-online/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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> > 
> > 
> > 
0
Stuart (76)
11/8/2005 6:55:04 PM
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/stuartbennett-online/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.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >


0
bob.phillips1 (6510)
11/8/2005 8:05:40 PM
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/stuartbennett-online/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.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> 
> 
> 
0
Stuart (76)
11/8/2005 9:51:02 PM
Enabling the Toolpak cannot have anything to do with the formula, it just
doesn't figure into it when installing/enabling the Toolpak,

There is something odd here that is impossible to debug from this distance.
Did you try my non-ATP version?

-- 

HTH

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


"Stuart" <Stuart@discussions.microsoft.com> wrote in message
news:534ABCFA-65A9-467D-8A78-63716787A30B@microsoft.com...
> 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/stuartbennett-online/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.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >


0
bob.phillips1 (6510)
11/8/2005 10:39:49 PM
no as unfortunately it uses that holiday command and it is hard to predict 
holidays especially as our bodyshops in scottland just take them whenever 
they feel like it as per scottish law.

"Bob Phillips" wrote:

> Enabling the Toolpak cannot have anything to do with the formula, it just
> doesn't figure into it when installing/enabling the Toolpak,
> 
> There is something odd here that is impossible to debug from this distance.
> Did you try my non-ATP version?
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> news:534ABCFA-65A9-467D-8A78-63716787A30B@microsoft.com...
> > 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/stuartbennett-online/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.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
> > >
> 
> 
> 
0
Stuart (76)
11/9/2005 6:41:02 AM
Either create an empty range of holidays or strip it out

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


-- 

HTH

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


"Stuart" <Stuart@discussions.microsoft.com> wrote in message
news:6C4AE681-1A10-4F37-90D0-F3B795E8C8D6@microsoft.com...
> no as unfortunately it uses that holiday command and it is hard to predict
> holidays especially as our bodyshops in scottland just take them whenever
> they feel like it as per scottish law.
>
> "Bob Phillips" wrote:
>
> > Enabling the Toolpak cannot have anything to do with the formula, it
just
> > doesn't figure into it when installing/enabling the Toolpak,
> >
> > There is something odd here that is impossible to debug from this
distance.
> > Did you try my non-ATP version?
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > news:534ABCFA-65A9-467D-8A78-63716787A30B@microsoft.com...
> > > 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/stuartbennett-online/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.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > >
> > > >
> > > >
> >
> >
> >


0
bob.phillips1 (6510)
11/10/2005 10:26:01 AM
ok bob thanks for your help i will certainly give it a try however for now i 
have been saving the excel sheets as webpages so the customer can still see 
the data as we see it without having to worry if he can run the addin 
hopefully that will solve problem if not ill try that formulae and see what 
happens thanks for all your help youve been great ill certainly remember you 
for any future queries. actualy while i am at it i do have another query.

scenario:
i have a column for drivable this column contains either a value of yes or 
no which i want selected from a dropdown list. i have a notification date a 
bookin date and an elapsed time. i want to set conditional formating on the 
elapsed time using the following conditions.

if drivable:
value of elapsed less than 6 use green background, if drivable and is 6 or 
greater show in orange and red if drivable and greater than 9. 

if non drivable:
less than 3 background to be green if 3 or greater make it orange if 10 or 
greater then make it red. 

how do i?
A) make all cells in drivable column use the dropdown list with preset 
values of my choice.
B) get a formulae to make the formating changews i require based on the 
conditions specified.

Stuart

"Bob Phillips" wrote:

> Either create an empty range of holidays or strip it out
> 
> =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2))-WEEKDAY(A2+1-{2;3;4;5;6}
> )-A2+8)/7
> 
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> news:6C4AE681-1A10-4F37-90D0-F3B795E8C8D6@microsoft.com...
> > no as unfortunately it uses that holiday command and it is hard to predict
> > holidays especially as our bodyshops in scottland just take them whenever
> > they feel like it as per scottish law.
> >
> > "Bob Phillips" wrote:
> >
> > > Enabling the Toolpak cannot have anything to do with the formula, it
> just
> > > doesn't figure into it when installing/enabling the Toolpak,
> > >
> > > There is something odd here that is impossible to debug from this
> distance.
> > > Did you try my non-ATP version?
> > >
> > > -- 
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > > news:534ABCFA-65A9-467D-8A78-63716787A30B@microsoft.com...
> > > > 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/stuartbennett-online/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.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
> 
> 
> 
0
Stuart (76)
11/10/2005 7:45:12 PM
Bob

i need a formulae similar to the one you gave me for downtime. i have an 
estimate date and a date estimate was authorised, i want to know how long it 
took from the date of estimate to the date it was authorised but i want the 
weekends included. if authorised date not given o est date not given then 
return as blank.

"Stuart" wrote:

> 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.
0
Stuart (76)
11/14/2005 2:37:17 PM
bob

i need a specialist formulae. ok heres what i need. i have a date the 
accident was reported and the date the vehicle came onsite the difference 
between these two is put in a column called elapsed. according to my companys 
SLA's (service level agreements) that we have with our customers all vehicles 
that are drivable must be onsite within 5 days of when the accident was 
reported to us, all non drivable vehicles are to be recovered to the bodyshop 
within 2 days of notification.

now then we want to use a traffic light based system in our customer 
reports, if the vehicles is recovered per SLA then it shows up green if it 
exceeds SLA criteria then it goes to orange, if it exceeds the 10 day 
threshold it must go to red indicating a serious problem and that the 
customer needs to get booked in quick.

therefore i need a formulae to for my conditional formating that tests if 
the elapsed time is within SLA Specifications and take the appropriate 
formating action. could you please tell me what the formulae would be based 
on drivable values being in column G and elapsed times being in column J? in 
case it makes a difference the formulae used to calculate the elapse time is 
as follows.

=IF(H2="","",IF(I2="",TODAY()-H2,I2-H2))
 
Stuart


"Stuart" wrote:

> 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.
0
12/6/2005 9:26:03 PM
Reply:

Similar Artilces:

Macro on cell content
Dear all, this must be a common problem, and I thought I found the solution, but it doesn't work ... I'm probably missing something very simple. I want to execute a simple VB function in one cell, taking another cell as Parameter. Both, parameter cell and function result are strings. For example: A1: "this is content of cell A1" A2: =Convert(A1) and Convert is a VB function Function Convert(content As string) as String ' do something depending on content Convert = "result-string" End Function However, I always get a #NAME? Error in cell A2. The ide...

Data Validation Help Needed urgently !!!!!!!!!!!
I am trying to write a data validation in VBA. I know I have the code right but I can't get it to work. It works fine if the validation was done dirctly in excel but I get an error when I try the same thing in VBA. Below is a snippet of the code I am having a problem with. The problem seem to be with the " Formula1:=" line. I have been scratching my head for the past 24hrs now trying to figure this out and I am still not coming up with anything. Any help will be greatly appreciated. Range("B3:C3").Select With Selection.Validation .Delete .Add Type...

using macro to save a file produces runtime error 4198
I upgraded from Win-XP/Office 2003 to Win-7/Office 2007; the code below ran fine in Word 2003 but in Word 2007 generates runtime error 4198. The variables and directory location are valid. The parameters for FileName are updated for Word 2007; the grammer for the FileName appears correct (no grammer errors when macro runs as is, but grammer errors if I change the string.) When I substitute an actual file name in place of the variables, the code runs w/o error, but the Debug.Print statements generate correct strings. I feel kind of dumb- but what is different about use of variab...

Custom dat & time code
Does anyone know if it's possible write a custom cell-format code that=20 would look like the one Excel provides (mm/dd hh:mm AM/PM) but sets the=20 time in red? I've tried "mm/dd [RED]hh:mm AM/PM," but whenever I save = it,=20 Excel moves the red bracket to the beginning of the code so the whole = thing=20 is red. Help! Any ideas? I don't think you can. You can't format formulas like this (character by character). And you can't format numbers this way, either. (Anyway, I couldn't do it.) Wuddus wrote: > > Does anyone know if it's possible ...

I need a running total of monthly grocery expense
I need to keep a weekly total of my grocery expense vs. budget and totaled each month. ...

documents issued in one version need to be updated in another
Can we open a document issued in version 2003 if we have 2007 installed? The document would be emailed to me by another office and I need to be able to open it and work in it. YES John G. "Becky Freedman" <Becky Freedman@discussions.microsoft.com> wrote in message news:A839A4C9-41EA-4FBF-94AF-6974E061D771@microsoft.com... > Can we open a document issued in version 2003 if we have 2007 installed? > The > document would be emailed to me by another office and I need to be able to > open it and work in it. ...

Trying begin VBA template project, get error "macros disabled"?
I am beginning a project to code and update a Word template using VBA. When I try to run the form given to me I get error "The macros in this project are disabled?" Any suggestions? Thank you. Save the template in your templates folder, which is a trusted location. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "The Applicant" <The Applicant@discussions.microsoft.com> wrote in message news:D2E642C...

Arrgh!..Need OW Help Please
Let me preface by saying I am using this document http://www.msexchange.org/tutorials/OWA_Exchange_Server_2003.html as a guide. I have not configured any SSL options yet because I can't get it to work. Trying to build Exchange 2003 Frontend/backend on my LAN. Front end is W2k3 SP1\Exchange 2003 SP1 and Backend is W2k3 SP1\Exchange 2003 (no SP1). After installing Exchange on the 2nd server (to be the new Front-end), I am able to create a mailbox in it's own store and access it via OWA from a Win2000 Pro workstation. This mailbox resides on the 2nd server's own store. I then en...

Copy/Paste macro...with a few twists
Hi, I needed to create a macro that would copy/paste all the selected rows into a new workbook which the macro creates. Before creating the workbook however I need it to ask the user for a six digit number, which it then takes and names the workbook (eg. PO######) and attaches the current date on the end. I'm still a newb to VBA so any help is appreciated. Best Regards, Mjack003 --- Message posted from http://www.ExcelForum.com/ Something like this might get you started: Option Explicit Sub testme() Dim myStr As String Dim newWkbk As Workbook Do myStr = ...

Macro to delete specific data in tables
Hi: I am an analyst working in service quality for a market research firm. We use SPSS 12 to output the data and later copy paste the tables into Excel for formatting and printing. The problem I currently have is that I got a large group of output tables which come in two different formats. The first table is like this: Satisfacción Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 Más de 100.001 UF ...

Macro Blunder
I seem to have lost the toolbar that pops up when you prompt Excel t create a new macro. The toolbar with the stop button and the relative reference button i gone. How do I get it back??? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com nkob Tools>Customize>Toolbars. Select "Stop Recording" toolbar>Close Stop Recording Toolbar should now be visible on sheet. Hit "X" to close it(but not while you are recording). It will pop up next ...

Custom Fields/Formulas Receiving an error "NA" in Project Center
Hello All, Was wondering if I could get some help, we are running project server 2007 latest edition with all patches. And are running a custom formula. "IIf([Milestone]=Yes,"Milestone",IIf([PPC_Result]>=1,"100.00%",IIf([Baseline Start]=ProjDateValue("NA") Or [Baseline Finish]=ProjDateValue("NA") Or [Baseline Duration]=ProjDateValue("NA"),"No Baseline",IIf([Baseline Start]>Now(),"Baseline start in future",Format(([PPC_NUM]/[Baseline Duration]),"percent")))))" The formula runs off a ...

Problems importing customizations
Hi, I'm having difficulties with the import of customizations to a new CRM. I keep getting one of two messages, "SQL Server error occurred" or "Either the file could not be upload or import failed". The customizations file conforms with the schema, and it's size is 5.97MB. Could it be because of the amount of customizations? I have at least 70 custom entities, some of them heavily customized. Do I have to import the entities in groups of related entities? Is the substitution of the new CRM databases with the customized ones, a possible solution? Are there an...

Need helps about importing and etc.
I'm a newbie, where can i find information about RMS Setup and Deployment Overview, Installation Requirements, Terminology, Upgrading, Configuration, Import/Export, Limitations, and so on? thanks, Jay K Partner Source http://www.microsoft.com/BusinessSolutions/partnersource.mspx -- Mobitech Lady Amy Luby Mobitech 402.330.0707 www.mobitechonline.com "JayK" <JayK@discussions.microsoft.com> wrote in message news:3AD41CE8-6B86-45A0-A2EA-A61A91CFB482@microsoft.com... > I'm a newbie, where can i find information about RMS Setup and Deployment > Overview, Instal...

Naming Custom Animations
I would like to propose Microsoft to add editable naming fields for custom animations. It would save a massive amount of time for not only a designer but for anyone else on a design team who has to edit another designer's work. Instead of saying: TEXT BOX 1, or RECTANGLE 4, or GROUP 32, a designer could add, for example: ABC LOGO, XYZ LOGO, CIRCLE FROM LEFT, CIRCLE FROM RIGHT, or whatever is appropriate. If a designer could add their own relevant name, at a later date anyone would be able to go directly to the specific animation rather than search by clicking and testi...

Open Form with macro and data from Selected Record
I am wanting a person to open a form, and have the subform display the records that relate to the main form. This part works. I then have a continuous form view for the subform and i want the user to be able to double click on the record they see in the subform and have it pull up that record into a blank form. I get the blank form to open up when they double click, however I cannot get the data for that record to show up. I either get a blank/new record or all records. I am trying the SearchForRecord macro in an On Load Event. This loads after the form has been opened. Ac...

Need to change pic viewer in Excel
When I was running XP Home I could open a link to a picture in Windows FAX viewer. But in running XP Pro the picture opens in IE which causes multiple browser windows to close if viewing several linked pictures. This is an Excel newsgroup. You should post your question to a WindowsXP Pro newsgroup. HTH Otto "rgroff" <rgroff@discussions.microsoft.com> wrote in message news:46469ECB-F3B4-4F33-9FAC-8496239F14C2@microsoft.com... > When I was running XP Home I could open a link to a picture in Windows FAX > viewer. But in running XP Pro the picture opens in IE which...

Custom addition formula
Using Office2000 Sp3 I use Excel a lot for historical things including eg; areas of land expressed in ACRES ROODS PERCHES which is not added in tens (similar to �/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a formula which when applied to entries across 3 columns gives a decimal answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in D1, Roods E1, perches F1. Once this is decimalised I can work out ratios etc. However, I occasionally get tabbed text files or excel files from people who have entered lots of raw data as eg ARP where it would be ...

numbering macro
In a database of say 10 columns and 100 rows, how do I record a macro that will o Insert a new column to the left of column A, then o in the new column A, number the rows that contain data in column B. Note: The numbering should start from 1. Since row 1 has the database heading & row 2 has the column headings, the numbering (no. 1) should start from row 3 and continue down to the last row that has data in column B. Columns("A:A").Insert For i = 1 to 100 If Cells(i,"B").Value <> "" Then Cells(i,"A&q...

Customer/vendor consolidations
Is there a seperate module for the customer/vendor consolidations? I have a situation where a customer owes us some money and just wants to deduct the amount from what we owe them. Sounds simple enough but when I go to Tools>>Setup>>Company (directions printed from the HELP file) I don't have a Customer/Vendor Setup window. Any suggestions?? Customer/Vendor Consolidation is a separate module and will do what you're after. However, if you only need it this one time, you might want to consider simply entering a credit memo in both payables and receivables to take car...

Outlook Macro
I need to set a large number of Outlook calendar entires to: Show Time as: Free, and Sensitivity: Private I am having difficulty working out how to write a macro to do this. can anyone help please? Thanks V ...

IMAP service started but the protocol needs to be started under SysMGR
I have a W2K3 and Exchange 2003 server with no error messages in the event viewer. Even though IMAP is listed as started under Services, I had to go to Exchange System Manager to go to the server, select protocols and then start. There are no error messages in the Event viewer - can anyone tell me how to start this service automatically? It is currently set for automatic in Services but that isn't doing it (even though it says started) If you stop the service with the virtual server online it should be online when the service is again started. You might have to refresh but it shoul...

Sorting in a custom view not functioning as expected
I just can't shake the feeling this is a stupid question, but I can't seem to get it or find an appropriate Google response. I have saved a custom view for my tasks. In it, I have a custom sort set up for my tasks. (A custom field text field "Grouping" ascending, then "Status" descending, then "Due Date" ascending, if you want to know.) My problem is, it's often convenient to look at things by category, so I (and my little users) click on the "Category" heading to get a quick view of things sorted that way. Then, I want to...

publishing custom reports for MS CRM with Crystal Reports
Hi all, To meet specific reporting requirements I have made several custom reports with Crystal Reports for Microsoft CRM. The reports are stored in the Enterprise folder of our test server. Now I'd like to publish them on our production server. My question is how to do this. Is Report Manager the tool I need on the production server? Thank you for replying, Basman Hi, You can use the Report Manager to deploy the reports to the server. The tool was designed specifically for this task. By standard the tool is only accessible from the same server as it is installed on and not from ...

importing with a custom contact form results in a notes record
I am importing a comma deliniated dos file to a contacts sub-folder using a custom contacts form and the imported records arrive in a notes format rather than my custom contact format. When I add a record manually the custom form produces the desired result. My form starts with IPM.Contact.Principals. What am I doing wrong? and can I change a notes record to a contact record after the fact. I am using Outlook 2000. Thanks ...