Conditional Formatting

[googlegroups.zaphod@rhoads-nicora.com]

Thank you in advance for any help you may be able to provide!

I would like to make the backgroud of a cell change color based on the
cells relaton to another cell.

For example;

The celles in column A are formated a Number/Custom/h.mm
The celles in column B are formated a Number/Custom/h.mm

I would like for the background of the cell in column B to be Blue if
the time in B is more then 30 minutes before the time in column A.

I would like for the background of the cell in column B to be white if
the time in B is less then 30 minutes before the time in column A and
less then 1 hour after then the time in column A.

I would like for the background of the cell in column B to be Red if
the time in B is more then 1 hour after the time in column A.

Thank you again.

[kassie]

Hi

May not be the best solution, but the problem is time calculations resulting
in a minus.

Insert a helper column, say C. Insert the following formula:
=A6/1*24-B6/1*24.
Results will show as decimal, eg 30 minutes will show as 0.50

With the cursor on the cell in col B, click on Format, Conditional
Formatting, and insert the following 3 conditions. Change "Cell Value Is" to
"Formula is" for all three.

Enter the following 3 formulae, for conditions 1, 2 and 3 respectively:

1 = =C9>=0.5
2 = =AND(C9<0.5,C9>-1)
3 = =C9<-1

For the conditions, set Format|Patterns to the colours you want, eg
Condition 1 = Green, Condition 2 is Yellow and Condition 3 is Red. If you
want to keep the normal background for condition 1, don't use condition 1.
You can then hide the helper column, obviously after copying the formula to
all the relevant cells in Column C.

"googlegroups.zaphod@rhoads-nicora.com" wrote:

> Thank you in advance for any help you may be able to provide!
>
> I would like to make the backgroud of a cell change color based on the
> cells relaton to another cell.
>
> For example;
>
> The celles in column A are formated a Number/Custom/h.mm
> The celles in column B are formated a Number/Custom/h.mm
>
> I would like for the background of the cell in column B to be Blue if
> the time in B is more then 30 minutes before the time in column A.
>
> I would like for the background of the cell in column B to be white if
> the time in B is less then 30 minutes before the time in column A and
> less then 1 hour after then the time in column A.
>
> I would like for the background of the cell in column B to be Red if
> the time in B is more then 1 hour after the time in column A.
>
> Thank you again.
>
>