# Conditional format time

#### Vtookup

##### Board Regular
Hi all.
I have a column of results. counting how many hours a task takes. if it exceed 5:00 (Formatted to time 13:30) the text should turn red.
tried conditional format greater than, it doesn't work and don't know what formula to use. I know time is tricky and i'm blank with this.
Can someone help? Thanks

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
If your cell displays 5:00 with the cell formatted with the 13:30 choice from the Format dialog the cell ACTUALLY CONTAINS the VALUE .208333 (or 5/24).
So maybe try "> 5/24"

Last edited:
Maybe (using yellow rather than red below just because it displays better on the board)...

Excel Workbook
A
106:30
204:00
305:30
410:50
503:30
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =A1>TIMEVALUE("05:00")Abc

Last edited:
Hi again.
Thanks for the quick replies. I got this formula from an online help
to clarify,Im using this formula =TEXT(C3-B3+(C3 < B3)+(E3-D3+(E3 < D3)),"h:mm")
what do you think is the correct format for the result? @jim may, maybe my format is wrong.
I tried MARK858's approach, even 5:00 got highlighted, same with the blanks.
what do you think is wrong? Thanks again.

In an empty cell put
=ISNUMBER(A1)
changing the A1 to the cell containing the 05:00, is the result FALSE?
It should be, as the formula
=TEXT(C3-B3+(C3 < B3)+(E3-D3+(E3 < D3)),"h:mm")
would return a text result and so it is not a real time in the cell.

what does
=C3-B3+(C3 < B3)+(E3-D3+(E3 < D3))
return (a bit concerned what effect the < tests is having)?

Last edited:
Hi MARK858.
I have removed the TEXT and format in the formula. now it is working.
Thank You.

You're welcome

Replies
2
Views
132
Replies
4
Views
408
Replies
4
Views
1K
Replies
5
Views
294
Replies
2
Views
482

1,196,254
Messages
6,014,273
Members
441,810
Latest member
LouLou1234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back