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

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

"Bob Phillips" wrote:

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

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?

"JE McGimpsey" wrote:

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

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?
--
David Biddulph

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

"JE McGimpsey" wrote:

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

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

"David Biddulph" wrote:

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

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="")
--
David Biddulph

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

"David Biddulph" wrote:

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

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

=AND(H1<"",--H1TIME(1,0,0))

--
---
HTH

Bob

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



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

"Bob Phillips" wrote:

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