Using Conditional Formatting with Drop Down Menus

[cjay85]

My problem is as follows -

I am creating a spreadsheet to monitor response times to incoming
incidents to my department.

There are three different types of incidents - Critical, Major and
Minor. Each of these have different times that we should respond in.

Critial : 30 Minutes
Major : 1 Hour
Minor : 24 Hours

When entering information into the spreadsheet I have a drop down menu
so the user can choose Critical, Major or Minor.

I have a simple formula that if you enter the incoming date and time
and the response date and time it works out the time difference. I
have also used conditional formating to highlight fields in red that
take longer than 24 hours.

Is there any way to set values for each of these items on the drop
down menu. For example if the user chooses Critical and the response
time is worked out to be over 30 minutes it highlights the field in
red. But if the user chooses Major it only highlights the field if
the response time is over 1 hour.

Many Thanks,

Chris

 

[Bob Phillips]

You can do that with conditional formatting and a formula like

=OR(AND($B2="Critical",$C2>TIME(0,30,0)),AND($B2="Major",$C2>TIME(1,0,0)))

for example

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[cjay85]

You can do that with conditional formatting and a formula like

=OR(AND($B2="Critical",$C2>TIME(0,30,0)),AND($B2="Major",$C2>TIME(1,0,0)))

for example

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thank you Bob, that has worked a treat.

I am now trying something extra in regards to dates, exactly the same
concept as the first problem with time but it is in regards to dates.
For example Critical need to be solved in 1 Day, Major in 14 and Minor
in a month. Can I use the same conditional format concept using DATE
like below -

=OR(AND($B2="Critical",$C2>DATE(??)),AND($B2="Major",$C2>DATE(??)))

Could I ask what parameters I would need to validate the dates?

Thanks,

Chris