Removing negative ( - ) symbol from Time Calculation

[BrianExcel]

I have a time display that is being calculated off other cells. The workbook is formatted in the 1904 date setting.

Also, I have conditional formatting applied so that if the value is less than zero is shows as red, but if greater than 0 it shows red.

What I need to do is simply remove the negative symbol from the time. If it shows as -6:40, then it should show as red but not have a - before it.

Thoughts? I tried using absolute value but that changes it to a positive number which then changes the red text to green. I need to somehow keep the value negative but not show the negative symbol. The red text should designate negative...

 

[AlphaFrog]

Instead of conditional formatting, use a custom cell format.

• Remove your conditional format
• Right-click on the cell with the result time
• Select Format Cells from the pop-up menu
• Custom format: h:mm;[Red]h:mm

 

[BrianExcel]

Thats works perfectly Alpha except one thing...how would I add Green to that mix?

So if it's negative show red, if positive show Green? Right now the red works but what about a second color?

 

[BrianExcel]

Sorry Alpha, one more question. How do you choose which color if it's not the standard Excel color?

Example: If I change your RED to GREEN in your provided example, it shows up as a 'Highlight' green, but what if I wanted it to be a shade or two darker and not so bright?

 

[AlphaFrog]

Custom Format: [Color10]h:mm;[Red]h:mm

Color10 is a dark green

Custom Format codes:
http://www.ozgrid.com/Excel/CustomFormats.htm

 

[BrianExcel]

You rock Alpha. Thank you!