Complicated conditional formatting with multiple columns

Hi everybody, this forum is awesome! I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I'm sorry, I but the attachment system wasn't working for me, so I can't provide the example I've been working on. If someone can help me with the attachment, I can edit the post to add it.

So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. Hopefully all this makes sense so far, because here’s what I need the conditional formatting to do.

If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)

The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"

Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)

And finally, I’m hoping to apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank. I hope all of this makes sense. Please let me know if I need to clarify anything.

Thanks in advance for your help, and for the mere existence of this forum!

=$C2<=TODAY()+TIME(9,0,0)
that looks OK - should not highlight any date after today() at 9:00
OR(Left (B2,22)="Rework Request from PS", left(B2,22)="Rework Request from QA")

so to combine those

AND( OR(Left ($B2,22)="Rework Request from PS", left($B2,22)="Rework Request from QA") , $C2<=TODAY()+TIME(9,0,0) )

to stop the blank as that is seen as Less than today

C2<>""

AND( OR(Left ($B2,22)="Rework Request from PS", left($B2,22)="Rework Request from QA") , $C2<=TODAY()+TIME(9,0,0) , $C2<>"" )

and the due date in column D is today,

AND( OR(Left ($B2,22)="Rework Request from PS", left($B2,22)="Rework Request from QA") , $C2<=TODAY()+TIME(9,0,0) , $C2<>"", $D2=today() )

=AND(OR(LEFT($B2,22)="Rework Request from PS",LEFT($B2,22)="Rework Request from QA"),$C2<=TODAY()+TIME(9,0,0),$C2<>"",$D2=TODAY())

to upload a file

go advanced
Manage attachments
add files
basic uploader
choose file
upload

Also, is there a way to sort by what's highlighted?

yes you can sort by colour
if you goto sort> custom sort> sort on > you can change from value to Font or Fill colour

And, is there a formula for automatically counting how many cells in a column start with a specific phrase? I'd like to do an automatic count of how many of each kind job there is left.

you can use a countif() or countifs()
and use a left() to use only certain starting characters
can you give a specific example of what your need

you have a lot of conditional formatting now in that sheet
to help me ,can you give some specific examples and what cells to use for the new conditional format
as you say you have covered a few of the areas i mentioned - so not sure not what the rules are

try using an AND with some of your other conditions
AND( $D2 <= today() , $H2 <> "assigned", the rule you are applying )

the $ fixes the column so can then be applied across the range

so for this rule ,
=SEARCH("Rework Request from PS",$B2)

=AND($D2 <= TODAY(), $H2 <> "assigned", SEARCH("Rework Request from PS",$B2) )

Now the row has to be less than or equal today in D2 , must not have "assigned" in H2 and in B2 the text contains the string "Rework Request from PS"

I have used
=AND($D2 <= TODAY(), $H2 <> "assigned", SEARCH("Rework Request",$B2) )
in the attached copy sheet of you spreadsheet
Today's MRM List.txt (2)

If you would write all your rules for each colour out here - perhaps using an AND or OR

for example
Date in D2 must Not be greater than today AND it must not have "assigned" in H2 AND B2 has the text "request" somewhere in the cell Cell I2 contains "fred" OR "Harry"

hope that helps , and then I can review the sheet and help setup the conditions for you , at the moment you have quite a few conditions applying the same colour