Color Change depending on time without refresh. Excel 2010, Windows XP Professional

[NAZQAR]

OK, so my company uses excel as it's employee clock / in-out software. While not an ideal solution it works fairly well. However we have had a recent spate of missed work because a few dispatchers have failed to notice that an employee has not checked in. The following table shows how I have the columns set up. As an employee checks in we type their check in time in the "actual" column. What I would like is for the blank space in the "Actual" column to change color if the time that the employee was supposed to check in has passed. So in cell G4 if it is past 7:35 and G4 is empty I would like it to turn red. I also would like that feature to be automatic, meaning that the dispatcher should not have to refresh the sheet or hit the save button for it to work. Is this possible with either formulas or VBA?

	E	F	G
1	Employee	Check In	Actual
2	Bob Smith	7:30	7:30
3	John Jones	7:32	7:32
4	Greg Abode	7:35
5	Helen Jennings	7:45

<tbody>
</tbody>

 

[adam087]

You could use a conditional formatting with this formula

=AND($F$3="", (NOW()-INT(NOW()))>(91/288))

That should not require a refresh.
(Famous last words).

/AJ

 

[ITWare2008]

Hi Nazqar,

Maybe a conditional format could achieve what you want.

select cells from g2:g5 for example create a new rule for conditional format and place this formula

=isblank($g2)

Hope this help.


Vândalo

 

[NAZQAR]

Column F is never empty, it lists the time that the employee is supposed to report. Column G is empty until the employee reports for duty, at which time we type their report time in column G. Usually the two times match but occasionally if an employee is late or clocking in early for an approved reason. What I want is, if for instance G4 is empty and the time is greater than the time in G3 then G4 will turn red. Thank you greatly for the replies so far.

 

[adam087]

Column F is never empty, it lists the time that the employee is supposed to report. Column G is empty until the employee reports for duty, at which time we type their report time in column G. Usually the two times match but occasionally if an employee is late or clocking in early for an approved reason. What I want is, if for instance G4 is empty and the time is greater than the time in G3 then G4 will turn red. Thank you greatly for the replies so far.

Sorry. Mine should still work, just change the F for a G.

/AJ