Conditional format - Time format that passes 3hrs?

[kris998]

Hi everyone,
I need a formula for conditional formatting the highlights once 3hrs have passed from a single time cell? So time is in A1 for example, 11:00am. Once its 14:01 it highlights red.

Many thanks in advance. Hope thats ok?

[XLent]

You could use a custom rule of:

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

and format as red

note however, a volatile action will be required to trigger a calculation in order for the rule to be evaluated.

[harrisonland]

If A2 contains a date with the time, B2 would show the "due" time with the formula

Please Login or Register  to view this content.

Put this in C2

Please Login or Register  to view this content.

and do a conditional format.

That should fix it.

HTH
Tim


EDIT: Forgot to add that it's a volatile function, but I now see XLent got there first!

[kris998]

Thanks for your help, but what is a volatile function?

[XLent]

the calc engine is clever to the point that it will only calculate what it needs to when it needs to thanks to it's calculation tree (known precedents etc) but there are exceptions with Volatile functions being the prime example.
another exception is if, within the VB Editor, you enforce full calculation thereby overriding the above - this can be helpful if you have RAM issues on an otherwise efficient file

a Volatile function will recalculate whenever the calculation engine is triggered c/o a Volatile action - like entering a value in a cell, hiding a row etc
always worth remembering that not every action is volatile - e.g. formatting a cell, or hiding a column

generally speaking Volatile functions are detrimental to performance as they generate additional calculation overhead
as always, odd exception, e.g. Data Validation -- using a Volatile in a Data Validaton rule generates no additional overhead as only evaluated when the Data Validation is invoked

the key here, in your example, is that the Conditional Format will work, logically speaking, but will only be enacted if a calculation is triggered

to illustrate concept, I could say enter the below Volatile function into A1:

=RANDBETWEEN(1,1000)

however, once I've committed the above it won't change again until I perform a volatile action that triggers the calc engine -- like typing "a" into B1
(the above is what we mean by additional overhead as technically speaking A1 has no direct dependency on B1 yet it still recalculates when B1 is modified)

so, what I was trying to say was, you can add the Conditional Format rule as outlined - but unless you're doing something that triggers a calculation in XL it won't be evaluated

so you could watch the cell all day and it would never change colour, regardless of the time, but if you type "a" into another cell it would.

the best resource, IMO, for all things Volatility is Charles Williams' website, the author of many a white paper etc.

http://www.decisionmodels.com/calcsecrets.htm

[Pepe Le Mokko]

See http://www.decisionmodels.com/calcsecretsi.htm for volatile functions

[harrisonland]

"Volatile" means it depends on a value that constantly changes - for instance the current time or today's date. A calculated cell value that is "volatile" can't always be relied on to be correct unless the workbook has just re-calculated.

NB: XLent's solution, above, will work whether your single cell (A1) is a date/time stamp, or just contains the time. Mine will only work if (A2) includes the date as well.

I assumed that would be the case as as you could otherwise get false results. Consider, for example, if your single "Time" cell contained the time "11:34pm".

Tim

[kris998]

Thank you everyone, thats really helpful.