conditional formatting time

[Dgwood90]

I have a spreadsheet that calculates remaining battery time. What I am
wanting is when battery run time has less than 1/3 remaining I want that cell
to turn red.
For example:
D2 has battery run time: 40 hours
D3 is remaining battery life: 13 hours
So when D3 drops below 13.333 hours it will turn red.

 

[barry houdini]

I have a spreadsheet that calculates remaining battery time.  What I am
wanting is when battery run time has less than 1/3 remaining I want that cell
to turn red.
For example:
D2 has battery run time: 40 hours
D3 is remaining  battery life: 13 hours
So when D3 drops below 13.333 hours it will turn red.

Try this
Select D3 and use Format > Conditional Formatting
under condition 1 select "formula is" from the dropdown and put this
formula in the box
=D3<D2/3
set required formatting > OK

 

[myname.rahul]

Try this
Select D3 and use Format > Conditional Formatting
under condition 1 select "formula is" from the dropdown and put this
formula in the box
=D3<D2/3
set required formatting > OK

Hi Dg Wood,

the above formatting is correct.


Regards

Rahul Gandhi

 

[Dgwood90]

I must be doing something wrong. It is turning red, no matter what the hours
are in D2. I tried putting quotes, deleted and redid. Thanks for your help.

 

[barry houdini]

Hello Dgwood90

Exactly what values do you have in D2 and D3, are they numbers like 40
and 13 or time values like 40:00 and 13:00? Are these values just
input or derived from formulas, if so which formulas?

 

[Dgwood90]

This is what I have
D1 is Battery Start Run Time, formated custom m/d/yyyy h:mm
D2 is battery run time, formatted as general
D3 is Battery run time left, formated custom [h]:mm:ss, with formula D2/24-D4
D4 is Time on Batteries, fomated custom [h]:mm:ss, with formula =NOW()-D1

 

[Dgwood90]

Thanks,

That fixed it. Very thankful for your help

In which case you wouldn't expect your =D3<D2/3 formula to work, as one
variable is a number of hours and the other is an Excel time (measured in
days).

You remembered the factor of 24 in your D3 formula, but then you forgot it
in your Conditional Formatting formula.

Try =D3*24<D2/3
--
David Biddulph

This is what I have
D1 is Battery Start Run Time, formated custom m/d/yyyy h:mm
D2 is battery run time, formatted as general
D3 is Battery run time left, formated custom [h]:mm:ss, with formula
D2/24-D4
D4 is Time on Batteries, fomated custom [h]:mm:ss, with formula =NOW()-D1

Hello Dgwood90

Exactly what values do you have in D2 and D3, are they numbers like 40
and 13 or time values like 40:00 and 13:00? Are these values just
input or derived from formulas, if so which formulas?