Time problem

  • Thread starter Thread starter MythicPhoenix
  • Start date Start date
M

MythicPhoenix

Hi,
Does anyone know how to use conditional formatting to take a saved time
and then find the difference between the saved time and the current
time to change a cell color. I am trying to use this to keep cell
information up to date by changing it a different color if the
worksheet time has not been updated in an hour.

Any suggestions?
-Phoenix
 
One way:

Assume the saved time is in A1, and is saved as an XL time (rather
than NOW() which saves both the date and time). Then

CF1: =(MOD(NOW(),1)-A1)>1/24

The only problem with using CF is that it is updated on calculation,
so if your sheet doesn't recalculate, the color won't change.

You could use an OnTime macro to cause your sheet to calculate once
a minute (or less). See

http://cpearson.com/excel/ontime.htm

for an explanation.
 
Using the simple and elegant formula provided by J. E. McGimpsey, I wa
able to conditionally format a cell color to show that the tim
entered is later than the current time (by more than a minute) usin
the modification below:

CF1 =(MOD(NOW(),1)-A1)<1/1440

I use this in a timekeeping spreadsheet to hi-lite an error on star
time entered
 
Back
Top