Prioritising condtitional formatting??

Question

Hi

I am making up a spreadsheet in which a cell highlights in red if a task takes longer than the allotted time. I am using the NOW() function, so therefore, I need the conditional formatting to change if I write in another cell (to indicate the task is completed). As I am using the NOW() function, the cell will stay red whether the task is completed or not, so I need to work the conditional formatting so that if anything is written in the completed box, then they cell goes clear, and all other formatting is overridden.

I am a bit of a novice at this, so please let me know if this is even possible!! I have tried to include & in the 'formula is' but this doesn't seem to do anything, and I don’t know where to go now... help would be very much appreciated!!

Thanks

Amy!

Name Lines Route Time Issued Time Allocated to complete (hours) Projected completion time Actual Completion Time Time taken to complete (hrs) OVERRUN (hrs)

Amy 20 3 9:00 AM 00:23 9:23 AM ############ 0:06

Answer 1

it might be possible to get this done.  First of all, you can have mulitple conditions assigned (just click the <Add> option on the conditional format box to assign more conditions).  But one thing to keep in mind is  that if you are using multiple conditions, Excel applies only the formats of the first true condition, even if more than one condition is true.

So in your case what you could do is put your 'completed' condition as the 1st condition and your time alloted condition as the 2nd one.

For example as the first condition, let's say your time condition is in A1 and your "completed" indication is in cell B1.  Add a conditon to A1 of

=ISBLANK(B1)=FALSE  : set format of normal

add a 2nd condition to A1 of

cell value > now()  : set format to red text

now if B1 (completed indicator) has something in the cell (it is not blank), the format condition becomes true because B1 is not blank.  Excel will then ignore the 2nd condition.

if B1 is not blank and the alloted time is greater than today, it'll change it to red.

you can use different condition as your first condition depending on what you need (for example cell value > 0 or whatever your keying on to determine the task is completed).

the important thing to remember is you'll need your completion condition to come first so that excel ignores the rest of the condtions once the task is completed.