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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Bob
we apear to have hit a problem. i was unaware that our customer is
only
using office 2000 is there anyway we can adapt the formulae to
just
use
the
technologies available in excel 2000?
"Stuart" wrote:
thanks bob it was part of my excelk i just went to tools then
addins
and
ticked both boxes you need the normal and the vba ticked ill try
this at
work
on monday thank you for your help.
"Bob Phillips" wrote:
The Analysis Toolpak is part of Excel, it just needs to be
installed
from
the Excel CD. Are you sure it is not installed, jut not loaded
(InsertAddins, and check it)
If your IT guys are not willing to do that (why not? -
challenge
them),
then you could use
=IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6}
)-A2+8)/7))-
SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*(
holidays<=(IF(B2="",TODAY(),B2)))))
replace holidays with a range of dates for holidays, such as
H1:H10,
(even
if you have none)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Stuart" wrote in message
...
firstly i dont have analysis toolpack which is probably why
it
didnt
work
and
even if i knew where to get it from we arent allowed to
install
new
software
at our work and the customer wont want to install new
software
just
to
read
there reports. is there any other way round this problem?
"Bob Phillips" wrote:
In your workbook, use the formula that I gave and in the
first
row,
change
onsite_date to A2, return_date to B2.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Stuart" wrote in
message
...
i dont fully understand go to
http://homepage.ntlworld.com/stuartb...e/Downtime.xls
make
the
needed adjustments and send modified file to
as this will be easier
that
way ill
be
able
to see what you did. would you guys mind?
"Bob Phillips" wrote:
You don't need a macro for this, a simple formula will
work
=IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET
WORKDAYS(onsite_date,return_date)))
it uses NETWEORKDAYS which is part of the Analysis
Toolpak, so
that
needs to
be installed.
--
HTH
RP
(remove nothere from the email address if mailing
direct)
"Stuart" wrote in
message
...
I work for a company in Hertford now. We are an
accident
management
company
as part of our services we provide our fleet
customers
with
weekly,
biweekly,
monthly or even daily reports. one of the pieces of
information in
these
reports is the downtime which is how long the
vehicle
has
been in
the
bodyshop. I have attached an working example to this
to
this
email
it
uses
an
excel formulae to calculate the difference between
the 2
dates.
now
then
what
I want to know from you is how to improve on this
formulae.
I need it to not only work out the difference
between
the 2
dates
but
if a
onsit date is given but no returned date it will
show
how
many
days
its
been
from onsite date to today's date. also if returned
date
is
given
but
no
onsite date it should return a blank value as our
customers
don't
want
to
see
"#VALUE" show up anywhere. lastly this formulae is
limited
in that
our
bodyshop's do not work on weekends yet the current
formulae
can't
compensate
for weekends is the any way to get the formulae to
filter
out
weekends
when
doing its calculations?
any help you can provide me with would be greatly
appreciated.
thanks
in
advance.