Conditionl format a cell to change text colour

[PAULANDBEX24]

I want to change the colour of the number in cell C3 (to red) when the time
is 5 seconds greater than in C2

They are lap times ie 1min 33 seconds . 672
C3 = 1.39.672 current time
C2 = 1.33.672 best time

I used to be able to do this easily in the previous office but cant get
office 2007 to do it.
Please can someone help.

 

[Peo Sjoblom]

Format>conditional formatting>formula is


=C3-TIME(0,0,5)>=C2

then click the format button and select a red font and click OK twice


Of course 1.33.672 is text unless you have changed you time delimiter,
you need to enter the times as 0:01:33.672
and format the cell as mm:ss.000


Make sure your regional settings are 24 hour and not AM/PM or else it would
look strange in the formula bar with
12:01:34 AM

--


Regards,


Peo Sjoblom

 

[PAULANDBEX24]

THANKS BUT IT HAS NOT WORKED.

BOTH CELLS ARE FORMATTED IN TIME with 24 hr clock
I inputted your formula but nothing has changed
This seems to be the formula i used in office 2003 but i am now using office
2007.
Any ideas

 

[Fred Smith]

Ensure you have real Excel times in your cells. It looks to me like you have
text, not times. Applying a format to text won't help. It's still text. Try
entering the times this way:

0:1:39.672
0:1:33.672

Regards,
Fred.

 

[Peo Sjoblom]

1. Stop using all caps, it is considered shouting, this is a peer to peer
newsgroup/forum.

I have 2007 as well as 2003 on my PC and it works perfectly fine there.
Select C3,
on the Home tab click the conditional formatting icon, select highlight cell
rules and from the
options select more rules, from the select rule type select "use a formula
to determine which cells to format"

Put the formula in the "format values where this formula is true:" and click
the format button and
select a red font and click OK twice.

I just tested it and it works in all versions that has this functionality.
If not your data is not what it looks
like.


--


Regards,


Peo Sjoblom

 

[David Biddulph]

You have deleted the entire content of the message to which you are
replying, which is presumably the messaghe from Peo. You haven't answered
the part where he told you that you needed to insert the data in the correct
format.
1.33.672 is NOT a time
1:33.672 IS a time.
There is a big difference between a full stop and a colon.
If you start by putting the data in the wrong format, it will be treated as
TEXT, as Peo told you. Trying to change the cell format subsequently will
not help, as the cell contents are still text.

Try again.

 

[PAULANDBEX24]

Ok thanks guys. between all of you i have pieced it together.
SORRY for the caps didnt realise.

1 other problem,
It now turns the number i enter to 00:01:34 which is ok and the cells change
colour but the split time .672 no longer shows is there a way to bring the
full time up as i cant find it on format
Once again thanks

 

[David Biddulph]

If it is showing as 00:01:34 you have presumably got it formatted as
hh:mm:ss. Peo told you to format the cell as format the cell as mm:ss.000
[you can do that in Format/ Cells/ Number/ Custom].

If you are struggling with the basics, you probably need to read one of the
books such as Excel for Dummies, or do some of the many basic Excel courses,
either using on-line tutorials (such as those at
http://office.microsoft.com/en-us/training/CR061831141033.aspx) or
elsewhere. You can answers to most things in Excel help.