Conditional Formatting
 

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

Conditional Formatting
 

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" wrote in message ...
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

Conditional Formatting
 

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" wrote in
message ...
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




.

Conditional Formatting
 

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

"Brian" wrote in message ...
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" wrote in
message ...
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




.

Conditional Formatting
 

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.