Conditional Format to highlight overdue or on hold status jobs.... please help

[Diana2rom]

hello,

I am using 2010 excel and I really need help with figuring out a formula for 3 conditions that highlights an entire row (each representing different status of a maintenance job) in a table format. I am trying to highlight the jobs in 3 different colors. The table Data A2 to R44, row 1 are the headers. These are the variables: The target completion cell M2 contains date and time. BUT in K2 can have 3 different statuses: "D-Dispatched", "EBO- Equipment on back order", "DHD- On hold per Client". Past Target days in cell N2 contains negative and positive numbers (-2 means we have 2 days still to complete). the Past target days is not necessarily the difference between now and target completion date. If its on "EBO- Equipment on back order", the clock sort of stopped until we received the material, so if the Past Target Date in N2 is a positive number on the EBO status, then that does not count as overdue because the clock stopped.So.....

If it's overdue, it should be highlighted RED (but that should look not just at the target completion date, because there could be a negative # of days in N2
If it's soon to be overdue (8 hours) highlight in YELLOW
If its on "DHD- On hold per Client" highlight in LIGHT BLUE


I tried this first

=$M2 < NOW()

=$M2 < NOW ()-1/3<now()-1 3="" format="" fill="" yellow<="" font="">
<now()-1 3="" format="" fill="" yellow

</now()-1> =$K2="DHD- On hold per Client" format fill BLUE

But it doesn't work

Please help...

I really hope that I made sense because there are a lot of conditions.


Thank you</now()-1>

 

[RonB1111]

Make sure you selected A2:R44,
Try this formula and adapt as needed:

=and([COLOR=#333333]$M2 < now(),$N2 > 0<now(),$n2><now(),$n2>)</now(),$n2></now(),$n2>[/COLOR]

 

[Diana2rom]

that is a great formula to start with. Thank you