CODEWITHSUNDEEP
excel
Bail P
Bail P

Reputation: 271

Adding conditional formatting to a value less than 0

I have a column which has time values which will either be a positive or a negative figure. I want the negative time values to be in red. The format of these cells is 'Custom' and then '[h]:mm:ss'. I have added the negative '-' prefix using the CONCAT function:

=IF(H3="Yes",CONCAT("-",F3),F3)

When selecting 'if less than 0' in the conditional formatting, the negative cells aren't being highlighted. I am guessing this is because I have simply prefixed the time values with a '-' prefix rather than it actually being a negative time. I am unsure how to enable the conditional formatting to work?

Here is a sample of the data:

enter image description here

Columns are E, F, and G from left to right

Upvotes: 0

Views: 7600

Answers (1)

Ken Lee
Ken Lee

Reputation: 8043

  1. Go to Home → Conditional Formatting → Highlight Cell Rules → Text that contains (type "-")
  2. Select the cells in which you want to highlight the negative numbers in red.
  3. In the dialog box, specify the value below which the formatting should be applied. If you want to use formatting other than the ones in the drop-down, use the Custom Format option.
  4. Click OK.

Upvotes: 1

Related Questions