Multiple Conditions for Conditional Formatting

[bhardison]

I want to set conditional formatting for columns a:c based on whether the employee is in the building or not.. However, because I have two columns that indicate the employee is out of the office (Time Out Break & POSH Out), one rule is basically overridden by the other. I'm sure there's an easy way to do it, I just haven't been able to figure it out..

What I want is for the Employee column to be formatted as follows:
-Time In (Day Begin) D7:D29 populated: Blue
-Time Out (Break) E7:E29 populated: Gray
-Time In (Break) F7:F29 populated: Blue
-POSH Out G7:G29 populated: Gray
-POSH In H7:H29 populated: Blue
-Time Out (Day End) L7:L29 populated: Gray

Ultimately, I want the employee cell to be blue if they're clocked in (Time In Day Begin, Time In Break or POSH In is populated) BUT turn gray if they've clocked out (Time Out Break, POSH Out, Time Out Day End) after they've clocked in.

Break Time (Columns D & E) may be before or after POSH time (Columns G & H) and I think that's where I'm getting hung up.


Any help would be appreciated.

 

[Jonmo1]

Welcome to the board

Do you have Headers in say Row 1?
And do those headers actually contain the words "In" or "Out" ?
If so, that can be used..

What type of data is entered in the cells of Row 7 from D to L ? Time values? Are they real numeric time entries?
Why does it skip column I (H is POSH IN, L is Day End) what type of data is in column I ?
Can we assume the values will be entered in order, D7 first, then E7 then F7 etc.. ?

 

[bhardison]

Jonmo- Thanks for getting back with me. I rearranged the spreadsheet a little so that it doesn't skip any columns.. So, all times will be entered in columns E:J. Column names appear in Row 6 and they all either say IN or OUT. They will all be Time values. Unfortunately, though, they won't always been entered in order.. Sometimes POSH Out time will be before Time Out (Break), but sometimes it will be after.

 

[Jonmo1]

OK, so long as they are VALID time entries, then we can just use the MAX time value.

And you really only need 1 conditional format.
Logically speaking, the person will be either In or Out.
So we can also say, if they are NOT IN, then therefor they MUST be OUT. right?
So we only need to test for IN.

So set your standard plain old cell formatting to Grey for out.
Then use this formula in your contional formatting for the cell you want to highlight based on when they're IN.
Also assuming the columns are now D to I
=SEARCH("In",INDEX($D$6:$I$6,MATCH(MAX($D7:$I7),$D7:$I7,0)))
Set the color to Blue for IN.

 

[bhardison]

Jonmo- That was exactly it! Thank you so very much!

 

[Jonmo1]

Glad to help, thanks for the feedback.