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

=H1TIME(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)



"Dom" wrote in message
...
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

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?

"Bob Phillips" wrote:

=H1TIME(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)



"Dom" wrote in message
...
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

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 =H1TIME(1,0,0)
Format: <pattern/<red

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




In article ,
Dom wrote:

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?

"Bob Phillips" wrote:

=H1TIME(1,0,0)

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

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)



"Dom" wrote in message
...
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?

"Bob Phillips" wrote:

=H1TIME(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)



"Dom" wrote in message
...
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

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

CF1 was =H1TIME(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 =H1TIME(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?

"Bob Phillips" wrote:

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)



"Dom" wrote in message
...
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?

"Bob Phillips" wrote:

=H1TIME(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)



"Dom" wrote in message
...
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

Ah, text!

Change the formulae to

=--H1TIME(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)



"Dom" wrote in message
...
I did try setting a different colour for the two different formulas

CF1 was =H1TIME(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 =H1TIME(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?

"Bob Phillips" wrote:

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)



"Dom" wrote in message
...
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?

"Bob Phillips" wrote:

=H1TIME(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)



"Dom" wrote in message
...
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

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).

In article ,
Dom wrote:

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

CF1 was =H1TIME(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 =H1TIME(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?

"Bob Phillips" wrote:

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)



"Dom" wrote in message
...
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?

"Bob Phillips" wrote:

=H1TIME(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)



"Dom" wrote in message
...
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

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

"Dom" wrote in message
...
I did try setting a different colour for the two different formulas

CF1 was =H1TIME(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 =H1TIME(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?

"Bob Phillips" wrote:

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)



"Dom" wrote in message
...
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?

"Bob Phillips" wrote:

=H1TIME(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)



"Dom" wrote in message
...
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