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