Hi
Someone on this site previous has make up this great formula for me (see
below)
What i need now is when the following answer is displayed the text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

Yes, you can do this with Conditional Formatting. Choose
Conditional Formatting dialog from the Format menu, and in that
dialog, choose "Cell Value Is" and "Equal To" and enter 'On Time'
(no quotes). Then, click the Format button and choose your fill
color. Repeat these steps for your other conditions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ben" wrote in message
...
Hi
Someone on this site previous has make up this great formula
for me (see
below)
What i need now is when the following answer is displayed the
text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't
work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

Ben,

Select column to be formatted

Format==Conditional Formatting

Cell Value Is ==equal to ==On Time

Click Format.... ==Pattern==Select colour (BLUE)

Click Add button

Repeat above for other 2 conditions

HTH

"Ben" wrote:

Hi
Someone on this site previous has make up this great formula for me (see
below)
What i need now is when the following answer is displayed the text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

Ben,
Ignore my last posting ..it won't work. I should have looked more
carefully at your formula.

"Ben" wrote:

Hi
Someone on this site previous has make up this great formula for me (see
below)
What i need now is when the following answer is displayed the text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

Hi Chris

Almost there, i've done as you said but the colour change is only being
applied when the answer is On-Time.

This may be because when the answer is displayed for Early by or Late by, it
is followed by a time i.e. Early by 0days & 1:00,but i can't set this as
the conditional formula because the time can change from cell to cell.

Have you a fix for this?


"Chip Pearson" wrote:

Yes, you can do this with Conditional Formatting. Choose
Conditional Formatting dialog from the Format menu, and in that
dialog, choose "Cell Value Is" and "Equal To" and enter 'On Time'
(no quotes). Then, click the Format button and choose your fill
color. Repeat these steps for your other conditions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ben" wrote in message
...
Hi
Someone on this site previous has make up this great formula
for me (see
below)
What i need now is when the following answer is displayed the
text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't
work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

Hi

It's works great when the answer is On-time,but not for when the answer is
Early by or Late by.

I think this is because these are then followed by a time i.e late by 0day &
1:00

Any ideas
Thanks
Ben

"Toppers" wrote:

Ben,
Ignore my last posting ..it won't work. I should have looked more
carefully at your formula.

"Ben" wrote:

Hi
Someone on this site previous has make up this great formula for me (see
below)
What i need now is when the following answer is displayed the text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

In conditional format select "Formula is" instead of "Cell value is", and
insert these formulas for the second and third condition.

=LEFT(A1,5)="Early"
=LEFT(A1,4)="Late"

A1 is the cell in the top left of the highlighted area that you are applying
the conditional formatting to. For example, if you select D5:D45 then your
formulas will look like this...
=LEFT(D5,5)="Early"
=LEFT(D5,4)="Late"

"Ben" wrote:

Hi Chris

Almost there, i've done as you said but the colour change is only being
applied when the answer is On-Time.

This may be because when the answer is displayed for Early by or Late by, it
is followed by a time i.e. Early by 0days & 1:00,but i can't set this as
the conditional formula because the time can change from cell to cell.

Have you a fix for this?


"Chip Pearson" wrote:

Yes, you can do this with Conditional Formatting. Choose
Conditional Formatting dialog from the Format menu, and in that
dialog, choose "Cell Value Is" and "Equal To" and enter 'On Time'
(no quotes). Then, click the Format button and choose your fill
color. Repeat these steps for your other conditions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ben" wrote in message
...
Hi
Someone on this site previous has make up this great formula
for me (see
below)
What i need now is when the following answer is displayed the
text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't
work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

Ben,
Use "Formula is" and enter =Find("Late by",C1) ... assuming C
is the column for CF and colour as before. Repeat for On Time and Early by.
(You can just test For Early/Late, omitting the "by").

HTH

"Ben" wrote:

Hi

It's works great when the answer is On-time,but not for when the answer is
Early by or Late by.

I think this is because these are then followed by a time i.e late by 0day &
1:00

Any ideas
Thanks
Ben

"Toppers" wrote:

Ben,
Ignore my last posting ..it won't work. I should have looked more
carefully at your formula.

"Ben" wrote:

Hi
Someone on this site previous has make up this great formula for me (see
below)
What i need now is when the following answer is displayed the text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))