Conditional Formatting on Cumulative HH:MM:SS

[MatthewIJClark]

Hi All,

I'm copying information from one spreadsheet to another and there is minus HH:MM:SS, zero HH:MM:SS and plus HH:MM:SS and I'd like to change the colour of the cumulative hours depending on what they are.

So if it's minus hours I want red, if it's zero hours I want green and if plus hours I would like black.

I've tried conditional formatting using 3 rules if "cell value" less than, equal to or greater than zero but that doesn't work.

So I've used the following to change red:

=SEARCH("-",F2)

But cannot for the life of me get any other of the cumulative hours to change colour.

Any help would be greatly apprecited.

[BadlySpelledBuoy]

Are your negative times in text format or are you using a 1904 date system to show negative times?

Perhaps a small sample workbook would be useful for assisting you here?

BSB

[MatthewIJClark]

Many thanks for the quick response.

I'm using the 1904 date system.

I've tried to attach the spreadsheet, however, doesn't appear that I'm able to do so.

[BadlySpelledBuoy]

Which cells in the attachment are you wanting to apply this conditional formatting to?

BSB

[BadlySpelledBuoy]

Your conditional formatting is likely not to work because the values are text formatted rather than numbers.
Have the times calculated with formulas rather than manually entered as text values and your <0, =0 & >0 conditional formatting should work.

BSB

[MatthewIJClark]

All the information in the spreadsheet I attached is copy and pasted from another spreadsheet which are formula answers either average or sum.

I can't manually calculate in the attached sheet as all the data on another sheet.

Unless I copy all the data over and hide it, then migrate the data I want via the " = " option.

[BadlySpelledBuoy]

Short of seeing the workbook that these values are copied and pasted from, it's a little difficult to give any real guidance.
If there's a reason they need to be pasted in as text values then perhaps you could look at creating a macro that did the copying and pasting for you and colouring the cells appropriately as it did so.

BSB