Time management

[OKY]

Hello,
I have a new job and to make it easy I need to have a worksheet where I can
track my service technicians.
In other words we have technicians who are schedule to complete jobs at
different time frames. Lets say Tech1 has 3 jobs between 8am to 11am, 2 jobs
between 11am to 2pm, etc, ect. I need the worksheet to change cell colors
according to the time, taht way if a technician is running late the cell for
that job will change to red..
example:
TECH# TECH NAME PASS/FAIL TIME FRAME STATUS
25 MIKE PASS 8AM - 11AM DONE

Could this be acomplished? please help me. Also be patient with me to
explain in detail your answers.. I'm kind of newbie..


thank you

 

[Luke M]

My first recommendation would be to break the TIME FRAME cell into 2 seperate
columns, a START and FINISH. This will make it much easier for calculations.
For your coloring scheme, you'll want to use the NOW function and Conditional
Formatting. (these topics can be found in Help File).

Let's say you want the start column to turn red if the start time has
arrived (or has past). Select then cell (for example, say D2), then go to
Format - Conditional Format. From the first dropdown, select "Formula is". In
the next box, input this formula:
=D2<=MOD(NOW,1)
Click the format button, pattern tab, choose red. Hit 'ok', then 'ok' again.
*The MOD function removes the date from NOW, leaving just the time*

Or, let's say we want the cell to change color only if time ahs passed, and
cell C2 does not equal "pass"

=AND(D2<=MOD(NOW,1),C2<>"pass")


Try to play around a little with this on your own, referring to help file
for ideas. If you get stuck, feel free to ask the group, but give more
specific detail as to your setup and what exactly you want to happen.