Conditional Formatting with Date and time

D

Dom

Hi,
I'm trying to get the cell colour to change depending on the time difference
between two times. In cell F i have the date and time i recieved a report and
in cell G the date and time i chased the jobs on the report in cell H is the
difference between the two and if this is under one hour i want it to be
green and if over one hour to be red. The difference is displayed in
dd:hh:mm. Can anyone help i've tried several formulas but can't seem to get
it to work. Any help would be appreciated. Thank you
 
B

Bob Phillips

=H1>TIME(1,0,0)

=H1<=TIME(1,0,0)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Dom

Hi, thanks for getting back to me. I've tried putting those in and it changes
the cell to green but doesn't change to red when the cell is over one hour
any ideas?
 
J

JE McGimpsey

Since H1 will be either > or < TIME(1,0,0), there's no need for both
formulas. Format the cells Green, then use one condition:

CF1: Formula is =H1>TIME(1,0,0)
Format: <pattern>/<red>

If that doesn't work, what formula are you using to populate H1?
 
B

Bob Phillips

you have to set different format pattern colours with each formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Dom

I did try setting a different colour for the two different formulas

CF1 was =H1>TIME(1,0,0) set to green
and
CF2 was =H1<=TIME(1,0,0) set to red

But just always came up as green. To populate H1 i'm using the formula
=TEXT(G2-F2,"dd:hh:mm")

I tried altering the time to over a hour but cell just stayed green

When i tried formatting the cells to green and and just using the one CF
CF1 =H1>TIME(1,0,0) and set to red (i changed H1 to H2 because the title of
the column is in H1 but niether worked)

the cell turned red even though it was less than 1 hour. Any ideas of what
is going wrong?
 
B

Bob Phillips

Ah, text!

Change the formulae to

=--H1>TIME(1,0,0)
=--H1<=TIME(1,0,0)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

JE McGimpsey

The reason you're not seeing red is that the TEXT() function converted
the time difference to text, so it no longer is evaluated as a number.

Instead of using the TEXT() function, why not use

=G2-F2

and format the cell as "dd:hh:mm"

(Note that this format only works for days <=31, since dd is day of the
month, which defaults to January).
 
D

Dom

Thats great it works, one last question though if i wanted the last CF to
make the cell white (normal) if there is no values in the two times i.e the
difference is 00:00:00 what formula would that be?
Thanks to both of you for all your help
 
D

Dom

Oh and also what would the formula look like if i wanted the cell to be green
if less than 4 hours rather than 1?
 
D

David Biddulph

Your problem is that you are trying to compare text with time. Try H1
=G2-F2 and just format as dd:hh:mm.
 
D

David Biddulph

Perhaps you should look at Excel Help for the TIME() function, to help you
to decide how to change TIME(1,0,0) for 1 hour to something equivalent for 4
hours?
 
D

Dom

Yeah i figured that one out just after i wrote the question i wasn't
thinking, still can't figure out how to get the cell to stay white if the
value is 00:00:00 though any help would be much appreciated
 
D

David Biddulph

Well, you've got formulae looking at 1 hour, so you could use a variation on
that theme to look at = 0 hours, or otherwise if you want to look
specifically for the 2 input cells being empty you could try
=AND(F2="",G2="")
 
B

Bob Phillips

=AND(H1<>"",--H1<=TIME(1,0,0))

=AND(H1<>"",--H1>TIME(1,0,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top