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

NAZQAR

New Member
Joined
May 11, 2014
Messages
23
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?
EFG
1
EmployeeCheck InActual
2Bob Smith7:30
7:30
3John Jones
7:327:32
4Greg Abode7:35
5Helen Jennings7:45

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
You could use a conditional formatting with this formula
Code:
=AND($F$3="", (NOW()-INT(NOW()))>(91/288))

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

/AJ
 

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
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

New Member
Joined
May 11, 2014
Messages
23
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

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,424
Messages
5,528,682
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top