Conditional Formatting

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have template that tracks the workers for a department.
I would like to be able to have the font change color if
the worker is late arriving or leaves early. The problem
that I have is that there are 15 workers who start at
various times so conventional condtional formatting will
not work. Here is a partial of what I have

Name Start Finish

Tom 8:05AM 5:00PM
Joe 9:00AM 5:55PM
Pam 6:30AM 3:15PM
Peter 7:03AM 4:00PM

What I would like to happen is to have Tom and Peter's
start time font change to yellow since they were late, and
Joe and Pam's color change as well since they left early?
TIA
 
Brian,

Do you have (anywhere in the sheet) the correct start and
finish times listed. Because that would make it a whole lot
easier.

Going by what you've got their the only thing I could think
to do would be format the cells based on the difference
between start and finish time (ie check to see if it's 9 hours)
But then you don't know if their late or early.

Dan E
 
yes, I can list them somewhere on the bottom of the sheet
if that will help.

-----Original Message-----
Brian,

Do you have (anywhere in the sheet) the correct start and
finish times listed. Because that would make it a whole lot
easier.

Going by what you've got their the only thing I could think
to do would be format the cells based on the difference
between start and finish time (ie check to see if it's 9 hours)
But then you don't know if their late or early.

Dan E

"Brian" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Brian,

That would make it much easier.

Sample

At the top A1:C4
Tom 8:05AM 5:00PM
Joe 9:00AM 5:55PM
Pam 6:30AM 3:15PM
Peter 7:03AM 4:00PM

At the bottom 101:A104
Tom 8:00M 5:00PM
Joe 9:00AM 6:00PM
Pam 6:30AM 3:30PM
Peter 7:00AM 4:00PM

Select the range B1:B4 (start times)
Format -> Conditional Formatting
Cell Value Is Greater Than =B101
Set your format and hit OK

Select the range C1:C4 (end times)
Format -> Conditional Formatting
Cell Value Is Less Than =C101
Set your format and hit OK

Now the late starts and early quittings will be identifiable
by whatever formay you've set

Dan E
 
If you name the array containing theexpected start and finish times and then use a suitable vlookup in the conditional formatting schedule, you questions are answered.
 
Back
Top