custom macro needed

[Stuart]

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.

[damorrison]

How do I find your example?

[Bob Phillips]

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.

[Stuart]

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 Phillips]

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.
> >
> >
> >

[Stuart]

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 Phillips]

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.
> > > >
> > > >
> > > >
> >
> >
> >

[Stuart]

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.

[Stuart]

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.